求一个SQL语句

Question

有表如下
id   number     codes
--------------------------
1    10         1001011
2    15         0111010   
3    16         0110011
4    24         0111101
5    32         1011110
6    12         1000001求codes列的
第一个字符为1 的 所有列number和并上
第二个字符为1 的 所有列number和并上
第三个字符为1 的 所有列number和并上
第四个字符为1 的 所有列number和并上
第五个字符为1 的 所有列number和并上
第六个字符为1 的 所有列number和并上
第七个字符为1 的 所有列number和并上如 ：
第一个字符为1 的 所有列number和 为 10+32+12=54
第二个字符为1 的 所有列number和 为 15+16+24=55
.......
第七个字符为1 的 所有列number和 为 10+16+24+12=62希望语句执行结果 如下：
1    54
2    55
.......
7    62

Answer 1

--抛砖引玉，等高手的
select 1, sum(num) from test where substr(codes,1,1)=1
union all
select 2,sum(num) from test where substr(codes,2,1)=1
union all
select 3,sum(num) from test where substr(codes,3,1)=1
union all
select 4,sum(num) from test where substr(codes,4,1)=1
union all
select 5,sum(num) from test where substr(codes,5,1)=1
union all
select 6,sum(num) from test where substr(codes,6,1)=1
union all
select 7,sum(num) from test where substr(codes,7,1)=1

Answer 2

with tmp as
(
select 1 id, 10 n, '1001011' code from dual union all
select 2 id, 15 n, '0111010' code from dual union all
select 3 id, 16 n, '0110011' code from dual union all
select 4 id, 24 n, '0111101' code from dual union all
select 5 id, 32 n, '1011110' code from dual union all
select 6 id, 12 n, '1000001' code from dual
)
select l, sum(n) from (
select distinct l, n, code from (
select level l, id, n, code 
from tmp
where substr(code,level,1)=1
connect by level <= (select max(length(code)) from tmp)
)
)
group by l
order by l;         L     SUM(N)
---------- ----------
         1         54
         2         55
         3         87
         4         81
         5         56
         6         73
         7         62性能有待改進

Answer 3

lz这样问非常的舒服。有数据，描述也清楚，先赞一个。如果是只固定的codes的长度不超过7的话，可以不使用connect by，而直接用union可能会性能上好些。

Answer 4

限定查询----LIKE 的使用
 查询中雇员的名字第二个字符是M的雇员信息
 SELECT empno,ename,comm,salFROM empWHERE ename LIKE '_M%';
 说明： _ 匹配一个字符，% 匹配0个或多个字符希望这个对lz有所帮助！
至于实现，楼上都有了。

Answer 5

with tmp as
(
select 1 id, 10 n, '1001011' code from dual union all
select 2 id, 15 n, '0111010' code from dual union all
select 3 id, 16 n, '0110011' code from dual union all
select 4 id, 24 n, '0111101' code from dual union all
select 5 id, 32 n, '1011110' code from dual union all
select 6 id, 12 n, '1000001' code from dual
)
SELECT lev,Sum(n) FROM(
       SELECT distinct LEVEL lev,n
       FROM tmp
       WHERE  Decode(SubStr(code,LEVEL,1),'1',1,0)=1
       CONNECT BY LEVEL<=Length(code)
)
GROUP BY lev结果：
         LEV     SUM(N)
----------- ----------
         1         54
         2         55
         3         87
         4         81
         5         56
         6         73
         7         62

Answer 6

SQL> select * from ttt;
 
        ID        NUM CODES
---------- ---------- --------------------------------------------------------------------------------
         1         10 1001011
         2         15 0111010
         3         16 0110011
         4         24 0111101
         5         32 1011110
         6         12 1000001
 
6 rows selected
 
SQL> 
SQL> select b.rn, sum(decode(substr(a.codes, b.rn, 1), '1', a.num, 0))
  2    from ttt a,
  3         (select rownum rn
  4            from dual
  5          connect by rownum <= (select max(length(codes)) - 1 from ttt)) b
  6   group by b.rn;
 
        RN SUM(DECODE(SUBSTR(A.CODES,B.RN
---------- ------------------------------
         1                             54
         2                             55
         3                             87
         4                             81
         5                             56
         6                             73
         7                             62
 
7 rows selected
 
SQL> 

Answer 7

如果你表中记录数比较多，最好不要用connect by 效率之低级！还要用distinct去重，资源更是浪费，如果code长度都是固定的7位，那么用union all效率高很多的！

Answer 8

用中间表用笛卡尔进行关联至少说毕connect by好点,数据多了 connect by 简直就是噩运到来！