select bm,lx,sum(cnt) from (select bm, lx,case when lx=lx1 then lx1cs when lx=lx2 then lx2cs when lx=lx3 then lx3cs when lx=lx4 then lx4cs else 0 end cnt from 表A) 表Bgroup by bm,lx
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>
select bm ,lx ,count(*) from table_Name where lx1cs = 1 and lx = 'lx1' group by bm,lx 需要那么复杂吗,这个语句没有测试过,基本思路就是这样的。
(select
bm, lx,case when lx=lx1 then lx1cs
when lx=lx2 then lx2cs
when lx=lx3 then lx3cs
when lx=lx4 then lx4cs else 0 end cnt from 表A) 表Bgroup by bm,lx
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>
where lx1cs = 1 and lx = 'lx1'
group by bm,lx
需要那么复杂吗,这个语句没有测试过,基本思路就是这样的。