一个分类统计的sql问题。

[guanyelong]

表InterceptInfo

ID	SiteID	KeywordName	Icookie	IP	InterceptDateTime	InterceptTypeID
2129	14	testttttt	4956d2b444923e488333c56202f459	127.0.0.5	2015-04-17 14:41:25.297	1
2130	16	testttttt	4956d2b444923e488333c56202f444	127.0.0.1	2015-04-17 14:42:25.297	1
2131	14	testttttt	4956d2b444923e488333c56202f459	127.0.0.2	2015-04-20 14:43:25.297	2
2132	16	testttttts	4956d2b444923e488333c56202f459	127.0.0.1	2015-04-17 14:44:25.297	1
2133	16	testttttt	4956d2b444923e488333c56202f459	127.0.0.5	2015-04-16 14:45:25.297	4
2134	14	testtttttv	4956d2b444923e488333c56202f459	127.0.0.1	2015-04-21 14:46:25.297	1
2135	16	testttttt	4956d2b444923e488333c56202f459	127.0.0.3	2015-04-16 14:47:25.297	3
2136	18	testtttttl	4956d2b444923e488333c56202f459	127.0.0.1	2015-04-17 14:48:25.297	1
2137	16	testttttt	4956d2b444923e488333c56202f459	127.0.0.5	2015-04-16 14:49:25.297	3
2138	17	testtttttd	4956d2b444923e488333c56202f459	127.0.0.1	2015-04-17 14:50:25.297	3
2139	18	testttttt	4956d2b444923e488333c56202f422	127.0.0.1	2015-04-25 14:14:25.297	3
2140	16	testttttt	4956d2b444923e488333c56202f459	127.0.0.9	2015-04-20 14:24:25.297	1
2141	16	testtttttk	4956d2b444923e488333c56202f443	127.0.0.1	2015-04-11 14:34:25.297	1
2142	14	testttttt	4956d2b444923e488333c56202f498	127.0.0.6	2015-04-20 14:54:25.297	2
2143	14	testttttt	4956d2b444923e488333c56202f459	127.0.0.5	2015-04-17 11:41:25.297	1
2144	14	testttttt	4956d2b444923e488333c56202f459	127.0.0.5	2015-04-17 12:41:25.297	1
2145	14	搜索竞价	6cc67a8447aa22c60b52b733184d38	10.110.0.46	2015-08-05 10:23:25.523	1
2146	14	未识别字符	fff25c48180b2462484d67c08c8a3f	127.0.0.1	2015-08-25 16:35:27.770	3
2147	14	未识别字符	536c2f8a7dbf692b311fc274ed1f98	127.0.0.1	2015-08-25 16:41:23.157	2
2148	14	未识别字符	536c2f8a7dbf692b311fc274ed1f98	127.0.0.1	2015-08-25 17:06:24.197	1
2149	14	未识别字符	536c2f8a7dbf692b311fc274ed1f98	127.0.0.1	2015-08-25 17:09:59.657	3
2150	14	未识别字符	d3c39f61899a92ac06cb72ff96a2a6	127.0.0.1	2015-08-25 18:04:39.277	3
2151	14	未识别字符	d3c39f61899a92ac06cb72ff96a2a6	127.0.0.1	2015-08-25 18:06:14.977	3
2152	14	aaaaa 	6cc67a8447aa22c60b52b733184d38	10.110.0.46	2015-08-26 10:23:25.523	1

表InterceptType

TypeID	TypeName
1	善意提醒
2	发起攻击
3	给予警告
4	直接加入黑名单
5	直接添加到vvvv排除  
6	直接加入黑名单2

想达到效果（按时间分组并按类型统计其个数并按时间倒序）

INTERCEPTDATETIME  善意提醒  发起攻击  给予警告  直接加入黑名单 直接添加到vvvv排除   直接加入黑名单2
2015-08-26                        1			0		0			0				0						0
2015-08-25		             1			0              3			0				0						0
2015-08-05		             1			0		0			0				0						0

[Tiger_Zhao]

DECLARE @sql nvarchar(max)
DECLARE @columns nvarchar(max)
DECLARE @columnNames nvarchar(max)

SET @columns = ''
SET @columnNames = ''

    SELECT @columns = @columns+N',['+CONVERT(varchar(11),TypeID)+N']',
           @columnNames = @columnNames+N',ISNULL(['+CONVERT(varchar(11),TypeID)+N'],0) AS ['+TypeName+N']'
      FROM InterceptType
  ORDER BY TypeID

SET @columns = STUFF(@columns,1,1,'')
SET @columnNames = STUFF(@columnNames,1,1,'')

SET @sql = N'
    SELECT InterceptDateTime,
           '+@columnNames+N'
      FROM (
                SELECT InterceptTypeID,
                       CONVERT(varchar(10),InterceptDateTime,12) InterceptDateTime,
                       COUNT(*) cnt
                  FROM InterceptInfo
              GROUP BY InterceptTypeID,
                       CONVERT(varchar(10),InterceptDateTime,12)
           ) t
     PIVOT (MAX(cnt)
            FOR InterceptTypeID IN ('+@columns+N')
           ) p
  ORDER BY InterceptDateTime DESC'

PRINT @sql
EXEC sp_executesql @sql

动态语句

    SELECT InterceptDateTime,
           ISNULL([1],0) AS [善意提醒],ISNULL([2],0) AS [发起攻击],ISNULL([3],0) AS [给予警告],ISNULL([4],0) AS [直接加入黑名单],ISNULL([5],0) AS [直接添加到vvvv排除],ISNULL([6],0) AS [直接加入黑名单2]
      FROM (
                SELECT InterceptTypeID,
                       CONVERT(varchar(10),InterceptDateTime,12) InterceptDateTime,
                       COUNT(*) cnt
                  FROM InterceptInfo
              GROUP BY InterceptTypeID,
                       CONVERT(varchar(10),InterceptDateTime,12)
           ) t
     PIVOT (MAX(cnt)
            FOR InterceptTypeID IN ([1],[2],[3],[4],[5],[6])
           ) p
  ORDER BY InterceptDateTime DESC

结果

InterceptDateTime    善意提醒    发起攻击    给予警告  直接加入黑名单  直接添加到vvvv排除 直接加入黑名单2
----------------- ----------- ----------- ----------- --------------- ------------------- ---------------
2015-08-26                  1           0           0               0                   0               0
2015-08-25                  1           1           4               0                   0               0
2015-08-05                  1           0           0               0                   0               0
2015-04-25                  0           0           1               0                   0               0
2015-04-21                  1           0           0               0                   0               0
2015-04-20                  1           2           0               0                   0               0
2015-04-17                  6           0           1               0                   0               0
2015-04-16                  0           0           2               1                   0               0
2015-04-11                  1           0           0               0                   0               0

[guanyelong]

佩服的五体投地。

[江南雪_158]