The user name (only) transaction date
A 20170101
B 20170101
C 20170102
A 20170101
B 20170102
.
A 20170130
B 20170201
.
C 20170228
Demand is the query each month how many business transaction, remove the repeat number of each month,
I only wrote a SQL statement,
SELECT
COUNT (DISTINCT (user name))
FROM a
WHERE ` approved time ` LIKE '% 2017-01%'
But I want to make structure
On January 2 month of march,,,,
in December4, 3, 4, 5
This structure, could you tell me how to write
CodePudding user response:
The insert statement,CodePudding user response:
Normal junction 11, as the knot on 15,,,CodePudding user response:
create table table_tmp as
Select the 'a' username, '20170101' time_ from dual union all
Select the username 'b', '20170101' time_ from dual union all
Select the username 'c', '20170102' time_ from dual union all
Select the 'a' username, '20170101' time_ from dual union all
Select the username 'b', '20170102' time_ from dual union all
Select the 'a' username, '20170130' time_ from dual union all
Select the username 'b', '20170201' time_ from dual union all
Select the username 'c', '20170228' time_ from dual;
Select * from (select the 'statistics' ", "the count (*) counts, substr (time_, 5, 2) mon from table_tmp group by substr (time_, 5, 2))
Pivot (sum (counts) for mon (in '01 "January", "February"' 02 ', '03 "march",' 04 '" April ",' 05 '" may ", "June")' 06 ')