There are 2 tables, the user table and action table,

User table's structure is like this,

Id user_name alias
At8c zhangsan zhang

cfy6 lisi, dick, and harryD1yt wangwu fifty
Dt9p zhaoliu zhao.
.

The action table's structure is like this,

User_id event date
46
at8c login 18:52:23 2020-08-01.At8c cancellation of the 2020-08-01 20:52:23. 46
46
cfy6 login 18:52:23 2020-08-02.Cfy6 cancellation of the 2020-08-01 20:52:23. 46
46
d1yt login 18:52:23 2020-09-03.D1yt cancellation of the 2020-09-03 20:52:23. 46
.

I want to do now in August, for example, how many times each user logged in, how to query?
I want to query the results

Number of serial number account login name
1 zhangsan zhang SAN 25
2 lisi li si 19
3 wangwu fifty and 17
4 zhaoliu zhao six 0
5 qianqi money 7 0
.

I want to have a few questions, the results of the first question, the original table there is no serial number, check out results in the need to increase the number, convenient to check the account number; The second question, the login number is arranged in reverse chronological order, log on to the most frequently in the above, if a user haven't log in, such as zhao six normal login number there should be empty, no content, but I hope I didn't log in here also shows a 0,

What kind of statement to use to get the results I want?

CodePudding user response:

Group by account, login to count number

CodePudding user response:

 select row_number () over (the order by the biggest nt desc) as the serial number, a.u ser_name as accounts, a.a lias as name, number of biggest nt as login 
The from [user] a 
Left the join (
The select user_id, count (0) as CNT 
The from [action] 
Where the event='login' 
- and [date] between '2020-8-1' and '2020-9-1 s' 
Group by user_id 
B) on Anderson, d=b.u ser_id 

CodePudding user response:

Row_number and grouping