mysql - sql 統計某列下的4個值，分別是多少個

問題描述

dg_order_time下的4個值 1,2,3,4他們各自的總和是多少個？是各自，不是這列的總和...

問題解答

你是要實現每一行數據中的所有相同數的總和嗎？所有行的1的總和；所有行2的總和...。是不！

這是橫的

select a.t1, b.t2, c.t3, d.t4from (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 allselect ’t2’, count(*) from table where col like ’%2%’union allselect ’t3’, count(*) from table where col like ’%3%’union allselect ’t4’, count(*) from table where col like ’%4%’

大致思路就是這樣

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;