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)
)
两个表的创建如上
显示如下
1 english 86.0
2 english 86.0
2 math 0.0
3 chinese 86.0
3 english 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
结果如下
1 NULL NULL 86.0
2 NULL 0.0 86.0
3 86.0 NULL 60.0
希望找到只在stuc表中执行查询，并且达到以上要求的sql语句，
[email protected]
谢谢！
------解决方案--------------------
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 '经济学基础 '