这个查询原先是用存储过程+临时表实现的,原因是不熟悉SQL,但是总感觉可以用一个查询完成,所以来请教:select * into #temp_table from view_1 where filter_commonselect
(count(*) from #temp_table where filter_1) as count_1
, (count(*) from #temp_table where filter_2) as count_2
, (count(*) from #temp_table where filter_3) as count_3
--...
, (count(*) from #temp_table where filter_n) as count_n也就是说需要统计n个不同filter的记录数,然后返回,而这n个不同的filter有个公共的filter_common
当然也可以写成下面这样:select
(count(*) from #temp_table where filter_1 and filter_common) as count_1
, (count(*) from #temp_table where filter_2 and filter_common) as count_2
, (count(*) from #temp_table where filter_3 and filter_common) as count_3
--...
, (count(*) from #temp_table where filter_n and filter_common) as count_n但是这样性能会受到影响,虽然已经在各个filter上建立了索引,但是因为
1, view_1本身比较复杂
2, 需要count统计的情况比较多
所以,这样性能比较差。相比较之下,使用前面的存储过程才能满足条件。
因为对T-SQL不是很熟悉,曾经以为下面的code可以work:
select
(count(*) from _temp where filter_1 and filter_common) as count_1
, (count(*) from _temp where filter_2 and filter_common) as count_2
, (count(*) from _temp where filter_3 and filter_common) as count_3
--...
, (count(*) from _temp where filter_n and filter_common) as count_n
from
(select * from view_1 where filter_common) as _temp但是
(count(*) from #temp_table where filter_1) as count_1
, (count(*) from #temp_table where filter_2) as count_2
, (count(*) from #temp_table where filter_3) as count_3
--...
, (count(*) from #temp_table where filter_n) as count_n也就是说需要统计n个不同filter的记录数,然后返回,而这n个不同的filter有个公共的filter_common
当然也可以写成下面这样:select
(count(*) from #temp_table where filter_1 and filter_common) as count_1
, (count(*) from #temp_table where filter_2 and filter_common) as count_2
, (count(*) from #temp_table where filter_3 and filter_common) as count_3
--...
, (count(*) from #temp_table where filter_n and filter_common) as count_n但是这样性能会受到影响,虽然已经在各个filter上建立了索引,但是因为
1, view_1本身比较复杂
2, 需要count统计的情况比较多
所以,这样性能比较差。相比较之下,使用前面的存储过程才能满足条件。
因为对T-SQL不是很熟悉,曾经以为下面的code可以work:
select
(count(*) from _temp where filter_1 and filter_common) as count_1
, (count(*) from _temp where filter_2 and filter_common) as count_2
, (count(*) from _temp where filter_3 and filter_common) as count_3
--...
, (count(*) from _temp where filter_n and filter_common) as count_n
from
(select * from view_1 where filter_common) as _temp但是
(count(*) from view_1 where filter_1 and filter_common) as count_1
, (count(*) from view_1 where filter_2 and filter_common) as count_2
, (count(*) from view_1 where filter_3 and filter_common) as count_3
--...
, (count(*) from view_1 where filter_n and filter_common) as count_n
(SELECT count(*) from #temp_table where filter_1 and filter_common) as count_1
, (SELECT count(*) from #temp_table where filter_2 and filter_common) as count_2
, (SELECT count(*) from #temp_table where filter_3 and filter_common) as count_3
--...
, (count(*) from #temp_table where filter_n and filter_common) as count_n
先从一个view中查出满足共同条件的一个记录
在从这个记录中统计n个不同条件的记录的数量,返回返回
sum(case when a>10 and b>5 then 1 end) as count_2,
sum(case when a>100 and b=3 then 1 end) as count_3
from xxx
case when 来判断 不过最好写成 动态语句
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
(select count(*) from #temp_table where filter_1) as count_1
, (select count(*) from #temp_table where filter_2) as count_2
, (select count(*) from #temp_table where filter_3) as count_3
--...
, (select count(*) from #temp_table where filter_n) as count_n
select
(select count(*) from view_1 where filter_1 and filter_common) as count_1
, (select count(*) from view_1 where filter_2 and filter_common) as count_2
, (select count(*) from view_1 where filter_3 and filter_common) as count_3
--...
, (select count(*) from view_1 where filter_n and filter_common) as count_n
select
(select count(*) from _temp where filter_1 and filter_common) as count_1
, (select count(*) from _temp where filter_2 and filter_common) as count_2
, (select count(*) from _temp where filter_3 and filter_common) as count_3
--...
, (select count(*) from _temp where filter_n and filter_common) as count_n
from
(select * from view_1 where filter_common) as _temp
问题的关键是怎样先根据某个filter_common拿到一个子集,再从该子集中根据n个确定的不同的filter去进行统计
如果有group by 怎么办?