這是寫的sql陳述句。不管那個值換成10，20，30 那個 s.sid, s.uname里面的資料都是1和哈哈 這該怎么寫
求大佬幫我解答！
uj5u.com熱心網友回復：
where 后面 加一個 and s.sid=1~~~uj5u.com熱心網友回復：
create table #course
(
cid int,
cname char(10)
)
insert into #course
select 10,'語文' union all
select 20,'數學' union all
select 30,'英語'
create table #student
(
sid int,
uname char(10)
)
insert into #student
select 1,'哈哈' union all
select 2,'嘿嘿' union all
select 3,'呵呵'
create table #sc
(
sid int,
cid int,
score int
)
insert into #sc
select 1,10,88 union all
select 1,20,89 union all
select 1,30,90 union all
select 2,20,100 union all
select 2,30,89 union all
select 2,10,99 union all
select 3,20,100 union all
select 3,30,100 union all
select 3,10,67
select s.*,d.cname,a.score,d.avg_score,d.max_score from #student s left join #sc a on s.sid=a.sid
left join
(
select a.cid,c.cname,avg(a.score) as avg_score ,max(a.score) as max_score from #sc a left join #course c on a.cid=c.cid
group by a.cid,c.cname
) d on a.cid=d.cid
where s.sid=1
/*
sid uname cname score avg_score max_score
----------- ---------- ---------- ----------- ----------- -----------
1 哈哈 語文 88 84 99
1 哈哈 數學 89 96 100
1 哈哈 英語 90 93 100
(3 行受影響)
*/
uj5u.com熱心網友回復：
select sid=1,uname='哈哈' from student
uj5u.com熱心網友回復：
if object_id('tempdb..#course') is not null drop table #course
create table #course
(cid int,cname nvarchar(50))
insert into #course
select 10,'語文' union all
select 20,'數學' union all
select 30,'英語'
if object_id('tempdb..#student') is not null drop table #student
create table #student
(sid int,uname nvarchar(50))
insert into #student
select 1,'哈哈' union all
select 2,'嘿嘿' union all
select 3,'呵呵'
if object_id('tempdb..#sc') is not null drop table #sc
create table #sc
(sid int,cid int,score int)
insert into #sc
select 1,10,88 union all
select 1,20,89 union all
select 1,30,90 union all
select 2,10,99 union all
select 2,20,100 union all
select 2,30,89 union all
select 3,10,67 union all
select 3,20,100 union all
select 3,30,100
--查詢各科目,最高分,平均分,成績最好的學生
select t.*,
stuff((select distinct ','+b.uname from #sc a inner join #student b on a.sid=b.sid where t.最高分=a.score and t.cid=a.cid for xml path('')),1,1,'') as 學生名
from (
select c.cid,c.cname,max(score) as 最高分,avg(score) as 平均分
from #sc s
left join #course c on s.cid=c.cid
group by c.cid,c.cname
)t
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34488.html
標籤：疑難問題
上一篇：postgresql求助