611
社区成员




declare @tb table (d_id char(10), p_id char(10), gx_name char(10), hg int, bl int, w_name char(10), r_date datetime)
insert @tb
select '001006', 'XHD65', '包装', 200, 1, ' 干检上漏', '2009-12-16' union all
select '001006', 'XHD65', '包装', 100, 1, ' 干检上漏', '2009-12-17' union all
select '001006', 'XHD65', '包装', 200, 1, ' 干检下漏', '2009-12-16'
select a.d_id, a.p_id, a.gx_name, a.hg, sum(b.bl) as bl, b.w_name
from
(
select d_id, p_id, gx_name, sum(distinct hg) as hg from @tb
where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
group by d_id, p_id, gx_name
) a left join @tb b
on a.d_id = b.d_id and a.p_id = b.p_id and a.gx_name = b.gx_name
where convert(char(10), b.r_date, 120) between '2009-12-16' and '2009-12-17'
group by a.d_id, a.p_id, a.gx_name, a.hg, b.w_name
/*
d_id p_id gx_name hg bl w_name
---------- ---------- ---------- ----------- ----------- ----------
001006 XHD65 包装 300 2 干检上漏
001006 XHD65 包装 300 1 干检下漏
*/
declare @tb table (d_id char(10), p_id char(10), gx_name char(10), hg int, bl int, w_name char(10), r_date datetime)
insert @tb
select '001006', 'XHD65', '包装', 200, 1, ' 干检上漏', '2009-12-16' union all
select '001006', 'XHD65', '包装', 100, 1, ' 干检上漏', '2009-12-17' union all
select '001006', 'XHD65', '包装', 200, 1, ' 干检下漏', '2009-12-16'
select a.d_id, a.p_id, a.gx_name, a.hg, b.bl, b.w_name
from
(
select d_id, p_id, gx_name, sum(hg) as hg
from
(
select distinct d_id, p_id, gx_name, hg from @tb
where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
) c group by d_id, p_id, gx_name
) a left join
(
select d_id, p_id, gx_name, sum(bl) as bl, w_name from @tb
where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
group by d_id, p_id, gx_name, w_name
) b
on a.d_id = b.d_id and a.p_id = b.p_id and a.gx_name = b.gx_name
/*
d_id p_id gx_name hg bl w_name
---------- ---------- ---------- ----------- ----------- ----------
001006 XHD65 包装 300 2 干检上漏
001006 XHD65 包装 300 1 干检下漏
*/
select a.p_id, a.gx_name, a.hg, b.w_name, b.bl
from
(select p_id, gx_name, w_name, sum(bl) as bl
from tb
where r_date between '2009-12-16' and '2009-12-17'
group by p_id, gx_name, w_name
) b,
(select p_id, gx_name, sum(hg) as hg
from (select distinct p_id, gx_name, r_date, hg from tb where r_date between '2009-12-16' and '2009-12-17') a1
group by p_id, gx_name
) a
where a.p_id = b.p_id and a.gx_name = b.gx_name
select a.d_id, a.p_id, a.gx_name, sum(a.hg) as hg, sum(b.bl) as bl, b.w_name
from
(
select distinct d_id, p_id, gx_name, hg from tb
where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
) a left join tb b
on a.d_id = b.d_id and a.p_id = b.p_id and a.gx_name = b.gx_name
where convert(char(10), b.r_date, 120) between '2009-12-16' and '2009-12-17'
group by a.d_id, a.p_id, a.gx_name, b.w_name
select a.d_id, a.p_id, a.gx_name, a.hg, sum(b.bl) as bl, b.w_name
from
(
select d_id, p_id, gx_name, max(hg) from tb
where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
group by d_id, p_id, gx_name
) a left join tb b
on a.d_id = b.d_id and a.p_id = b.p_id and a.gx_name = b.gx_name
where convert(char(10), b.r_date, 120) between '2009-12-16' and '2009-12-17'
group by a.d_id, a.p_id, a.gx_name, a.hg, b.w_name
select d_id, p_id, gx_name, hg, sum(bl) as bl, w_name
from tb where convert(char(10), r_date, 120) between '2009-12-16' and '2009-12-17'
group by d_id, p_id, gx_name, hg, w_name
select a.p_id, a.gx_name, a.hg, b.w_name, b.bl
from
(select p_id, gx_name, w_name, sum(bl) as bl
from tb
where r_date between '2009-12-16' and '2009-12-17'
group by p_id, gx_name, w_name
) b,
(select p_id, gx_name, sum(hg) as hg
from tb
where r_date between '2009-12-16' and '2009-12-17'
group by p_id, gx_name
) a
where a.p_id = b.p_id and a.gx_name = b.gx_name