首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将多个计数查询合并为一个查询

问将多个计数查询合并为一个查询
EN

Stack Overflow用户
提问于 2017-08-22 17:28:03
回答 4查看 166关注 0票数 1

在SQL中,我有一组查询,它们返回具有不同条件的不同表中的计数值,如下所示

代码语言:javascript
复制
SELECT COUNT(DISTINCT GroupUserID) AS KnownUsers 
FROM Table A 
WHERE ...

SELECT COUNT(DISTINCT GroupUserID) AS UnKnownUsers 
FROM TableB  
WHERE (a different condition)

SELECT 
    COUNT(DISTINCT ManagerID) AS Managers, 
    COUNT(DISTINCT LeadID) AS TeamLeads 
FROM
    TableX  
WHERE 
    (condition)

有没有什么方法或查询可以用来将所有3个SQL查询组合成一个返回4列的查询?

代码语言:javascript
复制
  KnownUsers    UnKnownUsers   Managers    TeamLeads
EN

回答 4

Stack Overflow用户

发布于 2017-08-22 17:30:39

尝尝这个

代码语言:javascript
复制
    SELECT  (select COUNT(distinct GroupUserID) as KnownUsers from Table A WHERE ...) as KnownUsers ,   

    (SELECT  COUNT(distinct GroupUserID) as UnKnownUsers from TableB  WHERE (a   different condition)) as UnKnownUsers,

    (SELECT  COUNT(distinct ManagerID) from TableX  WHERE (condition)) as Managers, 
 (SELECT  COUNT(distinct LeadID) as TeamLeads from TableX  WHERE (condition)) as TeamLeads 
票数 3
EN

Stack Overflow用户

发布于 2017-08-22 17:33:00

是。使用子查询选择它们:

代码语言:javascript
复制
SELECT
    KnownUsers=(SELECT  COUNT(distinct GroupUserID) from Table A WHERE ...),
    UnKnownUsers=(SELECT  COUNT(distinct GroupUserID) from TableB  WHERE (a   different condition)),
    Managers=(SELECT  COUNT(distinct ManagerID) from TableX  WHERE (condition)),
    TeamLeads=(SELECT  COUNT(distinct LeadID) from TableX  WHERE (condition));
票数 2
EN

Stack Overflow用户

发布于 2017-08-22 18:06:56

代码语言:javascript
复制
select *
from 
(
    select COUNT(1) TotalRecord,'KnownUsers' as Users from TableA 
    union all
    select COUNT(1) TotalRecord,'UnKnownUsers' as Users from TableB 
    union all
    select COUNT(1) TotalRecord,'TeamLeads' as Users from TableX
    union all
    select COUNT(1) TotalRecord,'Managers' as Users from TableX
)
 UserDetails
pivot
(
  sum(TotalRecord)
  for Users in ([KnownUsers], [UnKnownUsers], [TeamLeads], [Managers])
) piv;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45813796

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档