我可以设置一个条件来筛选COUNT子句吗?mysql

Question

我将单独解释与问题相关的内容，并省略任何其他不相关的细节。

现状：

我有两张桌子，硬币和用户。

coin有三个字段id, uid, fid.Coin表关系到用户(已经在系统中注册并能够邀请朋友)和他们的朋友(已经接受邀请并成为会员)-该表只存储被邀请后的成功注册-

id是唯一索引
uid用于存储用户id。
fid存储好友id(接受邀请并成为系统成员的好友)。

users有关于用户的常见信息，如id, fname, lname, email。。。etc和CCD_ 8

目标：查找发出最多邀请的获奖者。换句话说。要找到发出最多邀请的顶级用户及其受邀好友必须在2012-08-31之前注册。日期存储在表"users"列Date_create中。日期格式为yyyy-mm-dd

示例-

Table coin
id uid fid 
1  333 777   
2  444 888       
3  555 999       
4  333 123         
5  444 456         
6  333 789   
Table users
id   date_create
333  2012-07-15
444  2012-07-20
555  2012-07-25
777  2012-07-25
888  2012-07-25
999  2012-10-02   <-- I don't need this to be counted
123  2012-07-25
456  2012-07-25
789  2012-07-25

表示用户333的邀请次数最多(邀请了3个用户)->777123和789用户444邀请了2个用户，用户555只邀请了1个用户，但对于555，由于他的朋友(999)在2012-08-31 之后注册，因此将不计算在内

我想要的是用户

333 has made 3 invitations before 2012-08-31. 
444 has made 2 invitations before 2012-08-31.

到目前为止，我所做的是：[完全不确定]

SELECT
c.uid,       
u.fname,
u.lname,
u.phone,
u.email,
u.country,
COUNT(c.fid)    AS NoOfFriends
FROM coin AS c
JOIN users AS u
ON c.uid = u.id
GROUP BY c.uid
ORDER BY NoOfFriends desc

这个查询(据我所知)为用户带来了大多数邀请，无论他/她的朋友何时注册。

所以我的问题是：Q1)如何将日期条件应用到我的查询中

我想要的用户谁有最多的朋友邀请。他/她邀请的朋友必须在2012-08-31之前接受邀请并注册。在此日期之后接受的任何其他邀请都不应计算在内。

请给我提供代码示例。

Answer 1

许多答案混淆了实际用户和邀请的朋友。

SELECT
c.uid
info.fname,
info.lname,
info.phone,
info.email,
info.country,
info.date_create,
COUNT(c.fid) AS [NoOfFriends]
FROM coin AS c
JOIN users AS friend
ON c.fid = friend.id
LEFT JOIN users AS info
ON c.uid = info.id
WHERE friend.date_create < '20121031'
GROUP BY c.uid

EDIT修复了数据格式

Answer 2

您可以将SUM与CASE:结合使用

SUM(CASE WHEN <cond> THEN 1 ELSE 0 END) as NoOfFriends

完整的声明看起来像这样：

SELECT
c.uid,
u.fname,
u.lname,
u.phone,
u.email,
u.country,
u.`date_create`,
SUM(CASE WHEN f.date_create < 2012-10-31 THEN 1 ELSE 0 END)    AS NoOfFriends
FROM coin AS c
JOIN users AS u
ON c.uid = u.id
join users as f
on c.fid = f.id
GROUP BY c.uid, u.fname, u.lname, u.phone, u.email, u.country, u.`date_create`
ORDER BY NoOfFriends desc

Answer 3

count(expression)函数对该表达式的结果不为null的行进行计数。如果表达式只返回true或false，则所有行都将被计数，因为true和false都不为null。

要使用带count的表达式，如果为false，则必须使其返回null：

COUNT(friend.date_create < '2012-10-31' or null) AS NoOfFriends

Answer 4

您只按c.uid分组的方法在MySQL中有效，但您将与其他RDBMS脱离关系。

在这种情况下，我会将计数移动到子查询中，在我看来，这更容易阅读，而且它很好地将查询与您想要应用的逻辑分离开来，即获取朋友在某个日期之前注册的所有邀请，然后重新加入邀请他们的用户。

SELECT  u.ID,
u.fname,
u.lname,
u.phone,
u.email,
u.country,
u.`date_create`,
COALESCE(FriendCount, 0) AS FriendCount
COALESCE(FriendCount2, 0) AS FriendCount2
FROM    users AS u
LEFT JOIN  
(   SELECT  coin.UID,
COUNT(coind.fid) AS FriendCount,
COUNT(CASE WHEN Users.Date_Create < '20121031' THEN coind.fid END) AS FriendCount2
FROM    coin
INNER JOIN Users
ON coin.FID = users.ID
GROUP BY coin.UID
) c
ON c.uid = u.id
ORDER BY FriendCount2 DESC

如果你不关心总的朋友，只关心10月31日之前的朋友，那么你可以将其简化为

SELECT  u.ID,
u.fname,
u.lname,
u.phone,
u.email,
u.country,
u.`date_create`,
COALESCE(FriendCount, 0) AS FriendCount
FROM    users AS u
LEFT JOIN  
(   SELECT  coin.UID,
COUNT(coind.fid) AS FriendCount
FROM    coin
INNER JOIN Users
ON coin.FID = users.ID
WHERE   Users.Date_Create < '20121031'
GROUP BY coin.UID
) c
ON c.uid = u.id
ORDER BY FriendCount DESC

Answer 5

这是我的答案，感谢所有尝试并提供帮助的人，特别是LastCoder，他迅速提供了实际的解决方案。

SELECT
c.uid,
info.id,
info.fname,
info.lname,
info.phone,
info.email,
info.country,
COUNT(DISTINCT c.fid) AS NoOfFriends   -- modified this to get unique invitations
FROM coin c
JOIN users AS friends
ON c.fid = friends.id
LEFT JOIN users AS info
ON c.uid = info.id
WHERE friends.date_create < '20120831'
GROUP BY c.uid
ORDER BY NoOfFriends DESC
LIMIT 10

老实说，我没有使用其他解决方案，因为我不清楚SUM(CASE WHEN ...)。