sql - COUNT 与 SELECT by IF 条件

Question

如果它们真的相等，我正在寻找两个 sql 查询的计数

SELECT c.name,
count ( IF (

            (SELECT count(*) FROM catey_content cm WHERE cm.sid = c.id AND cm.state = 1) 
             =      

            (SELECT count(*) FROM catey_content_comp cmc WHERE cm.sid = c.id AND cmc.state BETWEEN 1 
             AND 10)    
            )
       ) AS "completed"

FROM category_it c GROUP BY c.name

标签： sql

Answer 1

如果你想检查一些条件，你可以使用 where 子句，也可以在子查询上使用 join

     SELECT c.name, "completed"
     FROM category_it c 
     INNER JOIN  (
         SELECT sid, count(*) count_cm
         FROM catey_content cm WHERE cm.state = 1
         GROUP BY sid
    ) t1 on t1.sid = c.id
    INNER JOJN (
     SELECT sid, count(*) count_cmc
     FROM catey_content_comp cmc 
     WHERE  cmc.state  BETWEEN 1  AND 10
     GROUP BY sid
    ) t2 on t2.sid = c.id
    WHERE t1.count_cm = t2.count_cmc