数据表结构如下:
ID MONTH QUANTITY NAME
1 2010-01 10 A
2 2010-02 3 A
3 2010-01 5 B
4 2010-02 15 B
5 2010-01 2 C
6 2010-02 5 C
期望的结果
能够用NAME进行分组,并能通过MONTH进行分别排序
例一:对MONTH 为2010-01的QUANTITY进行升序排序,期望的结果如下:NAME 2010-01 2010-02
C 2 5
B 5 15
A 10 3例二:对MONTH为2010-02的QUANTITY进行降序排序,期望的结果如下:
NAME 2010-01 2010-02
B 5 15
C 2 5
A 10 3很惭愧,本人想了半天也没有解决。望不吝赐教!!!!
ID MONTH QUANTITY NAME
1 2010-01 10 A
2 2010-02 3 A
3 2010-01 5 B
4 2010-02 15 B
5 2010-01 2 C
6 2010-02 5 C
期望的结果
能够用NAME进行分组,并能通过MONTH进行分别排序
例一:对MONTH 为2010-01的QUANTITY进行升序排序,期望的结果如下:NAME 2010-01 2010-02
C 2 5
B 5 15
A 10 3例二:对MONTH为2010-02的QUANTITY进行降序排序,期望的结果如下:
NAME 2010-01 2010-02
B 5 15
C 2 5
A 10 3很惭愧,本人想了半天也没有解决。望不吝赐教!!!!
with tb as(
select 1 ID, to_date('2010-01','yyyy-mm') MONTH,10 QUANTITY,'A' NAME FROM DUAL
UNION ALL
select 2,to_date('2010-02','yyyy-mm'),3,'A'FROM DUAL
UNION ALL
select 3,to_date('2010-01','yyyy-mm'),5,'B'FROM DUAL
UNION ALL
select 4,to_date('2010-02','yyyy-mm'),15,'B'FROM DUAL
UNION ALL
select 5,to_date('2010-01','yyyy-mm'),2,'C'FROM DUAL
UNION ALL
select 6,to_date('2010-02','yyyy-mm'),5,'C'FROM DUAL)
SELECT NAME,MAX(DECODE(MONTH,to_date('2010-01','yyyy-mm'),QUANTITY,NULL)) "2010-01",
MAX(DECODE(MONTH,to_date('2010-02','yyyy-mm'),QUANTITY,NULL)) "2010-02"
FROM TB
GROUP BY NAME
ORDER BY 2--按照2010-01排序 即第2列
N 2010-01 2010-02
- ---------- ----------
C 2 5
B 5 15
A 10 3
select 1 ID, to_date('2010-01','yyyy-mm') MONTH,10 QUANTITY,'A' NAME FROM DUAL
UNION ALL
select 2,to_date('2010-02','yyyy-mm'),3,'A'FROM DUAL
UNION ALL
select 3,to_date('2010-01','yyyy-mm'),5,'B'FROM DUAL
UNION ALL
select 4,to_date('2010-02','yyyy-mm'),15,'B'FROM DUAL
UNION ALL
select 5,to_date('2010-01','yyyy-mm'),2,'C'FROM DUAL
UNION ALL
select 6,to_date('2010-02','yyyy-mm'),5,'C'FROM DUAL)
SELECT NAME,MAX(DECODE(MONTH,to_date('2010-01','yyyy-mm'),QUANTITY,NULL)) "2010-01",
MAX(DECODE(MONTH,to_date('2010-02','yyyy-mm'),QUANTITY,NULL)) "2010-02"
FROM TB
GROUP BY NAME
ORDER BY 3----按照2010-02排序 即第3列N 2010-01 2010-02
- ---------- ----------
A 10 3
C 2 5
B 5 15
MAX(DECODE(MONTH,to_date('2010-02','yyyy-mm'),QUANTITY,NULL)) "2010-02"
FROM TB
GROUP BY NAME
ORDER BY 3 desc --降序的话加个desc
好在2楼的哪位看明白了。谢谢2楼的回复。
不过照你的方法如果有很多行数据我岂不是要写N条Select语句,本人认为这个方法不太好,也可能是我理解错了,还请指教。
有没有更好的办法那?
with tb as(
select 1 ID, to_date('2010-01','YYYY-MM') MONTH,10 QUANTITY,'A' NAME FROM DUAL
UNION ALL
select 2,to_date('2010-02','YYYY-MM'),3,'A'FROM DUAL
UNION ALL
select 3,to_date('2010-01','YYYY-MM'),5,'B'FROM DUAL
UNION ALL
select 4,to_date('2010-02','YYYY-MM'),15,'B'FROM DUAL
UNION ALL
select 5,to_date('2010-01','YYYY-MM'),2,'C'FROM DUAL
UNION ALL
select 6,to_date('2010-02','YYYY-MM'),2,'C'FROM DUAL
UNION ALL
select 7,to_date('2010-03','YYYY-MM'),5,'C'FROM DUAL)
SELECT REPLACE(WMSYS.WM_CONCAT(LPAD(TO_CHAR(QUANTITY), 10, ' ')),',') FROM TB GROUP BY NAME;
sum(decode(to_char(month,'yyyy-mm'),'2010-01',QUANTITY)) "2010-01"
from tb
group by name
order by "2010-01" --或者"2010-02"