合并一个COUNT查询
我有一个页面,我正在运行一个初始SQL查询来获取主题列表,然后我遍历这个查询并为从原始主题查询返回的每条记录运行两个附加查询(我碰巧在ColdFusion中这样做,但不确定真的很重要)。这两个附加查询为该特定主题执行COUNT,然后输出结果(输出主题名称,然后输出该主题的两个计数)。我试图提高此页面的性能,并想知道我是否可以以某种方式将它合并到单个查询中。合并一个COUNT查询
初始查询:
SELECT COUNT(test_ID) as priority_count FROM Test_Queue WHERE priority_flag = 1 AND subject_ID = #SubjectQuery.subject_ID# SELECT COUNT(test_ID) as locked_count FROM Test_Queue WHERE locked = 1 AND subject_ID = #SubjectQuery.subject_ID#
建议如何将这些可能会进行优化:
初始查询的环路内SELECT subject_ID, subject_name FROM Subjects ORDER BY subject_name
查询? DB是MS SQL 2008.谢谢。
SELECT
subject_ID,
subject_name,
priority_count = (select count(test_id) from test_queue where priority_flag = 1),
locked_count = (select count(test_id) from test_queue where locked = 1)
FROM Subjects
ORDER BY subject_name
,或者,如果计数都应该纳入subject_id(只是猜测),然后
SELECT
s.subject_ID,
s.subject_name,
priority_count = (select count(test_id) from test_queue t where priority_flag = 1 and t.subject_id = s.subject_id),
locked_count = (select count(test_id) from test_queue t where locked = 1 and t.subject_id = s.subject_id)
FROM Subjects s
ORDER BY subject_name
+1这是我在select中使用子查询的路线。我不得不说,我使用格式(
这应该这样做,假设从主体联接列test_queue是正确的,你的避风港'不指定test_queue中的哪一列引用科目
select
subjects.subject_id
,subjects.subject_name
,sum(case when test_queue.priority_flag=1 THEN 1 ELSE 0 END) as priority_count
,sum(case when test_queue.locked=1 THEN 1 ELSE 0 END) as locked_count
from
subjects
left join test_queue
on subjects.subject_id=test_queue.subject_id
group by subjects.subject_id, subjects.subject_name
order by subjects.subject_name
我喜欢你的使用组与VS不同...顺便说一句为什么大家都写不同风格/格式的SQL代码?只是好奇 :) – crosenblum 2009-12-22 02:05:58
这样的事情? (您可能需要转换为INT如果priority_flag或锁定是一类是SUM不喜欢)
SELECT
subject_ID
, subject_name
, SUM(priority_flag) AS priority_count
, SUM(locked) AS locked_count
FROM Subjects s, Test_Queue tq
GROUP BY subject_ID, subject_name
ORDER BY subject_name
;
我带你去一个平底船是Test_Queue.Subject_ID = Subjects.Subject_ID
SELECT s.subject_ID, s.subject_name, COUNT(t1.*) as priority_count, COUNT(t2.*) as locked_count
FROM Subjects s
LEFT OUTER JOIN Test_Queue t1
ON s.Subject_ID = t1.Subject_ID
AND t1.priority_flag = 1
LEFT OUTER JOIN Test_Queue t2
ON s.Subject_ID = t2.Subject_ID
AND t2.locked = 1
GROUP by s.subject_ID, s.subject_name
ORDER BY s.subject_name
你如何过滤基于subject_ID的Test_Queue,这里没有显示。 – Deeksy 2009-11-16 23:45:44
在循环中的查询中,WHERE子句是否包含subject_ID? – t3rse 2009-11-16 23:47:18
对不起,我删除了一些无关信息,并且意外地将其取出。我编辑了上面的查询。 – 2009-11-16 23:51:11