摘要:依一欄位值做條件做群組group by
| 8 | CREATE TABLE TabPhone |
| 9 | ( |
| 10 | 日期時段 DATETIME, |
| 11 | 客戶 CHAR(3), |
| 12 | 註記 NVARCHAR(255) |
| 13 | ) |
| 14 | GO |
| 15 | |
| 16 | INSERT INTO TabPhone VALUES('2007/1/1 09:00', 'AAA', '重要客戶') |
| 17 | INSERT INTO TabPhone VALUES('2007/1/1 09:00', 'BBB', '老客戶') |
| 18 | INSERT INTO TabPhone VALUES('2007/1/1 10:00', 'CCC', '一般客戶') |
| 19 | INSERT INTO TabPhone VALUES('2007/1/1 11:00', 'DDD', '老客戶') |
| 20 | INSERT INTO TabPhone VALUES('2007/1/1 11:00', 'EEE', '老客戶') |
| 21 | GO |
| 22 | |
| 23 | SELECT 日期, 時段, COUNT(*) as 總來電數 |
| 24 | ,SUM(CASE 註記 WHEN '一般客戶' THEN 1 ELSE 0 END) as 一般客戶 |
| 25 | ,SUM(CASE 註記 WHEN '重要客戶' THEN 1 ELSE 0 END) as 重要客戶 |
| 26 | ,SUM(CASE 註記 WHEN '老客戶' THEN 1 ELSE 0 END) as 老客戶 |
| 27 | FROM |
| 28 | ( |
| 29 | SELECT * |
| 30 | // ,CONVERT(VARCHAR, 日期時段, 111) as 日期 |
| 31 |
// ,CONVERT(VARCHAR, 日期時段, 108) as 時段 case when 客戶='AA' then 1 else 0 end cus |
| 32 | FROM TabPhone |
| 33 | ) as VW |
| 34 | GROUP BY 日期, 時段 |
| 35 | GO |