誠心請問
有一資料表[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不符合我的需求~
希望好心人幫我解答~謝謝
有一資料表[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),部門
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 部門
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 部門
-- 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)
--------------------------------------------------------------------------
--> 生成测试数据表:reqIF 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 行受影响)
*/这样可以吗
因為必須保留 Select 日期 的部份,讓使用者可以自己輸入欲查詢的日期
抱欠~我一開始沒說清楚,也謝謝 kerafan , fredrickhu , dawugui 的回答
SELECT convert(varchar(7),日期,23) 日期,部門,
要改成
SELECT to_char(to_date(reqm.dat_req,'yyyymmdd'),'yyyymm') 日期,