表结构如下:
BODYTEST_ID(主键) BODYTEST_STUID(学生Id) BODYTEST_NAME(测量项) BODYTEST_VALUE(测量项值)
1 1 血压 200
2 1 血脂 200
3 2 血压 200
4 2 血脂 200
5 2 维生素A 200我想要显示成这样的结果:学生姓名 血压 血脂 维生素A
1 200 200 Null
2 200 200 200查询语句应该怎样写?
BODYTEST_ID(主键) BODYTEST_STUID(学生Id) BODYTEST_NAME(测量项) BODYTEST_VALUE(测量项值)
1 1 血压 200
2 1 血脂 200
3 2 血压 200
4 2 血脂 200
5 2 维生素A 200我想要显示成这样的结果:学生姓名 血压 血脂 维生素A
1 200 200 Null
2 200 200 200查询语句应该怎样写?
select 1 BODYTEST_ID,1 BODYTEST_STUID,'血压' BODYTEST_NAME,200 BODYTEST_VALUE from dual
union all
select 2 BODYTEST_ID,1 BODYTEST_STUID,'血脂' BODYTEST_NAME,200 BODYTEST_VALUE from dual
union all
select 3 BODYTEST_ID,2 BODYTEST_STUID,'血压' BODYTEST_NAME,200 BODYTEST_VALUE from dual
union all
select 4 BODYTEST_ID,2 BODYTEST_STUID,'血脂' BODYTEST_NAME,200 BODYTEST_VALUE from dual
union all
select 5 BODYTEST_ID,2 BODYTEST_STUID,'维生素A' BODYTEST_NAME,200 BODYTEST_VALUE from dual
)
select BODYTEST_STUID,max(decode(BODYTEST_NAME,'血压',BODYTEST_VALUE,null)) 血压,
max(decode(BODYTEST_NAME,'血脂',BODYTEST_VALUE,null)) 血脂,
max(decode(BODYTEST_NAME,'维生素A',BODYTEST_VALUE,null)) 维生素A
from temp group by BODYTEST_STUID
with temp as(
select 1 BODYTEST_ID,1 BODYTEST_STUID,'血压' BODYTEST_NAME,'200' BODYTEST_VALUE from dual
union all
select 2 BODYTEST_ID,1 BODYTEST_STUID,'血脂' BODYTEST_NAME,'200' BODYTEST_VALUE from dual
union all
select 3 BODYTEST_ID,2 BODYTEST_STUID,'血压' BODYTEST_NAME,'200' BODYTEST_VALUE from dual
union all
select 4 BODYTEST_ID,2 BODYTEST_STUID,'血脂' BODYTEST_NAME,'200' BODYTEST_VALUE from dual
union all
select 5 BODYTEST_ID,2 BODYTEST_STUID,'维生素A' BODYTEST_NAME,'200' BODYTEST_VALUE from dual
)
select BODYTEST_STUID,max(decode(BODYTEST_NAME,'血压',BODYTEST_VALUE,null)) 血压,
max(decode(BODYTEST_NAME,'血脂',BODYTEST_VALUE,null)) 血脂,
max(decode(BODYTEST_NAME,'维生素A',BODYTEST_VALUE,null)) 维生素A
from temp group by BODYTEST_STUID;结果:
BODYTEST_STUID 血压 血脂 维生素A
---------------------------------------------
1 200 200
2 200 200 200
SQL> select max('aaa') from dual;MAX
---
aaaSQL> 也可以的
--max+decode便可
SQL> with tb as(
2 select 1 BODYTEST_ID,1 BODYTEST_STUID,'血压' BODYTEST_NAME,200 BODYTEST_VALUE from dual
3 union all
4 select 2 ,1,'血脂',200 from dual union all
5 select 3 ,2,'血压',200 from dual union all
6 select 4 ,2 ,'血脂',200 from dual union all
7 select 5 ,2 ,'维生素A',200 from dual
8 )
9 select BODYTEST_STUID,max(decode(BODYTEST_NAME,'血压',BODYTEST_VALUE)) 血压,
10 max(decode(BODYTEST_NAME,'血脂',BODYTEST_VALUE)) 血脂,
11 max(decode(BODYTEST_NAME,'维生素A',BODYTEST_VALUE)) 维生素A
12 from tb
13 group by BODYTEST_STUID
14 /BODYTEST_STUID 血压 血脂 维生素A
-------------- ---------- ---------- ----------
1 200 200
2 200 200 200
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?96906