问一个老问题 SQL行转列
create table stuc(
stuc_sno int not null,
stuc_course char(10) not null,
stuc_credit float ,
constraint pkstuc primary key ( stuc_sno,stuc_course )
)
create table stu(
stu_sno int not null,
stu_sname char(10) not null,
constraint pkstu primary key (stu_sno)
)
两个表的创建如上
显示如下
1english 86.0
2english 86.0
2math 0.0
3chinese 86.0
3english 60.0
现在我用的是如下代码
select distinct stu_sno as 姓名,
(select stuc_credit from stuc where stuc_sno=stu_sno and stuc_course = 'chinese ' )as 语文,
(select stuc_credit from stuc where stuc_sno=stu_sno and stuc_course = 'math ' )as 数学,
(select stuc_credit from stuc where stuc_sno=stu_sno and stuc_course = 'english ' )as 英语
from stuc,stu
结果如下
1NULLNULL86.0
2NULL0.086.0
386.0NULL60.0
希望找到只在stuc表中执行查询，并且达到以上要求的sql语句，
但有一个要求不使用@之类的参数方法。
谢谢！
[解决办法]
create table stuc(
stuc_sno int not null,
stuc_course char(10) not null,
stuc_credit float ,
constraint pkstuc primary key ( stuc_sno,stuc_course )
)
create table stu(
stu_sno int not null,
stu_sname char(10) not null,
constraint pkstu primary key (stu_sno)
)
insert stuc select 1, 'english ', 86.0
union all select 2, 'english ', 86.0
union all select 2, 'math ', 0.0
union all select 3, 'chinese ', 86.0
union all select 3, 'english ', 60.0
insert stu select 1, 'AA '
insert stu select 2, 'BB '
insert stu select 3, 'CC '
select stuc.stuc_sno as 学号,stu.stu_sname as 姓名,
max(case when stuc_course = 'chinese ' then stuc_credit end) as 语文,
max(case when stuc_course = 'math ' then stuc_credit end) as 数学,
max(case when stuc_course = 'english ' then stuc_credit end) as 英语
from stuc
left join stu on stuc.stuc_sno=stu.stu_sno
group by stuc.stuc_sno,stu.stu_sname
[解决办法]
这样的问题好象有人问过,楼主可以参考我写的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
