求SQL语句
0
求sql查询语句，大神们请多指教。。。。。。。。。。。
所有回答(11)
0
select * from table where score=100 and scale =40 and subject ="数学"
0
select name
,max(case subject when '数学' then score else 0 end) as 数学
,max(case subject when '数学' then scale else 0 end) as 数学scale
,max(case subject when '语文' then score else 0 end) as 语文
,max(case subject when '语文' then scale else 0 end) as 语文scale
,max(case subject when '英语' then score else 0 end) as 数学
,max(case subject when '英语' then scale else 0 end) as 英语scale
,sum(score*scale/100) as 总分
from table
group by name
1
pivot
0
select a.*,cast((数学*数学scale+语文*数学scale+英语*英语scale) as float)/100 as 总分
from (
select name,
max(case subject when '数学' then score else 0 end) as 数学,
max(case subject when '数学' then scale else 0 end) as 数学scale,
max(case subject when '语文' then score else 0 end) as 语文,
max(case subject when '语文' then scale else 0 end) as 语文scale,
max(case subject when '英语' then score else 0 end) as 英语,
max(case subject when '英语' then scale else 0 end) as 英语scale
from tablename
group by name
) as a
麻烦问下，为啥要用max
@恋人星空:
拿第一个case when来说：当subject='数学'时，值为score字段中的数据，否则为0
列名： Name 数学
数据： 张三 100
张三 0
张三 0
这个时候就需要group by name统计数据，select筛选的字段除group by后面字段之外，必须是聚合函数计算得到的结果，max()得到的值才是张三数学科目实际对应的分数，其实用sum()函数也可以。
其他科目类似。
其实主要要考虑的是行转列。
@会飞的金鱼: 讲的很好，十分感谢，我明白了
0
可以
0
are you meizi?
0
行转列
0
select "Name", sum(case "Subject" when '数学' then "Score" else 0 end) 数学, sum(case "Subject" when '数学' then "Scale" else 0 end) "数学 scale", sum(case "Subject" when '语文' then "Score" else 0 end) 语文, sum(case "Subject" when '语文' then "Scale" else 0 end) "语文 scale", sum(case "Subject" when '英语' then "Score" else 0 end) 英语, sum(case "Subject" when '英语' then "Scale" else 0 end) "英语 scale", sum("Score"*"Scale"/100) 总分 from "成绩" group by "Name"
有图，有真相，楼主好好看吧。
非常感谢你的回答，学习了
0
1 SELECT * FROM [TABLE] PIVOT ( MAX(SCORE), MAX(SCALE) SCALE FOR SUBJECT IN ('数学', '语文', '英语') )
1 SELECT * FROM [TABLE] PIVOT ( SUM(SCORE), SUM(SCALE) SCALE FOR SUBJECT IN ('数学', '语文', '英语') )
总分自己计算一下,
