这样的结果?CREATE TABLE #table(UserName VARCHAR(10),Score NUMERIC(4,1),DateMonth VARCHAR(7)) INSERT INTO #table SELECT '张三',78.5,'2011-01' UNION ALL SELECT '张三',88.5,'2011-02' UNION ALL SELECT '张三',68.5,'2011-02' UNION ALL SELECT '张三',78.5,'2011-03' UNION ALL SELECT '张三',78.5,'2011-04' UNION ALL SELECT '李四',40.0,'2011-01' UNION ALL SELECT '李四',60.0,'2011-02' UNION ALL SELECT '李四',78.5,'2011-03' DECLARE @SQL nvarchar(MAX)SET @SQL = ' SELECT T.UserName ,SUM(T.Score) AS TotalScore ' SELECT @SQL = @SQL + ',SUM(CASE WHEN T.DateMonth = '''+T.DateMonth+''' THEN T.Score END) AS ['+T.DateMonth+']' FROM #table T GROUP BY T.DateMonth SET @SQL = @SQL + ' FROM #table T GROUP BY T.UserName ORDER BY SUM(T.Score) DESC 'EXEC SP_EXECUTESQL @SQLDROP TABLE #table/* UserName TotalScore 2011-01 2011-02 2011-03 2011-04 ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- 张三 392.5 78.5 157.0 78.5 78.5 李四 178.5 */
这个不容易吧要是总排名后面 带1个每月情况比较简单
这样的结果?CREATE TABLE #table(UserName VARCHAR(10),Score NUMERIC(4,1),DateMonth VARCHAR(7))
INSERT INTO #table
SELECT '张三',78.5,'2011-01' UNION ALL
SELECT '张三',88.5,'2011-02' UNION ALL
SELECT '张三',68.5,'2011-02' UNION ALL
SELECT '张三',78.5,'2011-03' UNION ALL
SELECT '张三',78.5,'2011-04' UNION ALL
SELECT '李四',40.0,'2011-01' UNION ALL
SELECT '李四',60.0,'2011-02' UNION ALL
SELECT '李四',78.5,'2011-03' DECLARE @SQL nvarchar(MAX)SET @SQL = ' SELECT T.UserName ,SUM(T.Score) AS TotalScore '
SELECT @SQL = @SQL + ',SUM(CASE WHEN T.DateMonth = '''+T.DateMonth+''' THEN T.Score END) AS ['+T.DateMonth+']'
FROM #table T
GROUP BY T.DateMonth
SET @SQL = @SQL + ' FROM #table T GROUP BY T.UserName ORDER BY SUM(T.Score) DESC 'EXEC SP_EXECUTESQL @SQLDROP TABLE #table/*
UserName TotalScore 2011-01 2011-02 2011-03 2011-04
---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
张三 392.5 78.5 157.0 78.5 78.5
李四 178.5 */
结果
/*(8 行受影响)
UserName TotalScore 2011-01 2011-02 2011-03 2011-04
---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
张三 392.5 78.5 157.0 78.5 78.5
李四 178.5 40.0 60.0 78.5 NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(2 行受影响)
*/