表结构大概是这样
用户名(唯一) 交易日期
a 20170101
b 20170101
c 20170102
a 20170101
b 20170102
..
a 20170130b 20170201
...
c 20170228需求是查询每个月有多少笔业务,去除每个月的重复笔数。
我只写了一个sql语句,
SELECT
COUNT(DISTINCT(用户名))
FROM a
WHERE `审批通过时间` LIKE '%2017-01%'但是我想把结构变成1月 2月 3月 。12月
3 4 5 4
这样的结构,请问该怎么写啊
用户名(唯一) 交易日期
a 20170101
b 20170101
c 20170102
a 20170101
b 20170102
..
a 20170130b 20170201
...
c 20170228需求是查询每个月有多少笔业务,去除每个月的重复笔数。
我只写了一个sql语句,
SELECT
COUNT(DISTINCT(用户名))
FROM a
WHERE `审批通过时间` LIKE '%2017-01%'但是我想把结构变成1月 2月 3月 。12月
3 4 5 4
这样的结构,请问该怎么写啊
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月"))