php - 如何调整这个查询？

Question

这是我的查询

SELECT DISTINCT a.id,a.venue_id,a.user_id,mm.profilenam,mm.photo_thumb 
  FROM announce_arrival AS a,members as mm 
 WHERE a.user_id = mm.mem_id AND a.venue_id=mm.mem_id

我有 venue_id 和 user_id，我想将这两个 ID 映射到成员表 mem_id，这将返回我的 profilename 和 photo_thumb。两个 ID 将不同，并且它们必须不同。

---

我喜欢这个答案:

SELECT DISTINCT a.id
               , a.venue_id
               , a.user_id
               , m1.profilenam
               , m2.photo_thumb
 FROM announce_arrival AS a
      , members as m1
      , members as m2  
 WHERE a.user_id = m1.mem_id  
 AND   a.venue_id=m2.mem_id

但我不希望 a.id 在那里，我只想要 venue_id 和 user_id 以及我得到的结果我想要每一行。

 SELECT DISTINCT COUNT(*) 
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
 GROUP BY a.venue_id, a.user_id  ORDER BY date DESC,time DESC;

如何在上述查询中使用计数？

Answer 1

如果您想查看不同的场所和用户，请尝试此查询。

 SELECT 
   count(*) as rowcount
  , a.venue_id                
  , a.user_id                
  , m1.profilenam                
  , m2.photo_thumb  
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
 GROUP BY a.venue_id, a.user_id

如果 members.mem_id 是主键(或唯一字段)，则字段 profilenam 和 photo_thumb 是依赖于这些字段，group by 可以安全使用。

注意
如果您没有得到预期的结果，您可能需要将一个或两个 inner join 替换为 left join。

关于不同
Distinct 和 group by 非常相似，但工作方式不同。

有关这方面的更多信息，请参阅:http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

对您的数据库设计的批评
Members.mem_id 链接到 announce_arrival.user_id 和 announce_arrival.venue_id。这看起来很可疑，因为如果 user_id 恰好与某个 venue_id 相同，则无法判断 mem_id 是否链接到 user_id #10 或 venue_id #10 或两者。
我建议您将 mem_id 分成两个字段:

members.venue_id -> links to announce_arrival.venue_id; is null if no link
members.user_id -> links to announce_arrival.user_id; is null if no link.

如果您这样做，您的查询将变为:

 SELECT 
   count(*) as rowcount
  , count(a.venue_id) as venue_count
  , count(a.user_id) as user_count
  , a.venue_id                
  , a.user_id                
  , m1.profilenam                
  , m2.photo_thumb  
 FROM announce_arrival AS a       
 LEFT JOIN members as m1 ON (a.user_id = m1.user_id)
 LEFT JOIN members as m2 ON (a.venue_id = m2.venue_id)
 GROUP BY a.venue_id, a.user_id

请注意此处需要使用左联接，因为您希望显示匹配第一个联接、第二个联接或两者的行。
如果您使用 inner join，您将只会获得两个连接都产生匹配的行，这只会给出非常有限的行子集。

计数
count(*) 对推荐的所有行进行计数，因为它是可能的最快计数；它在计数中包含空行。
count(somefield) 计算 somefield 不为空的所有行。