多表分类统计
A表（合同基本信息表）
ID 合同号 合同签订日期
1 cn001 2012-1-20
2 cn002 2012-2-10
3 ......
B表（产品价格表）
ID 合同号 产品编号 单价
1 cn001 111 0.5
2 cn001 112 0.8
3 cn001 113 0.5
4 cn002 2-1 0.6
5 cn002 2-2 0.6
C表（产品几班信息表）
ID 合同号 产品编号 数量 产品名称 产品规格 颜色 单件重 产品类别
1 cn001 111 100 铅笔 0.5mm 白色 0.47 自产
2 cn001 112 50 铅笔 1mm 蓝色 1.2 自产
3 cn001 113 100 橡皮 2X2 白色 0.87 外购
4 cn002 2-1 100 铅笔 1mm 黄色 1.5 自产
5 cn002 2-2 100 橡皮 2X2 黄色 0.65 自产
D表（产品所需主料表）
ID 合同号 产品编号 所需主料 数量 单价
1 cn001 111 木材 100 28
2 cn001 111 铅 200 10.8
3 cn001 112 木材 80 38
4 cn001 112 铅 100 13
E表（产品所需辅料表）
ID 合同号 产品编号 所需辅料 数量 单价
1 cn001 111 标签 100 1
2 cn001 111 纸箱 200 5.8
3 cn001 112 外壳 80 2.2
4 。。。。。
需要得到以下表格，
产品名称 规格 颜色 数量 包装 销售总价 总重量 所需主料总价 所需辅料总价
汇总出同名称，同规格，同颜色，同包装的自产的产品的汇总信息，颜色除了白色，其他颜色归并为彩色，包装分精包和散包，单件重小于1的为精包，大于1的为散包
[解决办法]
--> 测试数据: @A表declare @A表 table (ID int,合同号 varchar(5),合同签订日期 datetime)insert into @A表select 1,'cn001','2012-1-20' union allselect 2,'cn002','2012-2-10'--> 测试数据: @B表declare @B表 table (ID int,合同号 varchar(5),产品编号 varchar(3),单价 numeric(2,1))insert into @B表select 1,'cn001','111',0.5 union allselect 2,'cn001','112',0.8 union allselect 3,'cn001','113',0.5 union allselect 4,'cn002','2-1',0.6 union allselect 5,'cn002','2-2',0.6--> 测试数据: @C表declare @C表 table (ID int,合同号 varchar(5),产品编号 varchar(3),数量 int,产品名称 varchar(4),产品规格 varchar(5),颜色 varchar(4),单件重 numeric(3,2),产品类别 varchar(4))insert into @C表select 1,'cn001','111',100,'铅笔','0.5mm','白色',0.47,'自产' union allselect 2,'cn001','112',50,'铅笔','1mm','蓝色',1.2,'自产' union allselect 3,'cn001','113',100,'橡皮','2X2','白色',0.87,'外购' union allselect 4,'cn002','2-1',100,'铅笔','1mm','黄色',1.5,'自产' union allselect 5,'cn002','2-2',100,'橡皮','2X2','黄色',0.65,'自产'--> 测试数据: @D表declare @D表 table (ID int,合同号 varchar(5),产品编号 int,所需主料 varchar(4),数量 int,单价 numeric(3,1))insert into @D表select 1,'cn001',111,'木材',100,28 union allselect 2,'cn001',111,'铅',200,10.8 union allselect 3,'cn001',112,'木材',80,38 union allselect 4,'cn001',112,'铅',100,13--> 测试数据: @E表declare @E表 table (ID int,合同号 varchar(5),产品编号 int,所需辅料 varchar(4),数量 int,单价 numeric(2,1))insert into @E表select 1,'cn001',111,'标签',100,1 union allselect 2,'cn001',111,'纸箱',200,5.8 union allselect 3,'cn001',112,'外壳',80,2.2--很多字段没有说清楚规则，例如包装字段找不到，数量有2个，销售总价是 数量*单价+数量*单价吗？select c.产品名称,c.产品规格,c.颜色,d.数量 AS dsl ,e.数量 AS esl,所需辅料 AS 包装 ,d.单价 AS d销售总价, e.单价 AS e销售总价,c.单件重 as 总重量from @A表 a LEFT JOIN @B表 b ON a.合同号=b.合同号LEFT JOIN @C表 c ON a.合同号=c.合同号LEFT JOIN @D表 d ON a.合同号=d.合同号LEFT JOIN @E表 e ON a.合同号=e.合同号/*产品名称 产品规格 颜色 dsl esl 包装 d销售总价 e销售总价 总重量---- ----- ---- ----------- ----------- ---- --------------------------------------- --------------------------------------- ---------------------------------------铅笔 0.5mm 白色 100 100 标签 28.0 1.0 0.47铅笔 0.5mm 白色 100 200 纸箱 28.0 5.8 0.47铅笔 0.5mm 白色 100 80 外壳 28.0 2.2 0.47铅笔 0.5mm 白色 200 100 标签 10.8 1.0 0.47铅笔 0.5mm 白色 200 200 纸箱 10.8 5.8 0.47铅笔 1mm 黄色 NULL NULL NULL NULL NULL 1.50橡皮 2X2 黄色 NULL NULL NULL NULL NULL 0.65铅笔 1mm 黄色 NULL NULL NULL NULL NULL 1.50橡皮 2X2 黄色 NULL NULL NULL NULL NULL */
[解决办法]
case when C.颜色='白色'then '白色' else '彩色' end as 颜色--实现：颜色除了白色，其他颜色归并为彩色
case when ...<1 then '精包'else '散包' end as 包装--实现：包装分精包和散包，单件重小于1的为精包，大于1的为散包
[解决办法]
if object_id('a') is not nulldrop table acreate table a(id int identity(1,1) not null,合同号 varchar(10) ,合同签定日期 datetime)insert a(合同号, 合同签定日期)select 'cn001','2012-1-20' union allselect 'cn002','2012-2-10' if object_id('b') is not nulldrop table bcreate table b(id int identity(1,1) not null,合同号 varchar(10) ,产品编号 varchar(10),单价 float)insert b(合同号,产品编号,单价)select 'cn001','111',0.5 union allselect 'cn001','112',0.8 union allselect 'cn001','113',0.5 union allselect 'cn002','2-1',0.6 union allselect 'cn002','2-2',0.6if object_id('c') is not nulldrop table ccreate table c(id int identity(1,1) not null,合同号 varchar(10) ,产品编号 varchar(10),数量 int,产品名称 varchar(20),产品规格 varchar(40),颜色 varchar(10),单件重 float,产品类别 varchar(10))insert c(合同号,产品编号,数量,产品名称,产品规格,颜色,单件重,产品类别)select 'cn001','111',100,'铅笔','0.55mm','白色',0.47,'自产' union allselect 'cn001','112',50,'铅笔','1mm','蓝色',1.2,'自产' union allselect 'cn001','113',100,'橡皮','2X2','白色',0.87,'外购' union allselect 'cn002','2-1',100,'铅笔','1mm','黄色',1.5,'自产' union allselect 'cn002','2-2',100,'橡皮','2X2','黄色',0.65,'自产' if object_id('d') is not nulldrop table dcreate table d(id int identity(1,1) not null,合同号 varchar(10) ,产品编号 varchar(10),所需主料 varchar(10),数量 int,单价 float)insert d(合同号,产品编号,所需主料,数量,单价)select 'cn001','111','木材',100, 28 union allselect 'cn001','111','铅', 200 ,10.8 union allselect 'cn001','112','木材', 80, 38 union allselect 'cn001','112','铅', 100, 13if object_id('e') is not nulldrop table ecreate table e(id int identity(1,1) not null,合同号 varchar(10) ,产品编号 varchar(10),所需辅料 varchar(10),数量 int,单价 float)insert e(合同号,产品编号,所需辅料,数量,单价)select 'cn001','111','标签',100, 1 union allselect 'cn001','111','纸箱', 200 ,5.8 union allselect 'cn001','112','外壳', 80, 2.2 /*查询语句 */select 产品名称,产品规格,颜色,包装,sum(数量)数量,sum(总重量)总重量,sum(销售总价)销售总价,sum(主料总价)主料总价,sum(辅料总价)辅料总价from (select c.合同号,c.产品编号,c.产品名称,c.产品规格,c.数量,(c.数量*c.单件重)总重量,(case when c.颜色='白色' then c.颜色 else '彩色' end )颜色,(case when c.单件重>1 then '散包' else '精装' end )包装, c.产品类别,(c.数量*b.单价)销售总价,d.主料总价,e.辅料总价 from cleft join b on c.产品编号=b.产品编号 and c.合同号=b.合同号left join (select 合同号,产品编号,sum(数量*单价) 主料总价 from d group by 合同号,产品编号)d on c.产品编号=d.产品编号 and c.合同号=d.合同号left join (select 合同号,产品编号,sum(数量*单价) 辅料总价 from e group by 合同号,产品编号)e on c.产品编号=e.产品编号 and c.合同号=e.合同号where c.产品类别='自产') T group by 产品名称,产品规格,颜色,包装/*结果*//*产品名称 产品规格 颜色 包装 数量 总重量 销售总价 主料总价 辅料总价-------------------- ---------------------------------------- ---------- ---- ----------- ---------------------- ---------------------- ---------------------- ----------------------铅笔 0.55mm 白色 精装 100 47 50 4960 1260铅笔 1mm 彩色 散包 150 210 100 4340 176橡皮 2X2 彩色 精装 100 65 60 NULL NULL警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/