列をソートしたい

質問

下のようなテーブルがあります。

名前|国語|算数|理科|社会
------------------------
太郎| 85 | 75 | 65 | 95
花子| 62 | 72 | 82 | 92

ここから、

太郎| 95 | 85 | 75 | 65
花子| 92 | 82 | 72 | 62

というように、列データをソートして取り出したいのですが、１つのクエリで可能でしょうか？

回答No.1

masayuki0511 · ベストアンサー

次のようなクエリーでできます。
nが名前、k,s,r,sh は各教科の得点です。

1つのクエリーに拘らなければ、ビューを使えば、t1～t4の記述が楽になります。

少数を足して、floorを取っているのは、同得点の時のための処理です。

select t0.n, floor(t1.p),floor(t2.p),floor(t3.p),floor(t4.p)
from aaa as t0,
(select n,k+0.3 as p from aaa
union
select n,s+0.2 as p from aaa
union
select n,r+0.1 as p from aaa
union
select n,sh as p from aaa) as t1,
(select n,k+0.3 as p from aaa
union
select n,s+0.2 as p from aaa
union
select n,r+0.1 as p from aaa
union
select n,sh as p from aaa) as t2,
(select n,k+0.3 as p from aaa
union
select n,s+0.2 as p from aaa
union
select n,r+0.1 as p from aaa
union
select n,sh as p from aaa) as t3,
(select n,k+0.3 as p from aaa
union
select n,s+0.2 as p from aaa
union
select n,r+0.1 as p from aaa
union
select n,sh as p from aaa) as t4
where t0.n = t1.n
and t0.n = t2.n
and t0.n = t3.n
and t0.n = t4.n
and t1.p > t2.p
and t2.p > t3.p
and t3.p > t4.p

MySQLでしか試してませんが。

お礼

回答ありがとうございました。お礼が遅れまして大変申し訳ありませんでした。

回答No.2

masayuki0511

おまけですが、次のようにすると、どの科目かも出せます。

select t0.n,t1.kamoku,floor(t1.p),
t2.kamoku,floor(t2.p),
t3.kamoku,floor(t3.p),
t4.kamoku,floor(t4.p)
from aaa as t0,
(select n,k+0.3 as p,'国語' as kamoku from aaa
union
select n,s+0.2 as p,'算数' as kamoku from aaa
union
select n,r+0.1 as p,'理科' as kamoku from aaa
union
select n,sh as p,'社会' as kamoku from aaa) as t1,
(select n,k+0.3 as p,'国語' as kamoku from aaa
union
select n,s+0.2 as p,'算数' as kamoku from aaa
union
select n,r+0.1 as p,'理科' as kamoku from aaa
union
select n,sh as p,'社会' as kamoku from aaa) as t2,
(select n,k+0.3 as p,'国語' as kamoku from aaa
union
select n,s+0.2 as p,'算数' as kamoku from aaa
union
select n,r+0.1 as p,'理科' as kamoku from aaa
union
select n,sh as p,'社会' as kamoku from aaa) as t3,
(select n,k+0.3 as p,'国語' as kamoku from aaa
union
select n,s+0.2 as p,'算数' as kamoku from aaa
union
select n,r+0.1 as p,'理科' as kamoku from aaa
union
select n,sh as p,'社会' as kamoku from aaa) as t4
where t0.n = t1.n
and t0.n = t2.n
and t0.n = t3.n
and t0.n = t4.n
and t1.p > t2.p
and t2.p > t3.p
and t3.p > t4.p