17,377
社区成员
发帖
与我相关
我的任务
分享
id bm lx lx1cs lx2cs lx3cs lx4cs
1 1 lx1 0 0 0 0
2 1 lx1 1 0 0 0
3 2 lx2 0 0 0 0
4 2 lx2 0 1 0 0
5 2 lx3 0 0 0 0
6 2 lx3 0 0 1 0
7 2 lx4 0 0 0 0
8 2 lx4 0 0 0 1
9 2 lx1 1 0 0 0
10 1 lx2 0 0 0 0
11 1 lx3 0 0 1 0
12 1 lx4 0 0 0 1
13 1 lx1 0 0 0 0
bm lx count
1 lx1 2
1 lx2 0
1 lx3 1
1 lx4 1
2 lx1 1
2 lx2 1
2 lx3 1
2 lx4 1
SQL> --如果lx不固定,可能要使用存储过程来拼接动态SQL来执行
SQL> with a as (
2 select 1 id,1 bm, 'lx1' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
3 select 2 id,1 bm, 'lx1' lx, 1 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
4 select 3 id,2 bm, 'lx2' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
5 select 4 id,2 bm, 'lx2' lx, 0 lx1cs,1 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
6 select 5 id,2 bm, 'lx3' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
7 select 6 id,2 bm, 'lx3' lx, 0 lx1cs,0 lx2cs, 1 lx3cs, 0 lx4cs from dual union all
8 select 7 id,2 bm, 'lx4' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
9 select 8 id,2 bm, 'lx4' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 1 lx4cs from dual union all
10 select 9 id,2 bm, 'lx1' lx, 1 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
11 select 10 id,1 bm, 'lx2' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual union all
12 select 11 id,1 bm, 'lx3' lx, 0 lx1cs,0 lx2cs, 1 lx3cs, 0 lx4cs from dual union all
13 select 12 id,1 bm, 'lx4' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 1 lx4cs from dual union all
14 select 13 id,1 bm, 'lx1' lx, 0 lx1cs,0 lx2cs, 0 lx3cs, 0 lx4cs from dual)
15 SELECT bm,
16 lx,
17 CASE
18 WHEN lx = 'lx1' THEN
19 SUM(lx1cs)
20 WHEN lx = 'lx2' THEN
21 SUM(lx2cs)
22 WHEN lx = 'lx3' THEN
23 SUM(lx3cs)
24 WHEN lx = 'lx4' THEN
25 SUM(lx4cs)
26 END cnt
27 FROM a
28 GROUP BY bm, lx;
BM LX CNT
---------- --- ----------
1 lx1 1
2 lx4 1
2 lx2 1
2 lx1 1
1 lx2 0
2 lx3 1
1 lx4 1
1 lx3 1
已选择8行。
SQL>