mysql - sql 统计某列下的4个值，分别是多少个

Question

dg_order_time下的4个值 1,2,3,4他们各自的总和是多少个？是各自，不是这列的总和...

Answer 1

你是要实现每一行数据中的所有相同数的总和吗？所有行的1的总和；所有行2的总和...。是不！

Answer 2

这是横的

select a.t1, b.t2, c.t3, d.t4
from (select count(*) t1 from table where col like '%1%') a,
(select count(*) t2 from table where col like '%2%') b,
(select count(*) t3 from table where col like '%3%') c,
(select count(*) t4 from table where col like '%4%') d

这是竖的

select 't1', count(*) from table where col like '%1%'
union all
select 't2', count(*) from table where col like '%2%'
union all
select 't3', count(*) from table where col like '%3%'
union all
select 't4', count(*) from table where col like '%4%'

大致思路就是这样

Answer 3

select nvl(substr(t.str, 0, 1),0) +
       nvl(substr(t.str, (case when instr(t.str, ',', 1, 1) < 0 then -1
                 when instr(t.str, ',', 1, 1) > 0 then instr(t.str, ',', 1, 1)+1 end), 1),0) +
       nvl(substr(t.str, (case when instr(t.str, ',', 1, 2) < 0 then -1
                 when instr(t.str, ',', 1, 2) > 0 then instr(t.str, ',', 1, 2)+1 end), 1),0) +
       nvl(substr(t.str, (case when instr(t.str, ',', 1, 3) < 0 then -1
                 when instr(t.str, ',', 1, 3) > 0 then instr(t.str, ',', 1, 3)+1 end), 1),0)
  from test_calc t;