主表(Opt_Receive_Send ):
id customerid opt_date gcid(从表gcid字段) work_id
1 1 2009-12-16 123 1
2 2 2009-12-16 null (代表未建档,从表中无记录) 7从表(Archives):
id gcid bar_code
1 123 333
2 222 666要求对主表汇总,work_id=7代表气瓶发送,work_id=1 代表气瓶回收,统计今天的气瓶发送回收情况:
select customerid ,sum(case when work_id='7' then 1 else 0 end) as 今日发送 ,
sum(case when work_id='1' then 1 else 0 end) as 今日回收 from Opt_Receive_Send
形成以下结果:
customerid opt_date 今日发送 今日回收
1 2009-12-16 1 1
当我关联到从表之后还能形成一下这样的查询结果
customerid opt_date 今日发送 今日回收
1 2009-12-16 1 1
id customerid opt_date gcid(从表gcid字段) work_id
1 1 2009-12-16 123 1
2 2 2009-12-16 null (代表未建档,从表中无记录) 7从表(Archives):
id gcid bar_code
1 123 333
2 222 666要求对主表汇总,work_id=7代表气瓶发送,work_id=1 代表气瓶回收,统计今天的气瓶发送回收情况:
select customerid ,sum(case when work_id='7' then 1 else 0 end) as 今日发送 ,
sum(case when work_id='1' then 1 else 0 end) as 今日回收 from Opt_Receive_Send
形成以下结果:
customerid opt_date 今日发送 今日回收
1 2009-12-16 1 1
当我关联到从表之后还能形成一下这样的查询结果
customerid opt_date 今日发送 今日回收
1 2009-12-16 1 1
select T.*,a.gcid from
(select customerid ,sum(case when work_id='7' then 1 else 0 end) as 今日发送 ,
sum(case when work_id='1' then 1 else 0 end) as 今日回收 from Opt_Receive_Send
group by customerid) T left join Archives a on a.customerid =T.customerid
m.opt_date,
sum(case m.work_id when 7 then 1 else 0 end) [今日发送],
sum(case m.work_id when 1 then 1 else 0 end) [今日回收]
from Opt_Receive_Send m , Archives n
where m.gcid = n.gcid
group by m.customerid, m.opt_date
select m.customerid,
m.opt_date,
sum(case m.work_id when 7 then 1 else 0 end) [今日发送],
sum(case m.work_id when 1 then 1 else 0 end) [今日回收]
from Opt_Receive_Send m left join Archives n
on m.gcid = n.gcid
group by m.customerid, m.opt_date