已知A、B、C三人,进行互评打分,基本表TabDF如下打分人 被打分人 分数
A A M1
A B M2
A C M3
B A M4
B B M5
B C M6
C A M7
C B M8
C C M9如何设计一个查询
结果是:
A B C
A M1 M4 M7
B M2 M5 M8
C M3 M6 M9
A A M1
A B M2
A C M3
B A M4
B B M5
B C M6
C A M7
C B M8
C C M9如何设计一个查询
结果是:
A B C
A M1 M4 M7
B M2 M5 M8
C M3 M6 M9
用水晶报表等报表工具的交叉表做.
如果不用的话分情况.
1.表中人数固定,
直接用decode写死.
2.表中人数不固定,
写过程,拼动态sql.
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?4002
WITH TabDF AS(
SELECT 'A' p1,'A' p2,'M1' sorts FROM dual UNION ALL
SELECT 'A', 'B', 'M2' FROM dual UNION ALL
SELECT 'A', 'C', 'M3' FROM dual UNION ALL
SELECT 'B', 'A', 'M4' FROM dual UNION ALL
SELECT 'B', 'B', 'M5' FROM dual UNION ALL
SELECT 'B', 'C', 'M6' FROM dual UNION ALL
SELECT 'C', 'A', 'M7' FROM dual UNION ALL
SELECT 'C', 'B', 'M8' FROM dual UNION ALL
SELECT 'C', 'C', 'M9' FROM dual
)
SELECT p1,Max(Decode(p2,'A',sorts)) A,
Max(Decode(p2,'B',sorts)) B,
Max(Decode(p2,'C',sorts)) C
FROM TabDF
GROUP BY P1
P1 A B C
--------------------
A M1 M4 M7
B M2 M5 M8
C M3 M6 M9
with tab as (
select 'A' m, 'A' n, 'M1' k from dual union all
select 'A' m, 'B' n, 'M2' k from dual union all
select 'A' m, 'C' n, 'M3' k from dual union all
select 'B' m, 'A' n, 'M4' k from dual union all
select 'B' m, 'B' n, 'M5' k from dual union all
select 'B' m, 'C' n, 'M6' k from dual union all
select 'C' m, 'A' n, 'M7' k from dual union all
select 'C' m, 'B' n, 'M8' k from dual union all
select 'C' m, 'C' n, 'M9' k from dual
)
select m,wm_concat(decode(n,'A',k)) A, wm_concat(decode(n,'B',k)) B ,wm_concat(decode(n,'C',k)) C from tab group by m
如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下:select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;
select 打分人,SUM(decode(被打分人,'A',分数,0)) "A",
SUM(decode(被打分人,'B',分数,0)) "B",
SUM(decode(被打分人,'C',分数,0)) "C"
from tb
group by 打分人
WITH tb AS(
SELECT 'A' s,'A' e,'M1' v FROM dual UNION ALL
SELECT 'A', 'B', 'M2' FROM dual UNION ALL
SELECT 'A', 'C', 'M3' FROM dual UNION ALL
SELECT 'B', 'A', 'M4' FROM dual UNION ALL
SELECT 'B', 'B', 'M5' FROM dual UNION ALL
SELECT 'B', 'C', 'M6' FROM dual UNION ALL
SELECT 'C', 'A', 'M7' FROM dual UNION ALL
SELECT 'C', 'B', 'M8' FROM dual UNION ALL
SELECT 'C', 'C', 'M9' FROM dual
)
SELECT replace(wm_concat(v), ',', ' ') from tb group by s;
动态的貌似就两个解决方案,一个是拼写动态的SQL,另一个就是拼接字符串。
SELECT 'A' COL1,'A' COL2,'M1' COL3 FROM DUAL
UNION ALL
SELECT 'A', 'B', 'M2' FROM DUAL
UNION ALL
SELECT 'A', 'C', 'M3' FROM DUAL
UNION ALL
SELECT 'B', 'A', 'M4' FROM DUAL
UNION ALL
SELECT 'B', 'B', 'M5' FROM DUAL
UNION ALL
SELECT 'B', 'C', 'M6' FROM DUAL
UNION ALL
SELECT 'C', 'A', 'M7' FROM DUAL
UNION ALL
SELECT 'C', 'B', 'M8' FROM DUAL
UNION ALL
SELECT 'C', 'C', 'M9' FROM DUAL
)
SELECT COL1,MAX(DECODE(COL2,'A',COL3,NULL)) A,
MAX(DECODE(COL2,'B',COL3,NULL)) B,
MAX(DECODE(COL2,'C',COL3,NULL)) C
FROM TB GROUP BY COL1C A B C
- -- -- --
A M1 M2 M3
B M4 M5 M6
C M7 M8 M9
--执行后结果如下
SQL> select * from v_tmp;C A B C
- -- -- --
A M1 M2 M3
B M4 M5 M6
C M7 M8 M9