求一分组统计SQL

Question

4个表发货单表头
发货单号  物料代码  数量  单价    金额  
001          wl    100     2.00      200  
002          kc     20    3.00     60
003          tk     10     2.00    20
004           tk     10     2.00   20
005           tk     30      2.00   60发货单表体   
发货单号  物料代码   日期        要货单位   是否审核（1为审核0为未审核）
001        wl    2009-01-01      dw001        1
002         kc      2010-01-01   dw002        1
003         tk      2009-02-01    dw003       1
004         tk      2009-02-01    dw003       1
005         tk      2009-02-01    dw004       1物料表
物料代码  物料名称
wl       小汽车
kc        卡车
tk         坦克
单位表
单位代码   单位名称
dw001       西数
dw002       神州
dw003        欧米卡
dw004        东方要求查询结果如下：
物料代码 物料名称  总数量 总金额      要货单位        时间范围
  wl      小汽车    100      200        西数         2009-01-01-2010-01-01
  tk       坦克     20      40           欧米卡      2009-01-01-2010-01-01
  tk        坦克    30      60           东方        2009-01-01-2010-01-01
  kc       卡车     20      60           西数        2009-01-01-2010-01-01

Answer 1

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-07 15:56:33
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:发货单表头IF NOT OBJECT_ID('[发货单表头]') IS NULL
DROP TABLE [发货单表头]
GO
CREATE TABLE [发货单表头]([发货单号] NVARCHAR(10),[物料代码] NVARCHAR(10),[数量] INT,[单价] DECIMAL(18,2),[金额] INT)
INSERT [发货单表头]
SELECT '001','wl',100,2.00,200 UNION ALL
SELECT '002','kc',20,3.00,60 UNION ALL
SELECT '003','tk',10,2.00,20 UNION ALL
SELECT '004','tk',10,2.00,20 UNION ALL
SELECT '005','tk',30,2.00,60
GO
--SELECT * FROM [发货单表头]--> 生成测试数据表:发货单表体IF NOT OBJECT_ID('[发货单表体]') IS NULL
DROP TABLE [发货单表体]
GO
CREATE TABLE [发货单表体]([发货单号] NVARCHAR(10),[物料代码] NVARCHAR(10),[日期] DATETIME,[要货单位] NVARCHAR(10),[是否审核] INT)
INSERT [发货单表体]
SELECT '001','wl',N'2009-01-01','dw001',1 UNION ALL
SELECT '002','kc',N'2010-01-01','dw002',1 UNION ALL
SELECT '003','tk',N'2009-02-01','dw003',1 UNION ALL
SELECT '004','tk',N'2009-02-01','dw003',1 UNION ALL
SELECT '005','tk',N'2009-02-01','dw004',1
GO
--SELECT * FROM [发货单表体]--> 生成测试数据表:物料表IF NOT OBJECT_ID('[物料表]') IS NULL
DROP TABLE [物料表]
GO
CREATE TABLE [物料表]([物料代码] NVARCHAR(10),[物料名称] NVARCHAR(10))
INSERT [物料表]
SELECT 'wl',N'小汽车' UNION ALL
SELECT 'kc',N'卡车' UNION ALL
SELECT 'tk',N'坦克'
GO
--SELECT * FROM [物料表]--> 生成测试数据表:单位表IF NOT OBJECT_ID('[单位表]') IS NULL
DROP TABLE [单位表]
GO
CREATE TABLE [单位表]([单位代码] NVARCHAR(10),[单位名称] NVARCHAR(10))
INSERT [单位表]
SELECT 'dw001',N'西数' UNION ALL
SELECT 'dw002',N'神州' UNION ALL
SELECT 'dw003',N'欧米卡' UNION ALL
SELECT 'dw004',N'东方'
GO
--SELECT * FROM [单位表]-->SQL查询如下:select a.物料代码,a.物料名称,b.总数量,b.总金额,c.单位名称 要货单位,b.日期 时间范围
from 物料表 a
join (
select a.[物料代码],b.要货单位,b.日期,sum(a.数量) 总数量,sum(a.金额) 总金额
from [发货单表头] a
join [发货单表体] b
on a.发货单号=b.发货单号 and a.物料代码=b.物料代码 and b.是否审核=1
group by a.[物料代码],b.要货单位,b.日期
) b
   on a.物料代码=b.物料代码
join 单位表 c
on b.要货单位=c.单位代码
/*
物料代码       物料名称       总数量         总金额         要货单位       时间范围
---------- ---------- ----------- ----------- ---------- -----------------------
wl         小汽车        100         200         西数         2009-01-01 00:00:00.000
kc         卡车         20          60          神州         2010-01-01 00:00:00.000
tk         坦克         20          40          欧米卡        2009-02-01 00:00:00.000
tk         坦克         30          60          东方         2009-02-01 00:00:00.000(4 行受影响)
*/

Answer 2

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-07 15:56:33
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:发货单表头IF NOT OBJECT_ID('[发货单表头]') IS NULL
DROP TABLE [发货单表头]
GO
CREATE TABLE [发货单表头]([发货单号] NVARCHAR(10),[物料代码] NVARCHAR(10),[数量] INT,[单价] DECIMAL(18,2),[金额] INT)
INSERT [发货单表头]
SELECT '001','wl',100,2.00,200 UNION ALL
SELECT '002','kc',20,3.00,60 UNION ALL
SELECT '003','tk',10,2.00,20 UNION ALL
SELECT '004','tk',10,2.00,20 UNION ALL
SELECT '005','tk',30,2.00,60
GO
--SELECT * FROM [发货单表头]--> 生成测试数据表:发货单表体IF NOT OBJECT_ID('[发货单表体]') IS NULL
DROP TABLE [发货单表体]
GO
CREATE TABLE [发货单表体]([发货单号] NVARCHAR(10),[物料代码] NVARCHAR(10),[日期] DATETIME,[要货单位] NVARCHAR(10),[是否审核] INT)
INSERT [发货单表体]
SELECT '001','wl',N'2009-01-01','dw001',1 UNION ALL
SELECT '002','kc',N'2010-01-01','dw002',1 UNION ALL
SELECT '003','tk',N'2009-02-01','dw003',1 UNION ALL
SELECT '004','tk',N'2009-02-01','dw003',1 UNION ALL
SELECT '005','tk',N'2009-02-01','dw004',1
GO
--SELECT * FROM [发货单表体]--> 生成测试数据表:物料表IF NOT OBJECT_ID('[物料表]') IS NULL
DROP TABLE [物料表]
GO
CREATE TABLE [物料表]([物料代码] NVARCHAR(10),[物料名称] NVARCHAR(10))
INSERT [物料表]
SELECT 'wl',N'小汽车' UNION ALL
SELECT 'kc',N'卡车' UNION ALL
SELECT 'tk',N'坦克'
GO
--SELECT * FROM [物料表]--> 生成测试数据表:单位表IF NOT OBJECT_ID('[单位表]') IS NULL
DROP TABLE [单位表]
GO
CREATE TABLE [单位表]([单位代码] NVARCHAR(10),[单位名称] NVARCHAR(10))
INSERT [单位表]
SELECT 'dw001',N'西数' UNION ALL
SELECT 'dw002',N'神州' UNION ALL
SELECT 'dw003',N'欧米卡' UNION ALL
SELECT 'dw004',N'东方'
GO
--SELECT * FROM [单位表]-->SQL查询如下:select a.物料代码,a.物料名称,b.总数量,b.总金额,c.单位名称 要货单位,ltrim(year(b.日期))+'-01-01~'+LTRIM(year(b.日期)+1)+'-01-01' 时间范围
from 物料表 a
join (
select a.[物料代码],b.要货单位,b.日期,sum(a.数量) 总数量,sum(a.金额) 总金额
from [发货单表头] a
join [发货单表体] b
on a.发货单号=b.发货单号 and a.物料代码=b.物料代码 and b.是否审核=1
group by a.[物料代码],b.要货单位,b.日期
) b
   on a.物料代码=b.物料代码
join 单位表 c
on b.要货单位=c.单位代码
/*
物料代码       物料名称       总数量         总金额         要货单位       时间范围
---------- ---------- ----------- ----------- ---------- -------------------------------------
wl         小汽车        100         200         西数         2009-01-01~2010-01-01
kc         卡车         20          60          神州         2010-01-01~2011-01-01
tk         坦克         20          40          欧米卡        2009-01-01~2010-01-01
tk         坦克         30          60          东方         2009-01-01~2010-01-01(4 行受影响)
*/时间范围修改

Answer 3

if object_id('[发货单表头]') is not null drop table [发货单表头]
go
create table [发货单表头]([发货单号] varchar(3),[物料代码] varchar(2),[数量] int,[单价] numeric(3,2),[金额] int)
insert [发货单表头]
select '001','wl',100,2.00,200 union all
select '002','kc',20,3.00,60 union all
select '003','tk',10,2.00,20 union all
select '004','tk',10,2.00,20 union all
select '005','tk',30,2.00,60
if object_id('[发货单表体]') is not null drop table [发货单表体]
go
create table [发货单表体]([发货单号] varchar(3),[物料代码] varchar(2),[日期] datetime,[要货单位] varchar(5),[是否审核(1为审核0为未审核)] int)
insert [发货单表体]
select '001','wl','2009-01-01','dw001',1 union all
select '002','kc','2010-01-01','dw002',1 union all
select '003','tk','2009-02-01','dw003',1 union all
select '004','tk','2009-02-01','dw003',1 union all
select '005','tk','2009-02-01','dw004',1
if object_id('[物料表]') is not null drop table [物料表]
go
create table [物料表]([物料代码] varchar(2),[物料名称] varchar(6))
insert [物料表]
select 'wl','小汽车' union all
select 'kc','卡车' union all
select 'tk','坦克'
if object_id('[单位表]') is not null drop table [单位表]
go
create table [单位表]([单位代码] varchar(5),[单位名称] varchar(6))
insert [单位表]
select 'dw001','西数' union all
select 'dw002','神州' union all
select 'dw003','欧米卡' union all
select 'dw004','东方'
 
---查询
select 
a.物料代码,
a.物料名称,
sum(a.数量) as 总数量,
sum(金额) as 总金额,
b.单位名称 as 要货单位,
min(convert(varchar(10),b.日期,120))+'-'+max(convert(varchar(10),b.日期,120)) as 时间范围
from
(
 select a.*,b.物料名称 from 发货单表头 a join 物料表 b on a.物料代码=b.物料代码
) a
left join
(
 select a.*,b.单位名称 from 发货单表体 a join 单位表 b on a.要货单位=b.单位代码
) b
on a.发货单号=b.发货单号
group by a.物料代码,a.物料名称,b.单位名称/**
物料代码 物料名称   总数量         总金额         要货单位   时间范围
---- ------ ----------- ----------- ------ ---------------------
kc   卡车     20          60          神州     2010-01-01-2010-01-01
tk   坦克     30          60          东方     2009-02-01-2009-02-01
tk   坦克     20          40          欧米卡    2009-02-01-2009-02-01
wl   小汽车    100         200         西数     2009-01-01-2009-01-01(4 行受影响)
**/ 
跟F姐姐没搭配上 

Answer 4

感谢各位高人！现在假如发货单表头和发货单表体的属性与记录改成如下，其它两表结构不变。按一个自己选定的时间段查询。要求查询结果如下：
1:选定的一个时间段内（如2009-01-01至2010-12-31）的发给某单位的某个物料的总数量与总金额。
 物料代码 物料名称 发货总数量 发货总金额 要货单位              2:选定的一个时间段内某物料的发货总数与总金额。
物料代码 物料名称 发货总数量 发货总金额  
---------
发货单表头  
发货单号           日期        要货单位   是否审核（1为审核0为未审核）要求过滤为0的记录
001               2009-01-01      dw001        1
002               2010-01-01      dw002        1
003               2009-02-01      dw003        1
004               2009-02-01      dw003        1--------
发货单表体
发货单号          物料代码   数量    单价     金额  
001                wl       100      2.00      200  
001                kc       20       3.00      60
001                tk       10       2.00      20
002                kc        6       2.00      12
002                wl       5        2.00       10
003                tk       10       2.00      20
004                tk       30       2.00      60
---------
物料表
物料代码   物料名称
wl          小汽车
kc          卡车
tk          坦克
---------
单位表
单位代码    单位名称
dw001        西数
dw002        神州
dw003        欧米卡
dw004        东方

Answer 5

感谢各位高人！现在假如发货单表头和发货单表体的属性与记录改成如下，其它两表结构不变。按一个自己选定的时间段查询。要求查询结果如下：
1:选定的一个时间段内（如2009-01-01至2010-12-31）的发给某单位的某个物料的总数量与总金额。
 物料代码 物料名称 发货总数量 发货总金额 要货单位              2:选定的一个时间段内某物料的发货总数与总金额。
物料代码 物料名称 发货总数量 发货总金额  
---------
发货单表头  
发货单号           日期        要货单位   是否审核（1为审核0为未审核）要求过滤为0的记录
001               2009-01-01      dw001        1
002               2010-01-01      dw002        1
003               2009-02-01      dw003        1
004               2009-02-01      dw003        1--------
发货单表体
发货单号          物料代码   数量    单价     金额  
001                wl       100      2.00      200  
001                kc       20       3.00      60
001                tk       10       2.00      20
002                kc        6       2.00      12
002                wl       5        2.00       10
003                tk       10       2.00      20
004                tk       30       2.00      60
---------
物料表
物料代码   物料名称
wl          小汽车
kc          卡车
tk          坦克
---------
单位表
单位代码    单位名称
dw001        西数
dw002        神州
dw003        欧米卡
dw004        东方

Answer 6

这种很简单的。无语了你再多看一下SQL多学习一下比较好，看楼主也是搞ERP的

Answer 7

--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101 At:2010-04-08 09:59:44=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #发货单表头
if object_id('tempdb.dbo.#发货单表头') is not null drop table #发货单表头
create table #发货单表头 (发货单号 varchar(3),物料代码 varchar(2),数量 int,单价 numeric(3,2),金额 int)
insert into #发货单表头
select '001','wl',100,2.00,200 union all
select '002','kc',20,3.00,60 union all
select '003','tk',10,2.00,20 union all
select '004','tk',10,2.00,20 union all
select '005','tk',30,2.00,60--> 测试数据: #发货单表体
if object_id('tempdb.dbo.#发货单表体') is not null drop table #发货单表体
create table #发货单表体 (发货单号 varchar(3),物料代码 varchar(2),日期 datetime,要货单位 varchar(5),是否审核 int)
insert into #发货单表体
select '001','wl','2009-01-01','dw001',1 union all
select '002','kc','2010-01-01','dw002',1 union all
select '003','tk','2009-02-01','dw003',1 union all
select '004','tk','2009-02-01','dw003',1 union all
select '005','tk','2009-02-01','dw004',1--> 测试数据: #物料表
if object_id('tempdb.dbo.#物料表') is not null drop table #物料表
create table #物料表 (物料代码 varchar(2),物料名称 varchar(6))
insert into #物料表
select 'wl','小汽车' union all
select 'kc','卡车' union all
select 'tk','坦克'--> 测试数据: #单位表
if object_id('tempdb.dbo.#单位表') is not null drop table #单位表
create table #单位表 (单位代码 varchar(5),单位名称 varchar(6))
insert into #单位表
select 'dw001','西数' union all
select 'dw002','神州' union all
select 'dw003','欧米卡' union all
select 'dw004','东方'select * from #发货单表头
select * from #发货单表体
select * from #物料表
select * from #单位表
----------------查询------------
;with cte as(
select a.*,b.日期,b.要货单位,b.是否审核 from #发货单表头 a inner join #发货单表体 b on
a.发货单号=b.发货单号
)
select b.物料代码,max(a.物料名称) 物料名称,sum(b.数量) 数量,sum(b.单价) 单价,max(c.单位名称) 单位名称,
convert(varchar(10),min(b.日期),120)+'~'+convert(varchar(12),max(b.日期),120) 时间范围
from #物料表 a 
inner join cte b on a.物料代码=b.物料代码
inner join #单位表 c on b.要货单位=c.单位代码
group by b.物料代码,b.要货单位----------------结果--------------
/*
物料代码 物料名称 数量 单价 单位名称 时间范围
kc 卡车 20 3.00 神州 2010-01-01~2010-01-01 0
tk 坦克 20 4.00 欧米卡 2009-02-01~2009-02-01 0
tk 坦克 30 2.00 东方 2009-02-01~2009-02-01 0
wl 小汽车 100 2.00 西数 2009-01-01~2009-01-01 0
*/PS，新的问题请另开一贴吧~~