Zhang SAN 0 1 2020-05-22 10:22:33
2 bill 20 2020-05-22 11:22:33
3 two 1 2020-05-23 10:22:33
4 * * 0 2020-05-24 10:22:33
5 zhang SAN 2 2020-05-25 10:42:33
6 zhang SAN 0 2020-05-26 10:25:31
7 threes 3 2020-05-26 10:25:33
8 zhang SAN 10 2020-05-27 10:25:33
.
The above data statistics according to the date of each type, the number of occurrences of (type of type 0,1,2,3,4,11,20)
Hope the result is:
Date type 0 type 1, type 2 3 4 type 11 type 20
The 2020-05-22 1 0 0 0 0 0 1
The 2020-05-23 0 1 0 0 0 0 0
The 2020-05-24 1 0 0 0 0 0 0
CodePudding user response:
Create table test (id int, Account varchar (50), type varchar (50), the time a datetime)
Insert into test (id, Account Type, [Time])
Select 1, 'zhang' 0, 'the 2020-05-22 10:22:33'
Union select 2, 'li si, 20,' the 2020-05-22 11:22:33 '
Union select 3, 'the king 2, 1,' the 2020-05-23 10:22:33 '
Union select 4, 'zhang' 0, 'the 2020-05-24 10:22:33'
Union select 5, 'zhang', 2, '2020-05-25 10:42:33'
Union select 6, 'zhang' 0, 'the 2020-05-26 10:25:31'
Union select 7, 'zhang', 3, 'the 2020-05-26 10:25:33'
Union select 8, 'zhang SAN, 10,' the 2020-05-27 10:25:33 '
Select * from
(
Select the CONVERT (varchar (10), time, 120) as the time, the type from the test
) a
The pivot (count (type) for type in ([0], [1], [2], [3], [4], [11], [20])) as t