oracle开发中用到的SQL
oracle开发中用到的SQL
查询一级类目下 2级 3级 4级 对应的数量
Sql代码
SELECT temp.cname,temp.rootcid,
(SELECT COUNT(1) FROM show_tb_category t1 WHERE t1.flag=2 AND t1.rootcid=temp.rootcid)category2,
(SELECT COUNT(1) FROM show_tb_category t1 WHERE t1.flag=3 AND t1.rootcid=temp.rootcid)category3,
(SELECT COUNT(1) FROM show_tb_category t1 WHERE t1.flag=4 AND t1.rootcid=temp.rootcid)category4
FROM
(SELECT rootcid,flag,cname FROM show_tb_category WHERE flag=1 ORDER BY cid) temp
查询1,2,3,4级类目总的数量
Sql代码
SELECT
SUM(CASE t.flag WHEN 1 THEN 1 ELSE 0 END) AS count1,
SUM(CASE t.flag WHEN 2 THEN 1 ELSE 0 END) AS count2,
SUM(CASE t.flag WHEN 3 THEN 1 ELSE 0 END) AS count3,
SUM(CASE t.flag WHEN 4 THEN 1 ELSE 0 END) AS count4,
COUNT(t.flag) AS counTotal
FROM show_tb_category t
