# 求SQL语句

0
[待解决问题]

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

@恋人星空:

张三              0

张三              0

@会飞的金鱼: 讲的很好，十分感谢，我明白了

0

0

are you meizi?

+小马哥++ | 园豆：906 (小虾三级) | 2014-11-01 12:12
0

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 ('数学', '语文', '英语') )`

0

您需要登录以后才能回答，未注册用户请先注册