表結構大概是這樣
用戶名(唯一) 交易日期
a 20170101
b 20170101
c 20170102
a 20170101
b 20170102
..
a 20170130
b 20170201
...
c 20170228
需求是查詢每個月有多少筆業務，去除每個月的重復筆數。
我只寫了一個sql陳述句，
SELECT
COUNT(DISTINCT(用戶名))
FROM a
WHERE `審批通過時間` LIKE '%2017-01%'
但是我想把結構變成
1月 2月 3月 。。。。12月
3 4 5 4
這樣的結構，請問該怎么寫啊
uj5u.com熱心網友回復：
貼下insert陳述句。uj5u.com熱心網友回復：
正常結貼11，為結貼15。。。uj5u.com熱心網友回復：
create table table_tmp as
select 'a' username, '20170101' time_ from dual union all
select 'b' username, '20170101' time_ from dual union all
select 'c' username, '20170102' time_ from dual union all
select 'a' username, '20170101' time_ from dual union all
select 'b' username, '20170102' time_ from dual union all
select 'a' username, '20170130' time_ from dual union all
select 'b' username, '20170201' time_ from dual union all
select 'c' username, '20170228' time_ from dual ;
select * from (select '統計' " ",count(*) counts,substr(time_,5,2) mon from table_tmp group by substr(time_,5,2))
pivot(sum(counts) for mon in ('01' "1月",'02' "2月",'03' "3月",'04' "4月",'05' "5月",'06' "6月"))
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79207.html
標籤：開發