开发中用到的SQL

最新推荐文章于 2022-02-17 07:50:00 发布

cfyme

最新推荐文章于 2022-02-17 07:50:00 发布

阅读量81

点赞数

分类专栏： oracle

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

查询一级类目下 2级 3级 4级对应的数量

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级类目总的数量

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