Question

我有以下表格:

users
+----------+----------+----------+
| id       | name     | dob      |
+----------+----------+----------+

authentications
+----------+----------+----------+ 
| id       | user_id  | provider |
+----------+----------+----------+

用户, 拥有多个认证。 user_ id 表格中的 user_ id 是 uses 表的外来密钥。

试图调和以下输出 :

+---------------+---------------+---------------+---------------+ 
|    user.id    |   user.name   |   user.dob    |       C       |
+---------------+---------------+---------------+---------------+

这里, C是, 用户认证的计数, 提供商是facebook 的用户认证。如果不是计数, 我可以得到一个布林值, 如果认证存在的话, 即是真实的, 如果不存在, 假的, 那会更好。

我无法理解如何构建查询。任何提示都会非常感激。

Answer 1

1) 这将返回每个用户的 FB 类型授权的实际数 :

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

Select Name UserName, ID UserID, COUNT(FBAuth) FBAuthCount
from Users LEFT JOIN
(Select UserID, COUNT(AuthType) FBAuth from Auth 
Where AuthType =  FB 
Group by USERID) Auths on (Users.ID = Auths.UserId)
Group by ID, Name

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

依据授权类型, 每位用户将返回真实或虚假的国旗 :

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

Select Name UserName, ID UserID, 
CASE WHEN COUNT(FBAuth) > 0 THEN  TRUE  ELSE  FALSE  END FBAuthFLAG
from Users LEFT JOIN 
(Select UserID, COUNT(AuthType) FBAuth from Auth 
Where AuthType =  FB 
Group by USERID) Auths on (Users.ID = Auths.UserId)
Group by ID, Name

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

Using a join on smaller dataset: faster response. Finally, I believe this is the one:

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

SELECT users.id,
       users.email,
       users.name,
       users.sid,
       users.created_at,
       ( CASE
           WHEN fb_auth > 0
           THEN  true 
           ELSE  false 
         END ) AS facebook
FROM   users
       LEFT JOIN (SELECT authentications.user_id,
                         Count(authentications.provider) fb_auth
                  FROM   authentications
                  WHERE  provider =  facebook 
                  GROUP  BY user_id) auths
              ON ( users.id = auths.user_id )
GROUP  BY users.id,
          users.email,
          users.name,
          users.sid,
          users.created_at

`` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ``` ``` ` `` ` `` ``` ` `` ` `` ``` ` `` ``` ``` ` `` ` `` ``` ` `` `

Answer 2

在用户和认证之间以 JOIN 创建查询, 并在用户编号字段中先添加到 Group By 上。您的组需要包含您想要在结果中列出的随后字段, 尽管它们不会改变结果的效果 :

SELECT u.id, u.name, u.dob, COUNT(a.user_id) as authCount
FROM users u 
LEFT JOIN authentications a on u.id = a.user_id
WHERE a.provider =  facebook 
GROUP BY u.id, u.name, u.dob

Answer 3

C 是计数, 如果有Facebook提供者, B 将返回 1 。如果没有, 则返回 0 (bolean) 。更新后将使用 LEFT Outer JOIN, 以便包括所有用户。

SELECT 
   users.id,
   users.name,
   users.DOB,
   SUM(CASE WHEN authentications.provider =  facebook  then 1 else 0 end) as C,
   MAX(CASE WHEN authentications.provider =  facebook  then 1 else 0 end) as B
FROM users
LEFT OUTER JOIN authentications
   ON users.id = authentications.user_id
GROUP BY users.id, users.name, users.DOB

Answer 4

先前的答案有效, 但非常慢。查询用15 - 20 分钟运行。由朋友推荐的这一个运行速度要快得多( 同一数据集的运行速度~ 4 秒 ) 。

SELECT users.id,
       users.email,
       users.name,
       users.sid,
       users.created_at,
       ( CASE
           WHEN fb_auth > 0
           THEN  true 
           ELSE  false 
         END ) AS facebook
FROM   users
       LEFT JOIN (SELECT authentications.user_id,
                         Count(authentications.provider) fb_auth
                  FROM   authentications
                  WHERE  provider =  facebook 
                  GROUP  BY user_id) auths
              ON ( users.id = auths.user_id )
GROUP  BY users.id,
          users.email,
          users.name,
          users.sid,
          users.created_at;

友情链接