php mysql按行数降序排序 [英] php mysql sort by number of rows descending

问题描述

你能帮我吗

$sql="select * from table1 where id='1,2,3,4'";
{
 $sql2="select distinct column1 from table2 where column2='".$row['id']."' and left(date,10) BETWEEN '".$date_from."' AND '".$date_to."'";
 }

我需要按 id

推荐答案

如果我对您的理解是正确的，则您希望$sql中的每一行中的$sql2找到的行数对$sql中的结果进行排序.这种连接应该做到这一点，它将table2和按计数顺序(降序)连接起来.

If I understand you correctly, you want the results from $sql ordered by number of rows found by $sql2 for each row in $sql. This join should do that, it joins table2 and orders by the count, descending.

SELECT t1.id
FROM table1 t1
LEFT JOIN table2 t2
  ON t1.id=t2.column2
WHERE id IN (1,2,3,4)                     -- should it really be = '1,2,3,4'?
  AND LEFT(date,10) BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY t1.id
ORDER BY COUNT(DISTINCT t2.column1) DESC

要测试的SQLfiddle .

这篇关于php mysql按行数降序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！