sql查询

将数据库中数字转换成汉字

SELECT
    字段1,字段2,字段3...,
    CASE
WHEN 字段4 = 1 THEN
    '壹'
ELSE
    '零'
END AS 字段4
FROM
    table

字段2的值随着字段1的变化而变化

SELECT
    CASE
WHEN 字段1 = 1 THEN
    '*******'
ELSE
    字段2
END AS 字段2,
 字段1 (为了展示字段1这一列，可以去掉，不影响查询)
FROM
    table;

当eid_detach_suport为0时eid_detach_state字段显示为无效或空。

SELECT
    items.id AS itemid,
    itemtypes.typedesc AS typedesc,
    agents.title,
    items.model AS itemmodel,
    typedesc,
    sn,
    /*（case eid_type when 0 then '未安装绑定标签' when 1 then '低功耗蓝牙标签 else '11'end) eid_type,）*/
    eidtype.eidstatus AS eid_type,
    eid,
    CASE
WHEN eid_detach_suport = 0 THEN
    '--'
ELSE
    CASE eid_detach_state
WHEN 0 THEN
    '--'
WHEN 1 THEN
    '分离'
ELSE
    'null'
END
END AS eid_detach_state,
 (
    CASE eid_detach_suport
    WHEN 0 THEN
        '--'
    WHEN 1 THEN
        '启用'
    ELSE
        'null'
    END
) eid_detach_suport,
 eid_battery,
 users.userdesc,
 statustypes.statusdesc,
 locations.floor AS locationname,
 locareas.areaname,
 coalesce(sn, '') AS serial,
 (
    SELECT
        group_concat(tags.name, ', ')
    FROM
        tags,
        tag2item
    WHERE
        tag2item.itemid = items.id
    AND tags.id = tag2item.tagid
) AS taginfo,
 (
    SELECT
        group_concat(software.stitle, ',')
    FROM
        software,
        item2soft
    WHERE
        item2soft.itemid = items.id
    AND software.id = item2soft.softid
) AS softinfo,
 purchprice,
 macs,
 ipv4,
 ipv6,
 remadmip
FROM
    items
JOIN itemtypes ON items.itemtypeid = itemtypes.id
JOIN agents ON items.manufacturerid = agents.id
LEFT OUTER JOIN statustypes ON items.status = statustypes.id
JOIN users ON items.userid = users.id
LEFT OUTER JOIN locations ON items.locationid = locations.id
LEFT OUTER JOIN locareas ON items.locareaid = locareas.id
LEFT OUTER JOIN eidtype ON items.eid_type = eidtype.status_id

当eid_detach_suport为0时eid_detach_state和eid_battery字段显示为无效或空。

SELECT
    items.id AS itemid,
    itemtypes.typedesc AS typedesc,
    agents.title,
    items.model AS itemmodel,
    typedesc,
    sn,
    /*（case eid_type when 0 then '未安装绑定标签' when 1 then '低功耗蓝牙标签 else '11'end) eid_type,）*/
    eidtype.eidstatus AS eid_type,
    eid,
    CASE
WHEN eid_detach_suport = 0 THEN
    '--'
ELSE


    CASE eid_detach_state
WHEN 0 THEN
    '--'
WHEN 1 THEN
    '分离'
ELSE
    'null'
END

END AS eid_detach_state,
 (
    CASE eid_detach_suport
    WHEN 0 THEN
        '--'
    WHEN 1 THEN
        '启用'
    ELSE
        'null'
    END
) eid_detach_suport,
--  eid_battery,
CASE
WHEN eid_detach_suport = 0 THEN
    '--'
ELSE

    eid_battery

END AS eid_battery,

 users.userdesc,
 statustypes.statusdesc,
 locations.floor AS locationname,
 locareas.areaname,
 coalesce(sn, '') AS serial,
 (
    SELECT
        group_concat(tags.name, ', ')
    FROM
        tags,
        tag2item
    WHERE
        tag2item.itemid = items.id
    AND tags.id = tag2item.tagid
) AS taginfo,
 (
    SELECT
        group_concat(software.stitle, ',')
    FROM
        software,
        item2soft
    WHERE
        item2soft.itemid = items.id
    AND software.id = item2soft.softid
) AS softinfo,
 purchprice,
 macs,
 ipv4,
 ipv6,
 remadmip
FROM
    items
JOIN itemtypes ON items.itemtypeid = itemtypes.id
JOIN agents ON items.manufacturerid = agents.id
LEFT OUTER JOIN statustypes ON items.status = statustypes.id
JOIN users ON items.userid = users.id
LEFT OUTER JOIN locations ON items.locationid = locations.id
LEFT OUTER JOIN locareas ON items.locareaid = locareas.id
LEFT OUTER JOIN eidtype ON items.eid_type = eidtype.status_id

 

111