PartNo Vendor Date QTY
001 A 2005-5-1 200
001 A 2005-5-20 400
001 A 2005-6-10 100
001 A 2005-6-28 400
001 B 2005-5-1 200
001 B 2005-5-20 400
001 B 2005-6-10 100
001 B 2005-6-28 400
002 A 2005-5-8 500
002 A 2005-5-22 200
002 A 2005-6-15 300
002 A 2005-6-30 200
002 B 2005-5-10 100
002 B 2005-5-26 600
002 B 2005-6-16 100
002 B 2005-6-28 200如何按照不同PartNo以及不同的Vendor统计出每个月总的采购量?即如下结果:
PartNo Vendor 5月QTY 6月QTY
001 A 600 500
001 B 600 500
002 A 700 500
002 B 700 300
001 A 2005-5-1 200
001 A 2005-5-20 400
001 A 2005-6-10 100
001 A 2005-6-28 400
001 B 2005-5-1 200
001 B 2005-5-20 400
001 B 2005-6-10 100
001 B 2005-6-28 400
002 A 2005-5-8 500
002 A 2005-5-22 200
002 A 2005-6-15 300
002 A 2005-6-30 200
002 B 2005-5-10 100
002 B 2005-5-26 600
002 B 2005-6-16 100
002 B 2005-6-28 200如何按照不同PartNo以及不同的Vendor统计出每个月总的采购量?即如下结果:
PartNo Vendor 5月QTY 6月QTY
001 A 600 500
001 B 600 500
002 A 700 500
002 B 700 300
select PartNo,Vendor,sum(case convert(char(7),Date,120) when '2005-05' then QTY else 0 end) as 5月QTY,
sum(case convert(char(7),Date,120) when '2005-06' then QTY else 0 end) as 6月QTY
from tb
group by PartNo,Vendor
select PartNo,Vendor,sum(case convert(char(7),convert(datetime,Date),120) when '2005-05' then QTY else 0 end) as 5月QTY,
sum(case convert(char(7),convert(datetime,Date),120) when '2005-06' then QTY else 0 end) as 6月QTY
from tb
group by PartNo,Vendor
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-27 13:39:18
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([PartNo] varchar(3),[Vendor] varchar(1),[Date] datetime,[QTY] int)
insert [tb]
select '001','A','2005-5-1',200 union all
select '001','A','2005-5-20',400 union all
select '001','A','2005-6-10',100 union all
select '001','A','2005-6-28',400 union all
select '001','B','2005-5-1',200 union all
select '001','B','2005-5-20',400 union all
select '001','B','2005-6-10',100 union all
select '001','B','2005-6-28',400 union all
select '002','A','2005-5-8',500 union all
select '002','A','2005-5-22',200 union all
select '002','A','2005-6-15',300 union all
select '002','A','2005-6-30',200 union all
select '002','B','2005-5-10',100 union all
select '002','B','2005-5-26',600 union all
select '002','B','2005-6-16',100 union all
select '002','B','2005-6-28',200
--------------开始查询--------------------------
select PartNo,Vendor,
sum(case when datepart(mm,date)=5 and datepart(yy,date)=2005 then QTY else 0 end) as [5月QTY],
sum(case when datepart(mm,date)=6 and datepart(yy,date)=2005 then QTY else 0 end) as [5月QTY]
from
tb
group by
PartNo,Vendor
----------------结果----------------------------
/* PartNo Vendor 5月QTY 5月QTY
------ ------ ----------- -----------
001 A 600 500
002 A 700 500
001 B 600 500
002 B 700 300(4 行受影响)
*/