现在表结构是这样的
A (主表)
ID DocNo
1 AA0001B(子表)
ID AID Car Item Num(装载数)
10 1 大卡车1 产品1 10000
20 1 大卡车2 产品1 10000
30 1 大卡车2 产品2 200C(子表)
ID AID Car JYX(检验项) YKZL(应扣重量) TSJY3XZS(特殊检验项3只数)
10 1 大卡车1 检验项1的ID 199 0
20 1 大卡车1 检验项2的ID 299 0
30 1 大卡车1 检验项3的ID 399 100JYX(C表的基础表)
JYXID Name
100 检验项1
200 检验项2
300 检验项3如何弄成 结果为单号 车 产品1装载数 产品2的装载数 检验项1应扣重量 检验项2应扣重量 检验项3应扣重量 检验项3只数
AA0001 大卡车1 10000 0 199 299 399 100
AA0001 大卡车2 10000 200 0 0 0 0
求解!
这个是我最后希望的结果!
单号 车 产品1装载数 产品2的装载数 检验项1应扣重量 检验项2应扣重量 检验项3应扣重量 检验项3只数
AA0001 大卡车1 10000 0 199 299 399 100
AA0001 大卡车2 10000 200 0 0 0 0--------------------------
刚才帖子 我照着 这么写
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
sum(case (select Name from JYX where id=c.JYX) when '检验项1’ then YKZL else 0 end) [检验项1应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项2’ then YKZL else 0 end) [检验项2应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项3’ then YKZL else 0 end) [检验项3应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项3’ then TSJY3XZS else 0 end) [检验项3只数]
from a , b ,c
where a.ID = b.aID and a.ID =c.aID
group by a.DocNo,b.Car,c.JYX他就报错了
消息 130,级别 15,状态 1,第 22 行
不能对包含聚合或子查询的表达式执行聚合函数。如果我不用sum的话
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
case (select Name from JYX where id=c.JYX) when '检验项1’ then YKZL else 0 end) [检验项1应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项2’ then YKZL else 0 end) [检验项2应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项3’ then YKZL else 0 end) [检验项3应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项3’ then TSJY3XZS else 0 end) [检验项3只数]
from a , b ,c
where a.ID = b.aID and a.ID =c.aID
group by a.DocNo,b.Car,c.JYX
会把C表的 数据全叠加起来。求解。
A (主表)
ID DocNo
1 AA0001B(子表)
ID AID Car Item Num(装载数)
10 1 大卡车1 产品1 10000
20 1 大卡车2 产品1 10000
30 1 大卡车2 产品2 200C(子表)
ID AID Car JYX(检验项) YKZL(应扣重量) TSJY3XZS(特殊检验项3只数)
10 1 大卡车1 检验项1的ID 199 0
20 1 大卡车1 检验项2的ID 299 0
30 1 大卡车1 检验项3的ID 399 100JYX(C表的基础表)
JYXID Name
100 检验项1
200 检验项2
300 检验项3如何弄成 结果为单号 车 产品1装载数 产品2的装载数 检验项1应扣重量 检验项2应扣重量 检验项3应扣重量 检验项3只数
AA0001 大卡车1 10000 0 199 299 399 100
AA0001 大卡车2 10000 200 0 0 0 0
求解!
这个是我最后希望的结果!
单号 车 产品1装载数 产品2的装载数 检验项1应扣重量 检验项2应扣重量 检验项3应扣重量 检验项3只数
AA0001 大卡车1 10000 0 199 299 399 100
AA0001 大卡车2 10000 200 0 0 0 0--------------------------
刚才帖子 我照着 这么写
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
sum(case (select Name from JYX where id=c.JYX) when '检验项1’ then YKZL else 0 end) [检验项1应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项2’ then YKZL else 0 end) [检验项2应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项3’ then YKZL else 0 end) [检验项3应扣重量],
sum(case (select Name from JYX where id=c.JYX) when '检验项3’ then TSJY3XZS else 0 end) [检验项3只数]
from a , b ,c
where a.ID = b.aID and a.ID =c.aID
group by a.DocNo,b.Car,c.JYX他就报错了
消息 130,级别 15,状态 1,第 22 行
不能对包含聚合或子查询的表达式执行聚合函数。如果我不用sum的话
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
case (select Name from JYX where id=c.JYX) when '检验项1’ then YKZL else 0 end) [检验项1应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项2’ then YKZL else 0 end) [检验项2应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项3’ then YKZL else 0 end) [检验项3应扣重量],
case (select Name from JYX where id=c.JYX) when '检验项3’ then TSJY3XZS else 0 end) [检验项3只数]
from a , b ,c
where a.ID = b.aID and a.ID =c.aID
group by a.DocNo,b.Car,c.JYX
会把C表的 数据全叠加起来。求解。
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
sum(case c.JYX when '检验项1的ID' then YKZL else 0 end) [检验项1应扣重量],
sum(case c.JYX when '检验项2的ID' then YKZL else 0 end) [检验项2应扣重量],
sum(case c.JYX when '检验项3的ID' then YKZL else 0 end) [检验项3应扣重量],
from a , b , c , d
where a.ID = b.aID and a.id = c.aid and c.JYX = d.JYXID
group by a.DocNo,b.Carselect a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数],
sum(case d.Name when '检验项1' then YKZL else 0 end) [检验项1应扣重量],
sum(case d.Name when '检验项2' then YKZL else 0 end) [检验项2应扣重量],
sum(case d.Name when '检验项3' then YKZL else 0 end) [检验项3应扣重量]
from a , b , c , JYX d
where a.ID = b.aID and a.id = c.aid and c.JYX = d.JYXID
group by a.DocNo,b.Car
isnull(m.Car,n.Car) Car,
m.产品1装载数,
m.产品1装载数,
n.检验项1应扣重量,
n.检验项2应扣重量,
n.检验项3应扣重量,
n.[检验项3只数]
from
(
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数]
from a , b
where a.ID = b.aID and a.id = c.aid and c.JYX = d.JYXID
group by a.DocNo,b.Car
) m full join
(
select a.DocNo,c.Car,
sum(case d.Name when '检验项1' then YKZL else 0 end) [检验项1应扣重量],
sum(case d.Name when '检验项2' then YKZL else 0 end) [检验项2应扣重量],
sum(case d.Name when '检验项3' then YKZL else 0 end) [检验项3应扣重量],
sum(case d.Name when '检验项3' then TSJY3XZS else 0 end) [检验项3只数]
from a , c , JYX d
where a.id = c.aid and c.JYX = d.JYXID
group by a.DocNo,c.Car
) n
on m.DocNo = n.DocNo and m.car = n.car
insert into a values(1 ,'AA0001')
create table B(ID int,AID int,Car varchar(10),Item varchar(10),Num int)
insert into b values(10 ,1 ,'大卡车1', '产品1', 10000)
insert into b values(20 ,1 ,'大卡车2', '产品1', 10000)
insert into b values(30 ,1 ,'大卡车2', '产品2', 200)
create table C(ID int,AID int,Car varchar(10),JYX int, YKZL int,TSJY3XZS int)
insert into c values(10 ,1 ,'大卡车1', 100, 199 ,0)
insert into c values(20 ,1 ,'大卡车1', 200, 299 ,0)
insert into c values(30 ,1 ,'大卡车1', 300, 399 ,100)
create table JYX(JYXID int,Name varchar(10))
insert into jyx values(100 ,'检验项1')
insert into jyx values(200 ,'检验项2')
insert into jyx values(300 ,'检验项3')
go
select isnull(m.DocNo,n.DocNo) DocNo,
isnull(m.Car,n.Car) Car,
isnull(m.产品1装载数,0) 产品1装载数,
isnull(m.产品2装载数,0) 产品2装载数,
isnull(n.检验项1应扣重量,0) 检验项1应扣重量,
isnull(n.检验项2应扣重量,0) 检验项2应扣重量,
isnull(n.检验项3应扣重量,0) 检验项3应扣重量,
isnull(n.检验项3只数,0) 检验项3只数
from
(
select a.DocNo,b.Car,
sum(case b.Item when '产品1' then Num else 0 end) [产品1装载数],
sum(case b.Item when '产品2' then Num else 0 end) [产品2装载数]
from a , b
where a.ID = b.aID
group by a.DocNo,b.Car
) m full join
(
select a.DocNo,c.Car,
sum(case d.Name when '检验项1' then YKZL else 0 end) [检验项1应扣重量],
sum(case d.Name when '检验项2' then YKZL else 0 end) [检验项2应扣重量],
sum(case d.Name when '检验项3' then YKZL else 0 end) [检验项3应扣重量],
sum(case d.Name when '检验项3' then TSJY3XZS else 0 end) [检验项3只数]
from a , c , JYX d
where a.id = c.aid and c.JYX = d.JYXID
group by a.DocNo,c.Car
) n
on m.DocNo = n.DocNo and m.car = n.cardrop table a , b,c,jyx/*
DocNo Car 产品1装载数 产品2装载数 检验项1应扣重量 检验项2应扣重量 检验项3应扣重量 检验项3只数
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
AA0001 大卡车1 10000 0 199 299 399 100
AA0001 大卡车2 10000 200 0 0 0 0(所影响的行数为 2 行)
*/
use tempdb;
/*
create table A
(
ID int not null,
DocNo nvarchar(10) not null
);
insert into A(ID,DocNo)
values
(1,'AA0001');create table B
(
ID int not null,
AID int not null,
Car nvarchar(10) not null,
Item nvarchar(10) not null,
Num int not null
);
insert into B(ID,AID,Car,Item,Num)
values
(10,1,'大卡车1','产品1',10000),
(20,1,'大卡车2','产品1',10000),
(30,1,'大卡车2','产品2',200);create table C
(
ID int not null,
AID int not null,
Car nvarchar(10) not null,
JYX int not null,
YKZL int not null,
TSJY3XZS int not null
);
insert into C(ID,AID,Car,JYX,YKZL,TSJY3XZS)
values
(10,1,'大卡车1',100,199,0),
(20,1,'大卡车1',200,299,0),
(30,1,'大卡车1',300,399,100);create table JYX
(
JYXID int not null,
Name nvarchar(10) not null
);
insert into JYX(JYXID,Name)
values
(100,'检验项1'),
(200,'检验项2'),
(300,'检验项3');
*/
select
t1.单号,t1.车,
ISNULL(t1.产品1装载数,0) as [产品1装载数],
ISNULL(t1.产品2装载数,0) as [产品2装载数],
ISNULL(t2.检验项1应扣重量,0) as [检验项1应扣重量],
ISNULL(t2.检验项2应扣重量,0) as [检验项2应扣重量],
ISNULL(t2.检验项3应扣重量,0) as [检验项3应扣重量]
from
(
select A.DocNo as [单号],B.Car as [车],
SUM(case when B.Item = '产品1' then B.Num else 0 end) as [产品1装载数],
SUM(case when B.Item = '产品2' then B.Num else 0 end) as [产品2装载数]
from A
join B on A.ID = B.AID
group by A.DocNo,B.Car
) as t1
full join
(
select A.DocNo as [单号],C.Car as [车],
SUM(case when C.JYX = 100 then C.YKZL else 0 end) as [检验项1应扣重量],
SUM(case when C.JYX = 200 then C.YKZL else 0 end) as [检验项2应扣重量],
SUM(case when C.JYX = 300 then C.YKZL else 0 end) as [检验项3应扣重量]
from A
join C on A.ID = C.AID
group by A.DocNo,C.Car
) as t2
on t1.单号 = t2.单号 and t1.车 = t2.车;