mysql:将子查询添加到现有查询

Question

我是sql新手，在实习期间我被要求了解一点sql，所以我开始学习并被允许在测试环境上练习(不更新，插入或删除，只是显示)，同时练习我有这个问题，这是我现有的查询:

Select 
ab.USER_ID,
user.email AS email, 
concat(user.firstName, ' ' ,user.lastName ) AS name, 
usl.loginDateTime AS LastLogin,
ab.creation_time AS DateJoined,
udl.devicePlatform AS Devices,
user.status AS Status
FROM F_USR_USER AS user

INNER JOIN F_USR_ACCOUNT_BASE AS ab 
on user.ID= ab.USER_ID

INNER JOIN F_USR_DSL_LOG AS udl
ON ab.USER_ID=udl.USER_ID

INNER JOIN F_USR_SESSION_LOG AS usl
ON ab.USER_ID=usl.USER_ID
group by ab.USER_ID

我想将此选择插入到此查询中。我尝试使用 union all 和内部联接中的选择，但它不起作用(可能结构错误)

这是我需要添加的查询。此查询显示每个用户下载的文档数，其中 User_id 作为我在此查询中使用的所有表的外键。

Select ab.user_id,count(*) as total
 from F_USR_ACCOUNT_BASE AS ab ,F_USR_DOCUMENT AS doc
 where ab.USER_ID= doc.USER_ID and doc.extractionComplete = '1'
 group by ab.user_ID;

我用什么来正确获取它？

Answer 1

有多种方法可以做到这一点。一种方法是将其添加到 select 子句中:

Select 
ab.USER_ID,
user.email AS email, 
concat(user.firstName, ' ' ,user.lastName ) AS name, 
usl.loginDateTime AS LastLogin,
ab.creation_time AS DateJoined,
udl.devicePlatform AS Devices,
user.status AS Status,
(
    Select  count(*) as total
    from    F_USR_DOCUMENT AS doc
    where   ab.USER_ID= doc.USER_ID 
    and     doc.extractionComplete = '1'
) as totalDocs
From
F_USR_USER AS user
INNER JOIN 
F_USR_ACCOUNT_BASE AS ab on 
    user.ID= ab.USER_ID
INNER JOIN 
F_USR_DSL_LOG AS udl ON 
    ab.USER_ID=udl.USER_ID
INNER JOIN 
F_USR_SESSION_LOG AS usl ON 
    ab.USER_ID=usl.USER_ID
group by ab.USER_ID /* Maybe take this GROUP BY out.  WHERE clause to filter. */