3表联合查询，有sum汇总

[nimeide1234567890]

A表字段，id是自增
id jsr type status
313 001 1 0
314 002 1 0
315 001 1 0

B表
id djbh bh dj(单价) sl(数量) je(金额)
1 313 0000000001 5 1 5
2 313 0000000002 5 1 5
3 314 0000000001 4 1 4
4 315 0000000001 3 1 3
注：A表的id等于B表的djbh，关联的，A是单据头，B是单身，B表的id是自增

C表
bh qm
0000000001 花生
0000000002 牛奶
注：B表的bh（商品编号）等于C表的bh，关联的


现在要得到A表里的id,jsr,type,stauts,B表里的djbh,bh,dj,sl,C表里的bh,qm,并且B表里的sl,je要按bh（编号）汇总，比较难，分不够可以加

[中国风]

[Quote=引用 3 楼 luojianfeng 的回复:]

select a.jsr,a.type,a.status,b.bh,c.qm,sum(b.sl) as sl,b.dj,sum(b.je) as je
from A a left join B b on a.id=b.djbh left join C c on b.bh=c.bh
group by b.bh

这句是错的，错的一塌糊涂，我就是想这么个意思
[/Quote]

以上數據提供一個顯示結果給大家看看

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] int,[jsr] nvarchar(3),[type] int,[status] int)
Insert #A
select 313,N'001',1,0 union all
select 314,N'002',1,0 union all
select 315,N'001',1,0
Go

--> --> 
 
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([id] int,[djbh] int,[bh] nvarchar(10),[dj] int,[sl] int,[je] int)
Insert #B
select 1,313,N'0000000001',5,1,5 union all
select 2,313,N'0000000002',5,1,5 union all
select 3,314,N'0000000001',4,1,4 union all
select 4,315,N'0000000001',3,1,3
Go

if not object_id(N'Tempdb..#C') is null
	drop table #C
Go
Create table #C([bh] nvarchar(10),[qm] nvarchar(2))
Insert #C
select N'0000000001',N'花生' union all
select N'0000000002',N'牛奶'
Go
Select a.jsr,a.type,a.status,b.bh,c.[qm],sum(b.sl) AS sl,sum(b.je) as je
from #A AS a 
	INNER JOIN #B AS b ON a.id=b.djbh
	INNER JOIN #C AS c ON c.bh=b.bh
GROUP BY a.jsr,a.type,a.status,b.bh,c.[qm]
	
/*
jsr	type	status	bh	qm	sl	je
001	1	0	0000000001	花生	2	8
001	1	0	0000000002	牛奶	1	5
002	1	0	0000000001	花生	1	4
*/

[nimeide1234567890]

晕啊，有显示那不就写出来了

[nimeide1234567890]

我要的结果是：

jsr type status bh dj(单价) sl(数量) je(金额) qm
001 1 0 0000000001 4 3 12 花生
002 1 0 0000000002 1 5 5 牛奶

[中国风]

能舉點有代表性的數據麼?
按商品顯示，沒有訂單的商品顯示為0

Select 
	c.bh,
	c.[qm],
	a.jsr,
	a.type,
	a.status,
	sum(b.sl) AS sl,
	sum(b.je) as je
from (#A AS a 
	INNER JOIN #B AS b ON a.id=b.djbh)
	right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.jsr,a.type,a.status,c.bh,c.[qm]

[中国风]

[Quote=引用 7 楼 luojianfeng 的回复:]

我要的结果是：

jsr type status bh dj(单价) sl(数量) je(金额) qm
001 1 0 0000000001 4 3 12 花生
002 1 0 0000000002 1 5 ……
[/Quote]
結果不正確jsr 對應記錄

002 1 0 0000000001 花生 1 4 ?

[中国风]

jsr--這個字段只有不要或重排，才會出現樓主提供的結果
jsr--取相同bh最大的

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] int,[jsr] nvarchar(3),[type] int,[status] int)
Insert #A
select 313,N'001',1,0 union all
select 314,N'002',1,0 union all
select 315,N'001',1,0
Go

--> --> 
 
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([id] int,[djbh] int,[bh] nvarchar(10),[dj] int,[sl] int,[je] int)
Insert #B
select 1,313,N'0000000001',5,1,5 union all
select 2,313,N'0000000002',5,1,5 union all
select 3,314,N'0000000001',4,1,4 union all
select 4,315,N'0000000001',3,1,3
Go

if not object_id(N'Tempdb..#C') is null
	drop table #C
Go
Create table #C([bh] nvarchar(10),[qm] nvarchar(2))
Insert #C
select N'0000000001',N'花生' union all
select N'0000000002',N'牛奶'
Go
Select 
	c.bh,
	c.[qm],
	max(a.jsr) AS jsr,
	a.type,
	a.status,
	sum(b.sl) AS sl,
	sum(b.je) as je
from (#A AS a 
	INNER JOIN #B AS b ON a.id=b.djbh)
	right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.type,a.status,c.bh,c.[qm]
	
/*
bh	qm	jsr	type	status	sl	je
0000000001	花生	002	1	0	3	12
0000000002	牛奶	001	1	0	1	5
*/

[nimeide1234567890]

相同bh编号的商品要SUM汇总sl和je，并且bh不能相同，就是商品sl和je汇总

[中国风]

[Quote=引用 11 楼 luojianfeng 的回复:]

相同bh编号的商品要SUM汇总sl和je，并且bh不能相同，就是商品sl和je汇总
[/Quote]
jsr--取相同bh最大的,用

Select 
    c.bh,
    c.[qm],
    max(a.jsr) AS jsr,
    a.type,
    a.status,
    sum(b.sl) AS sl,
    sum(b.je) as je
from (#A AS a 
    INNER JOIN #B AS b ON a.id=b.djbh)
    right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.type,a.status,c.bh,c.[qm]

[nimeide1234567890]

还要je/sl,也就是还要得到单价

GROUP BY a.type,a.status,c.bh,c.[qm]
这样写显然数据会重复

[中国风]

[Quote=引用 13 楼 luojianfeng 的回复:]

还要je/sl,也就是还要得到单价

GROUP BY a.type,a.status,c.bh,c.[qm]
这样写显然数据会重复
[/Quote]

相同的bh，單價不同的多條記錄時，取平均單價

Select 
    c.bh,
    c.[qm],
    max(a.jsr) AS jsr,
    a.type,
    a.status,
    sum(b.sl) AS sl,
    AVG(b.dj) AS dj,
    sum(b.je) as je
from (#A AS a 
    INNER JOIN #B AS b ON a.id=b.djbh)
    right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.type,a.status,c.bh,c.[qm]

/*
bh	qm	jsr	type	status	sl	dj	je
0000000001	花生	002	1	0	3	4	12
0000000002	牛奶	001	1	0	1	5	5
*/

[yyantifa]

mark

[nimeide1234567890]

B表
id djbh bh dj(单价) sl(数量) je(金额)
1 313 0000000001 5 1 5
2 313 0000000002 5 1 5
3 314 0000000001 4 1 4.5
4 315 0000000001 3 1 3

我把数据这样一换，就统计错误，0000000008的金额总和还是12，而不是12.5

[nimeide1234567890]

好象可以了，我再验证下

[nimeide1234567890]

B表
id djbh bh dj(单价) sl(数量) je(金额)
1 313 0000000001 5 1 5
2 313 0000000002 5 1 5
3 314 0000000001 4 1 4.5
4 315 0000000001 3 1 3

bh qm jsr type status sl dj je
0000000001 花生 002 1 0 3 4 12.5
0000000002 牛奶 001 1 0 1 5 5


我把数据这样一换，就统计错误，dj还是4

[中国风]

[Quote=引用 18 楼 luojianfeng 的回复:]

B表
id djbh bh dj(单价) sl(数量) je(金额)
1 313 0000000001 5 1 5
2 313 0000000002 5 1 5
3 314 0000000001 4 1 4.5
4 315 0000000001 3 1 3

bh qm jsr type status sl dj je
000000……
[/Quote]
B表的[dj]字段 還是4啊，不取這個字段

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[jsr] nvarchar(3),[type] int,[status] int)
Insert #A
select 313,N'001',1,0 union all
select 314,N'002',1,0 union all
select 315,N'001',1,0
Go

--> --> 
 
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([id] int,[djbh] int,[bh] nvarchar(10),[dj] int,[sl] int,[je] DECIMAL(18,2))
Insert #B
select 1,313,N'0000000001',5,1,5 union all
select 2,313,N'0000000002',5,1,5 union all
select 3,314,N'0000000001',4,1,4.5 union all
select 4,315,N'0000000001',3,1,3
Go

if not object_id(N'Tempdb..#C') is null
    drop table #C
Go
Create table #C([bh] nvarchar(10),[qm] nvarchar(2))
Insert #C
select N'0000000001',N'花生' union all
select N'0000000002',N'牛奶'
go
Select 
    c.bh,
    c.[qm],
    max(a.jsr) AS jsr,
    a.type,
    a.status,
    sum(b.sl) AS sl,
    CAST(sum(b.je)*1.0/sum(b.sl) AS DECIMAL(18,2)) AS dj,
    sum(b.je) as je
from (#A AS a 
    INNER JOIN #B AS b ON a.id=b.djbh)
    right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.type,a.status,c.bh,c.[qm]

/*
bh	qm	jsr	type	status	sl	dj	je
0000000001	花生	002	1	0	3	4.17	12.50
0000000002	牛奶	001	1	0	1	5.00	5.00
*/

單價=總金額/總數量時，這樣改，樓主看看是否正確

[nimeide1234567890]

貌似可以

[nimeide1234567890]

遇到被零除错误。.

[中国风]

[Quote=引用 22 楼 luojianfeng 的回复:]

遇到被零除错误。
这样试试
[/Quote]

数量有为0的记录?

Select 
    c.bh,
    c.[qm],
    max(a.jsr) AS jsr,
    a.type,
    a.status,
    sum(b.sl) AS sl,
    CAST(sum(b.je)*1.0/case when sum(b.sl)=0 then 1 else sum(b.sl) end AS DECIMAL(18,2)) AS dj,
    sum(b.je) as je
from (#A AS a 
    INNER JOIN #B AS b ON a.id=b.djbh)
    right JOIN #C AS c ON c.bh=b.bh
GROUP BY a.type,a.status,c.bh,c.[qm]