前端之家

  1. 首页
  2. 问答

如何从JOIN获得DISTINCT结果

• 问答

如何获得最近十位访问者的不同结果，以及他们访问了多少次？

此：

SELECT DISTINCT all_ref  FROM site_stats WHERE all_ref!='' ORDER BY id DESC LIMIT 10";

返回最近的十位访问者

all_ref
Chicago,IL     
Chesapeake,VA  
Austin,TX  
San Jose,CA    
Houston,TX 
Newport News,VA    
Sebastian,FL   
Dublin,IE  
Menlo Park,CA
Waves,NC

这将返回所有访客的计数：

SELECT all_ref,COUNT(*) AS ct FROM site_stats WHERE all_ref!='' 
AND all_ref!=',' GROUP BY all_ref ORDER BY ct DESC,all_ref

这是我想让最近的10位访问者访问过多少次：

SELECT x.all_ref,x.ct
FROM (SELECT all_ref,COUNT(*) AS ct FROM site_stats WHERE all_ref!='' GROUP BY all_ref )
AS x LEFT JOIN site_stats AS f
ON f.all_ref=x.all_ref
ORDER BY f.id DESC LIMIT 10

它返回此值（返回的all_ref和ct值正确）：

all_ref     ct  
Chicago,IL 26  
Chicago,IL 26  
Chesapeake,VA  18  
Chesapeake,VA  18  
Austin,TX  2   
San Jose,CA    3   
Houston,TX 1   
Chicago,IL 26

但应返回类似以下的内容：

all_ref         ct  
Chicago,IL     26    
Chesapeake,TX      2   
San Jose,TX     1      
Chicago,IL     26
Pittsburgh,PA  11
Richmond,VA    52
Waves,NC       24
Grandy,NC      9

让DISTINCT x.all_ref不能解决问题。

更新： 适用于我的解决方案：

SELECT x.all_ref,MAX(id) AS id,COUNT(*) AS ct 
FROM site_stats 
WHERE all_ref!='' GROUP BY all_ref )
AS x LEFT JOIN site_stats AS f
ON f.all_ref=x.all_ref
GROUP BY x.all_ref
ORDER BY x.id DESC LIMIT 10;
collinmao 回答：如何从JOIN获得DISTINCT结果

您可能首先找到与众不同的内容，然后加入表格-

SELECT f.id,x.all_ref,x.ct
FROM (SELECT all_ref,COUNT(*) AS ct
      FROM site_stats
      WHERE all_ref!=''
      GROUP BY all_ref ) AS x
LEFT JOIN (SELECT DISTINCT id,all_ref
           FROM site_stats) AS f ON f.all_ref=x.all_ref
ORDER BY f.id DESC LIMIT 10

仅告诉您，这是COUNT窗口功能的实现。如果您的MySQL版本支持Window功能，则可以简单地使用-

SELECT DISTINCT id,all_ref,COUNT() OVER() AS ct
FROM site_stats
WHERE all_ref!=''
ORDER BY id DESC LIMIT 10
,

最适合我的解决方案：

FROM (SELECT all_ref,MAX(id) AS id,COUNT(*) AS ct 
FROM site_stats 
WHERE all_ref!='' GROUP BY all_ref )
AS x LEFT JOIN site_stats AS f
ON f.all_ref=x.all_ref
GROUP BY x.all_ref
ORDER BY x.id DESC LIMIT 10;
distinctjoinmysql
本文链接：https://www.f2er.com/3151346.html

大家都在问