Question

为什么sql一个表中可查询两个count的sql语句？

T(1) as 总题数,paperName from question where papername in(select distinct paperName from question) group by paperNmeselect COUNT(1) as 审核题数,paperName from question where papername in(select distinct paperName from question) and statu=1 group by paperNme我的的sql语句只能查出一个题目数量或者审核数量如何让两个一起查询出来？





Answer 1

用case when根据条件计数:
select s.总题数, s.审核题数, s.paperNamefrom (select COUNT(1) as 总题数, case when status = 1 then count(1) else 0 end as 审核题数, paperNamefrom question--where papername in (select distinct paperName from question), 这个条件可以不要了group by paperNme, stauts -- status也要作为分组字段，因为在case中有使用) s

Answer 2

除了count之外还有一个函数叫做 SUM。
SELECT paperName , COUNT (1) AS 总题数 , sum (CASE WHEN statu = 1 THEN 1 ELSE 0 END) AS 审核题数FROM questionGROUP BY paperNmepapername in(select distinct paperName from question) 这个没意义吧？ 都是同一张question表，要不就是你表名写错了。

Good Luck ！

Answer 3

可以有两种解决方法，
所需工具：SQL
查询两个count的方法1:
SELECT paperName , COUNT (1) AS 总题数 , sum (CASE WHEN statu = 1 THEN 1 ELSE 0 END) AS 审核题数FROM questionGROUP BY paperNme查询两个count的方法2:
select s.总题数, s.审核题数, s.paperNamefrom (select COUNT(1) as 总题数, case when status = 1 then count(1) else 0 end as 审核题数, paperNamefrom question--where papername in (select distinct paperName from question), 这个条件可以不要了group by paperNme, stauts -- status也要作为分组字段，因为在case中有使用) s备注：两个都可以使用。