我有一个通过一定条件得到的结果如下(简化,实际比这复杂):
Select 编号,数量,日期
From tab_a
Where 条件现想将杳询结果按不同时间段再次筛选,并将多次筛选结果放在同一表中,用left可以满足,但是否有更好的办法来提高效率?我是新手望各位多指教。
Select a.编号,tj1,tj2,tj3
From (Select 编号,count(数量) as tj1
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 between '2007-01-01' AND '2007-03-31'
group by t.编号) a
left join
(Select 编号,count(数量) as tj2
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 between '2007-03-01' AND '2007-03-31'
group by t.编号) b
on a.编号=b.编号
left join
(Select 编号,count(数量) as tj3
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 = '2007-03-31'
group by t.编号) c
on a.编号=c.编号
group by a.编号
Select 编号,数量,日期
From tab_a
Where 条件现想将杳询结果按不同时间段再次筛选,并将多次筛选结果放在同一表中,用left可以满足,但是否有更好的办法来提高效率?我是新手望各位多指教。
Select a.编号,tj1,tj2,tj3
From (Select 编号,count(数量) as tj1
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 between '2007-01-01' AND '2007-03-31'
group by t.编号) a
left join
(Select 编号,count(数量) as tj2
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 between '2007-03-01' AND '2007-03-31'
group by t.编号) b
on a.编号=b.编号
left join
(Select 编号,count(数量) as tj3
From (Select 编号,数量,日期 From tab_a Where 条件) t
Where t.日期 = '2007-03-31'
group by t.编号) c
on a.编号=c.编号
group by a.编号
select 编号,
count(case when 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) as tj1,
count(case when 日期 between '2007-03-01' AND '2007-03-31' then 1 else 0 end) as tj2,
count(case when 日期 ='2007-03-31' then 1 else 0 end) as tj3
from tab_a
group by 编号
谢谢你!报歉,我为了讲述方便,把第2次的查询简化了,实际条件是:编号在另一张表在不同时间段存在数量,所以按你的方法照搬后出错:
count(case when 编号 in (select 编号 from tab_b where 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) as tj1
报:"不能对包含聚合或子查询的表达式执行聚合函数。"
再次谢谢你!望能继续得到指教。
sum(case when exists(select 1 from tab_b where 日期 between '2007-01-01' AND '2007-03-31' and tab_b.编号=基表.编号) then 1 else 0 end) as tj1
select 编号,
sum(case when exists(select 1 from tab_b where 日期 between '2007-01-01' AND '2007-03-31' and tab_b.编号=t.编号) then 1 else 0 end) as tj1,
sum(case when exists(select 1 from tab_b where 日期 between '2007-03-01' AND '2007-03-31' and tab_b.编号=t.编号) then 1 else 0 end) as tj2,
sum(case when exists(select 1 from tab_b where 日期 = '2007-03-31' AND tab_b.编号=t.编号) then 1 else 0 end) as tj3
from (原查询语句) t
group by t.编号
sum(case when cc=67 then 1 else 0 end),sum(case when cc=78 then 1 else 0 end)
from aa group by policyno
sum(case when 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) tj1,
sum(case when 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) tj1,
sum(case when 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) tj1
from (查询语句)aa group by 编号
谢谢你!!
(查询语句)设为t表,那么我要的结果是:统计t表中的编号在另一表tab_b中,在不同时间段的数量。另外你所列sum(case when 日期 between '2007-01-01' AND '2007-03-31' then 1 else 0 end) tj1中日期 是表tab_b中的日期。
select 编号,
case when exists(select 1 from tab_b where 日期 between '2007-01-01' AND '2007-03-31' and tab_b.编号=t.编号) then 1 else 0 end as tj1,
case when exists(select 1 from tab_b where 日期 between '2007-03-01' AND '2007-03-31' and tab_b.编号=t.编号) then 1 else 0 end as tj2,
case when exists(select 1 from tab_b where 日期 = '2007-03-31' AND tab_b.编号=t.编号) then 1 else 0 end as tj3
from (原查询语句))t
group by t.编号
谢谢你!!试过,能运行比用left简练多了(由于我原查询语句比较长),但运行时间与用left差不多。