sql条件查询、case when用法、替换字符、补全长度、联表查询语句
首先存在这样的数据库:
1、统计不同设备类别各运行状态的设备数,数量按从大到小排序。
SELECT a.*
FROM (
SELECT COUNT(1) AS num, MacStatus
FROM ZMac
GROUP BY MacStatus, MACType
) a
ORDER BY a.num DESC;
可以将统计结果为虚拟表a之后排序,也可以直接进行排序。
如果你的数据表没有主键,那么count(1)比count()快
如果有主键的话,那主键(联合主键)作为count的条件也比count()要快
如果你的表只有一个字段的话那count(*)就是最快的啦
SELECT COUNT(1) AS num, MacStatus FROM ZMac
GROUP BY MacStatus, MACType
ORDER BY num DESC;
2、若维修费用大于一万元则为等级L1,一千元到一万元则为等级L2,小于1000元则为等级L3。查询各等级的设备平均使用年龄和平均维修费用。
SELECT (
CASE
WHEN WCost > 10000 THEN
"L1"
WHEN WCost >= 1000 AND WCost <= 10000 THEN
"L2"
ELSE
"L3"
END) as l,
AVG(zmac.UseAge) as avgAge,
AVG(zcost.Wcost) as avgCost
FROM ZMacCost zcost
JOIN ZMac zmac ON zcost.MacNo = zmac.MacNo
GROUP BY l;
case when 函数:
多个条件时,THEN后边的值与ELSE后边的值类型应一致,否则会报错。
3、由于公司资产规则调整,将所有设备编码在原油的编码规则上,将所有的“_”去掉并增加前缀固定字符“MAC”,将设备编号游补足10位。
SELECT concat("MAC", replace(MacName, '_', '')) MacName, RPAD(MacNo, 10, 0) MacNo
FROM ZMac;
concat()函数
功能:将多个字符串连接成一个字符串。语法:concat(str1, str2,…)
返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
concat_ws()函数
功能:和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符~
语法:concat_ws(separator, str1, str2, …)。说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。
replace(MacName, ’ _ ', ‘’):将MacName中所有出现的‘ _ ',都替换为空
4、
SELECT wg.Gname,
zw.Wname,
zw.Age,
zmc.MacNo,
zm.MacName,
zm.UseAge,
zmca.zmcCount,
zmca.avgWCost,
zmca.maxWCost
FROM WGroup wg
JOIN ZWorker zw ON zw.Gid = wg.Gid
JOIN ZMacCost zmc ON zmc.Wid = zw.Wid
JOIN ZMac zm ON zmc.MacNo = zm.MacNo
JOIN (
SELECT MacNo, COUNT(MtaNp) zmcCount, AVG(WCost) avgWCost, MaX(WCost) maxWCost FROM ZMacCost GROUP BY MacNo
) zmca ON zmca.MacNo = zm.MacNo
ORDER BY zw.Wname;
5、写一个函数翻译维修班组名称,要求传入维修人员编号,传出维修班组名称。
SELECT wg.Gname
FROM WGroup wg
JOIN ZWorker zw ON zw.Gid = wg.Gid
WHERE zw.Wid = 1;