现在有如下的一张表格，
字段说明：
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 干检上漏
... ... ... ... ... ...
... ... ... ... ... ...
我是用交叉表做的。
------解决方案--------------------
同一种产品, 一天是不是最多只有一个合格数? 如果同一天里有两批一样的合格数, 那你这样的表结构是很难解决的
如果是一天最多一个合格数, 试试下面的sql
- SQL code
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 blfrom 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 hgfrom (select distinct p_id, gx_name, r_date, hg from tb where r_date between '2009-12-16' and '2009-12-17') a1group by p_id, gx_name) awhere a.p_id = b.p_id and a.gx_name = b.gx_name
------解决方案--------------------
搞死了
- SQL code
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 @tbselect '001006', 'XHD65', '包装', 200, 1, ' 干检上漏', '2009-12-16' union allselect '001006', 'XHD65', '包装', 100, 1, ' 干检上漏', '2009-12-17' union allselect '001006', 'XHD65', '包装', 200, 1, ' 干检下漏', '2009-12-16'select a.d_id, a.p_id, a.gx_name, a.hg, b.bl, b.w_namefrom( 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) bon 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 干检下漏 */