insert tbName(字段列表) select (字段列表) from tbName where estate='02'
这样的问题好象有人问过,楼主可以参考我写的2种方法:CREATE TABLE #T(xh nvarchar(20),kc nvarchar(40),cj int) INSERT INTO #T SELECT '051000333','高等数学', 55 UNION ALL SELECT '051000333','大学语文', 67 UNION ALL SELECT '051000333','经济学基础', 88 UNION ALL SELECT '021000224','高等数学', 64 UNION ALL SELECT '021000224','大学语文', 32 UNION ALL SELECT '021000224','经济学基础', 75 UNION ALL SELECT '041000851','高等数学', 69 UNION ALL SELECT '041000851','大学语文', 75 UNION ALL SELECT '041000851','经济学基础', 65 --方法1 SELECT xh ,MAX(CASE kc WHEN '高等数学' THEN cj ELSE 0 END) AS '高等数学' ,MAX(CASE kc WHEN '大学语文' THEN cj ELSE 0 END) AS '大学语文' ,MAX(CASE kc WHEN '经济学基础' THEN cj ELSE 0 END) AS '经济学基础' FROM #T GROUP BY xh --方法2 DECLARE @EXECUTE_SQL nvarchar(4000) SET @EXECUTE_SQL='SELECT xh' SELECT @EXECUTE_SQL=@EXECUTE_SQL+',MAX(CASE kc WHEN '''+kc+''' THEN cj ELSE 0 END) AS ['+kc+']' FROM #T GROUP BY kc SET @EXECUTE_SQL=@EXECUTE_SQL+' FROM #T GROUP BY xh ' EXECUTE(@EXECUTE_SQL) DROP TABLE #T /* xh 高等数学 大学语文 经济学基础 051000333 55 67 88 021000224 64 32 75 041000851 69 75 65 */自感觉第2种方法比较好,因为不用考虑多少个课程,自动根据各个课程个学号来统计. 原帖子: http://community.csdn.net/Expert/topic/5542/5542941.xml?temp=5.036563E-02
SELECT (字段列表) INTO #T FROM 表名 WHERE estate='02'#T是一个临时表.(记得后面使用完DROP TABLE #T一下)
select (字段列表)
from tbName where estate='02'
INSERT INTO #T
SELECT '051000333','高等数学', 55 UNION ALL
SELECT '051000333','大学语文', 67 UNION ALL
SELECT '051000333','经济学基础', 88 UNION ALL
SELECT '021000224','高等数学', 64 UNION ALL
SELECT '021000224','大学语文', 32 UNION ALL
SELECT '021000224','经济学基础', 75 UNION ALL
SELECT '041000851','高等数学', 69 UNION ALL
SELECT '041000851','大学语文', 75 UNION ALL
SELECT '041000851','经济学基础', 65
--方法1
SELECT xh
,MAX(CASE kc WHEN '高等数学' THEN cj ELSE 0 END) AS '高等数学'
,MAX(CASE kc WHEN '大学语文' THEN cj ELSE 0 END) AS '大学语文'
,MAX(CASE kc WHEN '经济学基础' THEN cj ELSE 0 END) AS '经济学基础'
FROM #T
GROUP BY xh
--方法2
DECLARE @EXECUTE_SQL nvarchar(4000)
SET @EXECUTE_SQL='SELECT xh'
SELECT @EXECUTE_SQL=@EXECUTE_SQL+',MAX(CASE kc WHEN '''+kc+''' THEN cj ELSE 0 END) AS ['+kc+']'
FROM #T
GROUP BY kc
SET @EXECUTE_SQL=@EXECUTE_SQL+' FROM #T GROUP BY xh '
EXECUTE(@EXECUTE_SQL)
DROP TABLE #T
/*
xh 高等数学 大学语文 经济学基础
051000333 55 67 88
021000224 64 32 75
041000851 69 75 65
*/自感觉第2种方法比较好,因为不用考虑多少个课程,自动根据各个课程个学号来统计.
原帖子:
http://community.csdn.net/Expert/topic/5542/5542941.xml?temp=5.036563E-02
FROM 表名 WHERE estate='02'#T是一个临时表.(记得后面使用完DROP TABLE #T一下)