With a statement like this:
Select the name of the action, priority, groupid, count (*) as the count from the users where clock & gt; 1598198400 group by the name of the action, priority, groupid order by count desc
The query results, is basically what I need, but there is a problem,
The name zhang SAN, for example, the man, he is a member of the group 1 and 2 groups at the same time? So he groupid is 1 and 2, the result of this statement repeated shows, the zhang SAN 1 group and zhang SAN 2 shows, also said the results in several, except gourpid, other items are all the same, the total count entry is doubled, in fact no matter zhang SAN is 1 or 2 group, is he, I just need to show one actually, can count 1 time, how to put this statement to change?

CodePudding user response:

 SELECT * FROM (
- press the groupid group combined with a serial number 
The select ROW_NUMBER () OVER (partition by groupid order by groupid) AS No, 
The name of the action, priority, groupid, count (*) as the count 
From the users where clock & gt; 1598198400 group by name, action, priority, groupid 
The order by count desc) A 
WHERE A.N o=1 -- take only one line 

CodePudding user response:

Mysql error on my side, this statement seems to be used in oracle?

CodePudding user response:

 
Select the name of the action, priority, Max (groupid) groupid, count 
The from 
(
Select the name of the action, priority, groupid, count (*) as the count 
From the users where clock & gt; 1598198400 
Group by the name of the action, priority, groupid 
The order by count desc 
TMP) 

On the basis of the group, and then to a group, by Max or min function to choose the groupid maximum or minimum of a set of can

CodePudding user response:

Your statement execution results only have one left, nor as a result, I need

My original statement shows the result of this,

Zhang SAN action senior 1 set of 6 times
Zhang SAN stop senior 1 set of 5
Zhang SAN action senior 2 set of 6
Zhang SAN stop senior two groups 5 times
Li si intermediate 3 action group 3 times
Fifty and stop low-level 6 group 2 times

I hope to show the result,

Zhang SAN action senior 1 set of 6 times
Zhang SAN stop senior 1 set of 5
Li si intermediate 3 action group 3 times
Fifty and stop low-level 6 group 2 times

CodePudding user response:

 
The select tmp1. Name, tmp1. Action, tmp1. Priority, tmp1. Groupid, tmp1. Count 
The from 
(
Select the name of the action, priority, groupid, count (*) as the count 
From the users where clock & gt; 1598198400 
Group by the name of the action, priority, groupid 
Tmp1) 
Left the join 
(
Select the name of the action, priority, min (groupid) min_groupid 
From the users where clock & gt; 1598198400 
Group by the name of the action, priority 
) tmp2 
On tmp1. Name=tmp2. The name, 
Tmp1. Action=tmp2. Action, 
Tmp1. Priority=tmp2. Priority, 
Tmp1. Groupid=tmp2. Min_groupid 

Can use temporary table method to solve

CodePudding user response:

Table: it isn't count field, if not, must be an error, put this question to your end,
Select the name of the action, priority, groupid, count (*) as the count from the users where clock & gt; 1598198400
Group by the name of the action, priority, groupid order by 5 desc

Just you said zhang SAN belong to multiple groups, only once, what in accordance with the rules, the groupid=1 or the groupid=2,