請教SQL問題

[koenma80]

誠心請問
有一資料表[REQ]
日期 部門 類別
20090707 01 A
20090707 01 A
20090707 03 C
20090707 01 B
20090707 02 C
20090712 02 C
20090726 01 A
20090815 02 A
20090820 01 A

如何做出月的統計表如下

7月份
部門 A B C
01 3 1 0
02 0 0 2
03 1 0 0

我試了很久,只能寫出

SELECT 日期,部門,
sum(case when 類別='A' then 1 else 0 end) as A,
sum(case when 類別='B' then 1 else 0 end) as B,
sum(case when 類別='C' then 1 else 0 end) as C
FROM reqm
WHERE 日期>= '20090701' and 日期<= '20090731'
GROUP BY 日期,部門

結果為

日期 部門 A B C
20090707 01 2 1 0
20090707 02 1 0 2
20090707 03 0 0 1
20090712 02 0 0 1
20090726 01 1 0 0

不符合我的需求~
希望好心人幫我解答~謝謝

[老黎]

SELECT substring(日期,1,6),部門,
sum(case when 類別='A' then 1 else 0 end) as A,
sum(case when 類別='B' then 1 else 0 end) as B,
sum(case when 類別='C' then 1 else 0 end) as C
FROM reqm
WHERE 日期>= '20090701' and 日期<= '20090731' 
GROUP BY substring(日期,1,6),部門

[--小F--]

SELECT 部門,
sum(case when 類別='A' then 1 else 0 end) as A,
sum(case when 類別='B' then 1 else 0 end) as B,
sum(case when 類別='C' then 1 else 0 end) as C
FROM reqm
WHERE 日期>= '20090701' and 日期<= '20090731' 
GROUP BY 部門

[dawugui]

SELECT 部門,
sum(case when 類別='A' then 1 else 0 end) as A,
sum(case when 類別='B' then 1 else 0 end) as B,
sum(case when 類別='C' then 1 else 0 end) as C
FROM reqm
WHERE 日期>= '20090701' and 日期<= '20090731' 
GROUP BY 部門

[jstoic]

已经对了，只是你去的日志把分组分详细了。
日期只取到月就行了

[htl258_Tony]

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-25 17:31:29
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表:req

IF NOT OBJECT_ID('[req]') IS NULL
	DROP TABLE [req]
GO
CREATE TABLE [req]([日期] DATETIME,[部門] NVARCHAR(10),[類別] NVARCHAR(10))
INSERT [req]
SELECT '20090707','01','A' UNION ALL
SELECT '20090707','01','A' UNION ALL
SELECT '20090707','03','C' UNION ALL
SELECT '20090707','01','B' UNION ALL
SELECT '20090707','02','C' UNION ALL
SELECT '20090712','02','C' UNION ALL
SELECT '20090726','01','A' UNION ALL
SELECT '20090815','02','A' UNION ALL
SELECT '20090820','01','A'
GO
--SELECT * FROM [req]

-->SQL查询如下:
SELECT convert(varchar(7),日期,23) 日期,部門,
sum(case when 類別='A' then 1 else 0 end) as A,
sum(case when 類別='B' then 1 else 0 end) as B,
sum(case when 類別='C' then 1 else 0 end) as C
FROM req
WHERE 日期>= '20090701' and 日期<= '20090731' 
GROUP BY convert(varchar(7),日期,23),部門
/*
日期      部門         A           B           C
------- ---------- ----------- ----------- -----------
2009-07 01         3           1           0
2009-07 02         0           0           2
2009-07 03         0           0           1

(3 行受影响)
*/

这样可以吗

[koenma80]

感謝大家的回覆,最後是用 jstoic 和 htl258 的做法
因為必須保留 Select 日期 的部份,讓使用者可以自己輸入欲查詢的日期
抱欠~我一開始沒說清楚,也謝謝 kerafan , fredrickhu , dawugui 的回答

[koenma80]

補充一下,最後我用在Oracle上
SELECT convert(varchar(7),日期,23) 日期,部門,
要改成
SELECT to_char(to_date(reqm.dat_req,'yyyymmdd'),'yyyymm') 日期,