数据库表里结果如下:
time lx count
2010-3-16 1 10
2010-3-16 2 20
2010-3-16 101 10
2010-3-16 102 30
2010-3-16 201 40
2010-3-16 202 50其中time列为date类型,lx列为number类型,count为number类型
现在想得到下面的结果:time lx0 lx1 lx2
2010-3-16 30 40 90其中lx0的值为lx为1 和2 加起来的值
lx1 的值为101、102类型加起来的值
lx2的值为201、202加起来的值。
如何实现?还有另外一种结果,即把末位相同的进行相加time lx1 lx2
2010-3-16 60 100其中lx1的值为1、101、201加起来的值
lx2的值为2、102、202加起来的值请问各位SQL高手
如何实现这两种统计结果?
time lx count
2010-3-16 1 10
2010-3-16 2 20
2010-3-16 101 10
2010-3-16 102 30
2010-3-16 201 40
2010-3-16 202 50其中time列为date类型,lx列为number类型,count为number类型
现在想得到下面的结果:time lx0 lx1 lx2
2010-3-16 30 40 90其中lx0的值为lx为1 和2 加起来的值
lx1 的值为101、102类型加起来的值
lx2的值为201、202加起来的值。
如何实现?还有另外一种结果,即把末位相同的进行相加time lx1 lx2
2010-3-16 60 100其中lx1的值为1、101、201加起来的值
lx2的值为2、102、202加起来的值请问各位SQL高手
如何实现这两种统计结果?
-------------- ---------- ----------
16-3月 -10 202 50
16-3月 -10 201 40
16-3月 -10 102 30
16-3月 -10 101 10
16-3月 -10 2 20
16-3月 -10 1 10已选择6行。SQL> SELECT TIME,
2 SUM(decode(lx,1,COUNT,2,COUNT)) lx0,
3 SUM(decode(lx,101,COUNT,102,COUNT)) lx1,
4 SUM(decode(lx,201,COUNT,202,COUNT)) lx2
5 FROM test
6 GROUP BY TIME;TIME LX0 LX1 LX2
-------------- ---------- ---------- ----------
16-3月 -10 30 40 90
SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。SQL> SELECT TIME,
2 SUM(decode(lx,1,COUNT,2,COUNT)) lx0,
3 SUM(decode(lx,101,COUNT,102,COUNT)) lx1,
4 SUM(decode(lx,201,COUNT,202,COUNT)) lx2
5 FROM test
6 GROUP BY TIME;TIME LX0 LX1 LX2
---------- ---------- ---------- ----------
2010-03-16 30 40 90SQL> SELECT TIME,
2 SUM(decode(lx,1,COUNT,101,COUNT,201,COUNT)) lx1,
3 SUM(decode(lx,2,COUNT,102,COUNT,202,COUNT)) lx2
4 FROM test
5 GROUP BY TIME;TIME LX1 LX2
---------- ---------- ----------
2010-03-16 60 100SQL>
union all select '2009',2,20 from dual
union all select '2009',102,20 from dual
union all select '2009',101,20 from dual
union all select '2009',201,30 from dual
union all select '2009',202,30 from dual)
select time,
sum(case when length(lx) = 1 then cnt end) lx0,
sum(case when length(lx) > 1 and substr(lx,1,1) = '1' then cnt end) lx1,
sum(case when length(lx) > 1 and substr(lx,1,1) = '2' then cnt end) lx2
from t
group by time;
CREATE TABLE t2 (time date,lx NUMBER(4), c NUMBER(4));
SELECT TIME,WMSYS.WM_CONCAT(x) s1, WMSYS.WM_CONCAT(S) S2
FROM (SELECT TIME,lx-rn x, SUM(C) S
FROM (SELECT T2.*,
ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY LX) RN
FROM T2)
GROUP BY TIME, LX - RN)
GROUP BY TIME;SELECT TIME,WMSYS.WM_CONCAT(x) S1, WMSYS.WM_CONCAT(S) S2
FROM (SELECT TIME,MOD(lx,2) x ,SUM(C) S FROM T2 GROUP BY TIME, MOD(LX, 2))
GROUP BY TIME;
union all select '2009',2,20 from dual
union all select '2009',102,20 from dual
union all select '2009',101,20 from dual
union all select '2009',201,30 from dual
union all select '2009',202,30 from dual) select time, substr(max(sys_connect_by_path(lx, ',')), 2) lx, sum(cnt) cnt
from t tt
start with not exists (select 1
from t
where time = tt.time
and lx = tt.lx - 1)
connect by prior time = time
and prior lx = lx - 1
group by time, rownum - level;
sum(decode(mod(lx, 10), 1, cnt)) lx1,
sum(decode(mod(lx, 10), 2, cnt)) lx2
from t
group by time