34,594
社区成员
发帖
与我相关
我的任务
分享
SELECT A.*,ISNULL(B.t_num,0) AS t_num
FROM Y A
LEFT JOIN
(SELECT t_id,t_group,SUM(t_num) as t_num FROM X GROUP BY t_id,t_group) AS B
ON A.t_id=B.t_id AND A.t_group=B.t_group
create table 表X(t_id varchar(10),t_num int,t_group varchar(10))
insert into 表X(t_id,t_num,t_group)
select '001',0,'一组' union all
select '001',2,'二组' union all
select '001',5,'二组' union all
select '002',5,'一组' union all
select '003',0,'三组' union all
select '002',20,'一组' union all
select '002',20,'二组'
create table 表Y(t_id varchar(10),t_min int,t_group varchar(10))
insert into 表Y(t_id,t_min,t_group)
select '001',5,'一组' union all
select '001',16,'二组' union all
select '001',12,'三组' union all
select '002',0,'一组' union all
select '002',4,'二组' union all
select '002',0,'三组'
create table 表Z(t_id varchar(10),t_name varchar(10))
insert into 表Z(t_id,t_name)
select '001','名称a' union all
select '002','名称b'
select a.t_id,c.t_name,a.t_min,t_num=isnull(b.t_num,0),a.t_group
from (select *
from 表Y
where t_min>0) a
left join (select t_id,t_group,t_num=sum(t_num)
from 表X
group by t_id,t_group
having sum(t_num)>0) b on a.t_id=b.t_id
and a.t_group=b.t_group
inner join 表Z c on a.t_id=c.t_id
/*
t_id t_name t_min t_num t_group
---------- ---------- ----------- ----------- ----------
001 名称a 5 0 一组
001 名称a 16 7 二组
001 名称a 12 0 三组
002 名称b 4 20 二组
(4 row(s) affected)
*/