请求大家指点该如何写这条sql语句？急【图】[

[ppyybb]

现在有如下的一张表格，
字段说明：
p_id：产品名称
gx_name:工序名称 hg:
hg:产品合格数量
bl：产品不良数量
w_name:不良项目
r_date:输入日期
其中一个产品（p_id）对应多道工序（gx_name)
我想实现的是在程序中通过选择日期，来统计每个产品（即p_id)的每道工序的产品合格数量（即hg）和不良数量（即bl）之和。以下图为例,当我在程序中选定日期从‘2009-12-16’到‘2009-12-17’时：
我想实现的格式是
d_id p_id gx_name hg bl w_name

001006 XHD65 焊接 300 2 干检上漏
... ... ... ... ... ...
... ... ... ... ... ...




我是用交叉表做的。

[qzyuanmu]

楼上的，请问 你的 pid 下的栏目怎么合并的，请教谢谢

[ppyybb]

[Quote=引用 5 楼 qzyuanmu 的回复:]
楼上的，请问  你的  pid 下的栏目怎么合并的，请教谢谢 
[/Quote]
用交叉表的。p_id下的栏目也当做row。p_id和栏目一对多的关系

[jimwoo]

不知你的不良项目是否固定。如果不固定的话，还是用交叉表方便。

[ppyybb]

[Quote=引用 7 楼 jimwoo 的回复:]
不知你的不良项目是否固定。如果不固定的话，还是用交叉表方便。
[/Quote]
我就是用交叉表做的。不良项目是不固定的，我选择‘2009-12-16’到‘2009-12-17’的时候，下图中，XHD65的包装工序中出现了两条，我想实现的是HG为300，就是一条显示出来就可以了，是这两条记录的hg的和以及不良项目的数量和。大侠有什么办法没？

[noviceCJ]

先汇总统计合格, 再统计不良

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 

[ppyybb]

[Quote=引用 9 楼 novicecj 的回复:]
先汇总统计合格, 再统计不良
SQL codeselect a.p_id, a.gx_name, a.hg, b.w_name, b.blfrom
(select p_id, gx_name, w_name,sum(bl)as blfrom tbwhere r_datebetween'2009-12-16'and'2009-12-17'groupby p_id, gx_name, w_name
) b,
(select p_id, gx_name,sum(hg)as hgfrom tbwhere r_datebetween'2009-12-16'and'2009-12-17'groupby p_id, gx_name
) awhere a.p_id= b.p_idand a.gx_name= b.gx_name
[/Quote]


找大侠您这样的语句出来的结果是这样的，如下图：
日期选择“2009-12-16”到“2009-12-16”

这里XHD65的包装工序中合格数量多了原来的一倍，本来是200才对。大侠请问什么原因啊？

[ppyybb]

日期选择“2009-12-16”到“2009-12-17”

[ppyybb]

请大侠们帮帮我啊，万分感谢了。555

[jimwoo]

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

[ppyybb]

[Quote=引用 13 楼 jimwoo 的回复:]
SQL codeselect d_id, p_id, gx_name, hg,sum(bl)as bl, w_namefrom tbwhereconvert(char(10), r_date,120)between'2009-12-16'and'2009-12-17'groupby d_id, p_id, gx_name, hg, w_name
[/Quote]
大侠，这样做我也试过了，会出现8楼所说的情况。

[永生天地]

你的数据里是有两个200 hg

[ppyybb]

在下图这张表中，第一条记录和第三条记录其实就是同一个hg，所以应该200就可以了，但是现在变成400了。即除了w_name(不良项目)和bl（不良数）不一样外，其他字段的值都一样。像这种记录我都不需要重复进行求和。求大侠继续指点。谢谢了。

[jimwoo]

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

[jimwoo]

上面的还是不对

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

[ppyybb]

[Quote=引用 18 楼 jimwoo 的回复:]
上面的还是不对
SQL codeselect a.d_id, a.p_id, a.gx_name,sum(a.hg)as hg,sum(b.bl)as bl, b.w_namefrom
(selectdistinct d_id, p_id, gx_name, hgfrom tbwhereconvert(char(10), r_date,120)between'2009-12-16'and'2009-12-17'
) aleftjoin tb bon a.d_id= b.d_idand a.p_id= b.p_idand a.gx_name= b.gx_namewhereconvert(char(10), b.r_date,120)between'2009-12-16'and'2009-12-17'groupby a.d_id, a.p_id, a.gx_name, b.w_name
[/Quote]
大侠，找你的sql语句运行结果是这样的，当我选择‘2009-12-16’到‘2009-12-17’时，运行结果如下：

包装下面多了一个hg为600的记录，并且不良项目那里也不对，"干检上漏"为1.“干检下漏”为2

[noviceCJ]

同一种产品, 一天是不是最多只有一个合格数? 如果同一天里有两批一样的合格数, 那你这样的表结构是很难解决的
如果是一天最多一个合格数, 试试下面的sql

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 

[jimwoo]

搞死了

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            干检下漏 
*/

[jimwoo]

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            干检下漏 
*/

[ppyybb]

[Quote=引用 20 楼 novicecj 的回复:]
同一种产品, 一天是不是最多只有一个合格数? 如果同一天里有两批一样的合格数, 那你这样的表结构是很难解决的
如果是一天最多一个合格数, 试试下面的sql
SQL codeselect a.p_id, a.gx_name, a.hg, b.w_name, b.blfrom
(select p_id, gx_name, w_name,sum(bl)as blfrom tbwhere r_datebetween'2009-12-16'and'2009-12-17'groupby p_id, gx_name, w_name
) b,
(select p_id, gx_name,sum(hg)as hgfrom (selectdistinct p_id, gx_name, r_date, hgfrom tbwhere r_datebetween'2009-12-16'and'2009-12-17') a1groupby p_id, gx_name
) awhere a.p_id= b.p_idand a.gx_name= b.gx_name
[/Quote]

谢谢大侠。我就是一天只有一个合格数量的，谢谢。万分感谢。

[ppyybb]

[Quote=引用 22 楼 jimwoo 的回复:]
SQL codedeclare@tbtable (d_idchar(10), p_idchar(10), gx_namechar(10), hgint, blint, w_namechar(10), r_datedatetime)insert@tbselect'001006','XHD65','包装',200,1,' 干检上漏','2009-12-16'unionallselect'001006'?-
[/Quote]

大侠辛苦了，谢谢了。万分感谢。我已经解决了。