这样的结果怎么得到？

[nimeide1234567890]

A表字段，id是自增,djbh(订单编号)也是唯一
id djbh
313 dj_001
314 dj_002
315 dj_003

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,djbh，B表里的djbh,bh,dj,sl,C表里的bh,qm,并且B表里的sl,je要按bh（编号）汇总，比较难，分不够可以加

[nimeide1234567890]

数据库是SQL 2000

[nimeide1234567890]

A表字段，id是自增,djbh(订单编号)也是唯一
id djbh rq
313 dj_001 2011-10-19
314 dj_002 2011-10-19
315 dj_003 2011-10-19

能否再从A表里多显示个单据日期字段？

Select
CASE WHEN c.[qm] IS NULL OR a.bh IS NULL THEN N'' ELSE c.qm end AS qm,
CASE WHEN c.[qm] IS NULL THEN N'合計' WHEN a.bh IS NULL THEN N'小計' ELSE a.bh END AS [djbh],
SUM(b.sl2) AS sl,
SUM(b.[je2]) 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 c.[qm],a.bh,a.ID WITH rollup
HAVING (GROUPING(a.ID)=0 OR GROUPING(c.qm)=1 OR GROUPING(a.bh)=1)

[中国风]

其它顯示字段用min(字段) as 字段

Select 
  CASE WHEN c.[qm] IS NULL OR a.bh IS NULL THEN N'' ELSE c.qm end AS qm,
  CASE WHEN c.[qm] IS NULL THEN N'合計' WHEN a.bh IS NULL THEN N'小計' ELSE a.bh END AS [djbh],
min(rq) as rq,
  SUM(b.sl2) AS sl,
  SUM(b.[je2]) 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 c.[qm],a.bh,a.ID WITH rollup
HAVING (GROUPING(a.ID)=0 OR GROUPING(c.qm)=1 OR GROUPING(a.bh)=1)

[nimeide1234567890]

小计下面怎么留一空行？

[中国风]

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

小计下面怎么留一空行？
[/Quote]
只能用union all拼結果集

select * from ...
union all
selet * from ...--小計
union all
select '',''--空行
union all
select * from ...--合計

針對顯示加其它列作為排序

[--小F--]

不要发两遍啊。。你这个用报表来解决比较好

[叶子]

貌似是三表连查，然后用rollup进行小计、合计即可。

[叶子]

自定义表头在程序中处理。

[nimeide1234567890]

[Quote=引用 1 楼 fredrickhu 的回复:]
不要发两遍啊。。你这个用报表来解决比较好
[/Quote]

和上一个不一样，我改了一点，希望好实现

[中国风]

還有其它欄位要顯示時加上 min(欄位) as 欄位

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] int,[djbh] nvarchar(6))
Insert #A
select 313,N'dj_001' union all
select 314,N'dj_002' union all
select 315,N'dj_003'
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
--现在要得到A表里的id,djbh，B表里的djbh,bh,dj,sl,C表里的bh,qm,并且B表里的sl,je要按bh（编号）汇总，比较难，分不够可以加
Select 
	CASE WHEN c.[qm] IS NULL  OR a.[djbh] IS NULL THEN N'' ELSE c.qm end AS qm,
	CASE WHEN c.[qm] IS NULL  THEN N'合計' WHEN a.[djbh] IS NULL THEN N'小計' ELSE  a.[djbh] END AS [djbh],
    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 c.[qm],a.[djbh],b.ID WITH rollup
HAVING (GROUPING(b.ID)=0 OR GROUPING(c.qm)=1 OR GROUPING(a.[djbh])=1)

/*
qm	djbh	sl	je
牛奶	dj_001	1	5
	小計	1	5
花生	dj_001	1	5
花生	dj_002	1	4
花生	dj_003	1	3
	小計	3	12        
	合計	4	17
	*/

[--小F--]

GROUPING函数和GROUP BY WITH ROLLUP的用法。