现在我有三个表:
Product_master:主要记录的是申请人申请的基本信息。字段有:
FlowId(关键字段),Emp_Id(申请人ID),Apl_AreaId(申请区域ID),Apl_StarDate(申请开始时间),Apl_EndDate(申请结束时间)
Product_detail:申请的详细记录。字段有:
FlowId(与Product_master表中的FlowId是多对一的关系),Pro_id(产品ID),Pro_Name(产品名称),
Product_init:产品初始表,即对每种产品付有一个初始的值。
字段有:
ID(自增型字段,没有意义),Area_id(区域ID),pro_id(产品ID),Grant_num(初始的数目。)
现在要求是求出每个区域申请了哪些种产品,且这种产品在这个区域申请了多少。并且要求产品的数目要加上初始表中区域对应的产品ID的数请朋友们帮忙。。请多多指教。
Product_master:主要记录的是申请人申请的基本信息。字段有:
FlowId(关键字段),Emp_Id(申请人ID),Apl_AreaId(申请区域ID),Apl_StarDate(申请开始时间),Apl_EndDate(申请结束时间)
Product_detail:申请的详细记录。字段有:
FlowId(与Product_master表中的FlowId是多对一的关系),Pro_id(产品ID),Pro_Name(产品名称),
Product_init:产品初始表,即对每种产品付有一个初始的值。
字段有:
ID(自增型字段,没有意义),Area_id(区域ID),pro_id(产品ID),Grant_num(初始的数目。)
现在要求是求出每个区域申请了哪些种产品,且这种产品在这个区域申请了多少。并且要求产品的数目要加上初始表中区域对应的产品ID的数请朋友们帮忙。。请多多指教。
insert into #t
select 'a',123,NULL
union all select 'a',234,'Green'
union all select 'a',345,NULL
union all select 'b',123,NULL
union all select 'b',234,NULL
union all select 'b',345,'Red'
union all select 'c',123,'Green'
union all select 'c',234,NULL
union all select 'c',345,NULL
select * from #t
order by isnull(DoorColor,(select max(DoorColor) from #t a where a.SaleorderID=#t.SaleorderID)),saleorderID,case when DoorColor is not null then 0 else UnitID end
/*
SaleorderID UnitID DoorColor
----------- ----------- --------------------
a 234 Green
a 123 NULL
a 345 NULL
c 123 Green
c 234 NULL
c 345 NULL
b 345 Red
b 123 NULL
b 234 NULL(所影响的行数为 9 行)
*/
回复:是的,没有申请数量,一个PRO_ID为一数量,这个数量是要你来汇总或者COUNT出来的,这要根据区域及PRO_ID来算的。
Product_detail.Pro_id,sum(Product_detail.数量)
+(select sum(Grant_num) from Product_init where pro_id =a.pro_id and AreaId = a.AreaId)
from Product_master join Product_detail a on Product_master.FlowId = Product_detail.FlowId
group by Product_master.AreaId 没经过测试
gahade(与君共勉) I 服了YOU!!! 好在我先看的下面的,要不我就死在你手里了!!
---------------------------------------------------
哈哈哈
回复:求出每个每个区域(即Area_ID)中PRO_ID的个数,这么说明白吗!并且要加上初始值。
(select pm.Apl_AreaId,count(pd.*) as count from Product_master pm,Product_detail pd
where pm.FlowId=pd.FlowId
group by pm.Apl_AreaId) a,Product_init pi
where a.Apl_AreaId=pi.Area_id没测试,如果理解没错的话错误不大
先求区域中的数量count(*)
select a.Apl_AreaId,b.Pro_id,c.Grant_num+counts as Totals
from Product_master a inner join
(select x.Apl_AreaId,y.Pro_id,count(1) as counts from Product_master x inner join Product_detail y on x.FlowId=y.FlowId group by x.Apl_AreaId,y.Pro_id) b
on a.Apl_AreaId=b.Apl_AreaId and c.Pro_id=b.Pro_id
inner join Product_init c on a.Apl_AreaId=c.Apl_AreaId and c.Pro_id=b.Pro_id
select a.FlowId,a.Product_master,a.Apl_AreaId, count(Pro_id),sum_n+count(Pro_id), Pro_Name from Product_master a left inner join Product_detail b on a.FlowId=b.FlowIdleft inner join (select pro_id,Area_id,sum(Grant_num) sum_n from Product_init group by pro_id,Area_id) c on a.Apl_AreaId=c.Area_id and a.pro_id=c.pro_idgroup by a.FlowId,a.Product_master,a.Apl_AreaId,Pro_id,Pro_Name
不知道對不對~怪怪的感覺
FlowId Emp_Id Apl_AreaId Apl_StarDate Apl_EndDate
1111 MX_0001 BJ_001 2006/12/23 2006/12/27
1112 MX_0021 BJ_021 2006/12/23 2006/12/27
1113 MX_0002 BJ_002 2006/12/24 2006/12/27Product_detail:
FlowId Pro_id Pro_Name
1111 SH_001 XXXXXXXX
1111 SH_002 XXXXXXXX
1112 SH_001 XXXXXXXX
1112 SH_002 XXXXXXXX
1113 SH_002 XXXXXXXX Product_init:
ID Area_id pro_id Grant_num
1 BJ_001 SH_002 54
2 BJ_001 SH_001 51
3 BJ_021 SH_001 30求出结果是:
Area_id pro_id num
BJ_001 SH_002 55
BJ_001 SH_001 52
BJ_021 SH_001 31
BJ_021 SH_002 1
BJ_002 SH_002 1
.........
这样明白一些了吧。累死我了。希望没写错!!!
(
flowId integer,
apl_areaid integer
);
create table product_list
(
flowId integer,
pro_name varchar(10),
pro_id integer
);
create table product_init
(
pro_id integer,
grant_num integer
)insert into product_mater values (1,1);
insert into product_mater values (2,1);
insert into product_mater values (3,1);
insert into product_mater values (1,2);
insert into product_mater values (4,2);insert into product_list values (1,'a',1);
insert into product_list values (2,'b',2);
insert into product_list values (3,'a',1);
insert into product_list values (4,'a',1);insert into product_init values (1,10);
insert into product_init values (2,100);select count(b.pro_id) num , sum(c.grant_num) summ
from product_mater a inner join product_list b
on a.flowId = b.flowid
inner join product_init c
on b.pro_id = c.pro_id;
(select pm.Apl_AreaId,count(pd.*) as count from Product_master pm,Product_detail pd
where pm.FlowId=pd.FlowId
group by pm.Apl_AreaId) a,Product_init pi
where a.Apl_AreaId=pi.Area_id
(select pm.Apl_AreaId,count(pd.*) as count from Product_master pm,Product_detail pd
where pm.FlowId=pd.FlowId
group by pm.Apl_AreaId) a,Product_init pi
where a.Apl_AreaId=pi.Area_id