PHP / MYSQL语句因此选择Max Sum of Join

问题描述：

I have Problems with a select statement, as a little help here are the important columns:

Table1            
ID NAME           

TABLE 2  
ID U_ID COUNTER

The ID of Table 1 Matches the U_ID of Table 2. Table 2 contains many entries for the same u_id.

What I want to do is to get the Name of the "user" (table 1) who has in sum the max. counter.

What I got since now is the join of the tables (Where clause depends on other rows which are not important for the problem).

Can anyone help me on this issue?

我遇到了一个select语句的问题，这里有一些重要的帮助： p>

  Table1 
ID NAME 
 
TABLE 2 
ID U_ID COUNTER 
  code>  pre> 
 
 
表1的ID与表2的U_ID匹配。表2包含相同u_id的许多条目。 p> 
 
 我想要做的是获取“用户”的名称 （表1）总和最多 计数器。 p> 
 
 
我从现在开始的是表的连接（Where子句取决于对问题不重要的其他行）。 p> 
 
 
有人可以帮我解决这个问题吗？ p> 
  div>

答

SELECT name,
      SUM(counter) as counter
FROM table1
JOIN table2
ON table1.id = table2.u_id
GROUP BY u_id
ORDER BY counter DESC 
LIMIT 1

答

You can try this:

SELECT name, SUM(counter) as total_counter
FROM table1
JOIN table2
ON table1.id = table2.u_id
GROUP BY u_id
ORDER BY total_counter DESC 
LIMIT 1

Working Demo: http://sqlfiddle.com/#!2/45419/4

答

So what you need is an aggregate of an aggregate (max of sum of column). The easiest will be to create a view providing the sum and u_id end then select the max of it:

create view table2sums
as
select u_id, sum(counter) as total
  from table2
 group by u_id;

and then

select t1.name
  from table1 t1, table2sums t2
 where t1.id = t2.u_id
   and t2.total >= all (
           select total
             from table2sums
       )

In this special case you can also do it directly:

select t1.name
  from table1 t1, table2 t2
 where t1.id = t2.u_id
 group by t1.name
having sum(t2.counter) >= all (
           select sum(counter)
             from table2
            group by t2.u_id
        )

NOTE: The other proposed solutions will show a better performance. My solution only selects the name (which is what you said you wanted) and works in any RDBMS. There exist RDBMS without the LIMIT possibility. In the end, I'd say: regard my solution as educational, the others as practical

PHP / MYSQL语句因此选择Max Sum of Join

相关推荐