Come in to help me take a look at it, in this case mysql to use what kind of statement to get the re-CodePudding

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:

Select (@ n:=@ n + 1) serial number, u.u ser_name account, u.a lias name, count (1) the login number from the user u
Left the join the action a on u.i d=a.u ser_id and date_format (a. d. ate, '% % m Y')='202008'
Inner join (select @ n:=0) r
Group by u.i d, u.u ser_name, u.a lias
You don't blind to write SQL, tested, you can try to use, sorting can be in finally add a order by a try, can't put the above statement to add a layer of a temporary table to go outside the order by

CodePudding user response:

reference 1st floor evanweng response:

select (@ n:=@ n + 1) serial number, u.u ser_name account, u.a lias name, count (1) the login number from the user u
Left the join the action a on u.i d=a.u ser_id and date_format (a. d. ate, '% % m Y')='202008'
Inner join (select @ n:=0) r
Group by u.i d, u.u ser_name, u.a lias
You don't blind to write SQL, tested, you can try to use, sorting can be in finally add a order by a try, can't put the above statement to add a layer of a temporary table to go outside the order by

CodePudding user response:

refer to the second floor evanweng response:

Quote: refer to 1st floor evanweng response:
select (@ n:=@ n + 1) serial number, u.u ser_name account, u.a lias name, count (1) the login number from the user u
Left the join the action a on u.i d=a.u ser_id and date_format (a. d. ate, '% % m Y')='202008'
Inner join (select @ n:=0) r
Group by u.i d, u.u ser_name, u.a lias
You don't blind to write SQL, tested, you can try to use, sorting can be in finally add a order by a try, can't put the above statement to add a layer of a temporary table to go outside the order by

Select (@ n:=@ n + 1) serial number, u.u ser_name account, u.a lias name, count (1) the login number from the user u
Left the join the action a on u.i d=a.u ser_id and date_format (a. d. ate, '% % m Y')='202008'
Inner join (select @ n:=0) r
Group by u.i d, u.u ser_name, u.a lias
The order by count (1) desc

Sorry, I was wrong, this is not a mysql database is used, so do your this statement produces the following error:
[42000] [Microsoft] [11.0] SQL Server Native Client (SQL Server) must declare the scalar variable "@" n ", (137)
[42000] [Microsoft] [11.0] SQL Server Native Client (SQL Server) ':' near a syntax error, (102)
Do you know about used? In used should be how to implement?

CodePudding user response:

I'm not used to, you put (@ n:=@ n + 1) serial number and inner join (select @ n:=0) r removed, changed to essentially a sequence number column function is ok