时间戳

首页 > 解决方案 > 与 Group By 联合

sql - 与 Group By 联合

问题描述

看了其他问题，尝试了不同的东西，但仍然返回不止一行。

2 个表上的 Union 问题，带有 group by 子句。应该只返回一行，按 serviceID 分组。

 SELECT 
    serviceID, 
    serviceName, 
    FullCount,
    WaitingCount,
    InProgressCount
    from (

    select
    a.serviceID,
    serviceName,
    count(applicantID) FullCount,
    ISNULL(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END),0) AS WaitingCount,
    ISNULL(SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END),0) AS InProgressCount
    from Products s
    left join Assigns a on a.serviceID = s.productID 
    WHERE s.clientID = @ClientID
    group by serviceID, serviceName

    UNION

    select
    s.serviceID,
    p.serviceName,
    count(s.ApplicantID) FullCount,
    ISNULL(SUM(CASE WHEN s.status = 0 THEN 1 ELSE 0 END),0) AS WaitingCount,
    ISNULL(SUM(CASE WHEN s.status = 1 THEN 1 ELSE 0 END),0) AS InProgressCount
    from Legacies s
    Left Join Products p on s.serviceID = p.productID 
    WHERE s.client = @CompanyName
    group by serviceID, serviceName

    ) t
    GROUP BY serviceID, serviceName

我总是从每个表中返回 2 行。我需要将它们组合在一起，因此它只返回 1 行，基于 servicedID。

我试图返回的数据来自下表..

产品表

productID    serviceName  
-------------------------
    1        Gold Service
    2        Silver Service
    3        Bronze Service

分配表

ApplicantID    serviceID    status
-------------------------------------  
     1             1           0
     2             1           0
     3             1           1
     4             2           0
     5             1           1

遗产表

ApplicantID    serviceID    status
-------------------------------------  
     1             1           0
     2             1           0
     3             1           0
     4             2           0
     5             1           1

我试图得到的结果是每个 serviceID 一行，以显示 Legacies 和 Assigns 表中有多少申请人在使用此服务，例如：-

serviceID   serviceName  FullCount WaitingCount  InProgressCount
----------------------------------------------------------------
    1       Gold Service     8          5               3
    2       Silver Service   2          2               0
    3       Bronze Service   0          0               0

FullCount 是每个服务上的申请人总数，WaitingCount 是状态为“0”的服务上的申请人数，InProgressCount 是该服务上状态为“1”的申请人数

标签： sqlgroup-byunion

解决方案

根据其他信息，我认为您可以只union all使用 Legacies 和 Assigns 表。

仍然未经测试

select serviceID, servicename, count(*) fullcount
   ,sum(case when status = 0 THEN 1 ELSE 0 END) AS WaitingCount
   ,SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS InProgressCount
from (
   select ApplicantID, serviceID, status
   from Assigns
   WHERE clientID = @ClientID

   union all

   select ApplicantID, serviceID, status
   from Legacies
   WHERE clientID = @ClientID
) combined
left join Products P on P.productID = combined.serviceID
group by serviceID, servicename

以下是编辑前

很难说，因为您没有发布足够的信息（没有样本数据、没有表结构、没有预期的输出）。但我认为您可以将它们全部组合成 1 个查询：

未经测试，这在缺乏信息的情况下应该是显而易见的。

SELECT isnull(a.serviceID, L.serviceID) serviceID, p.serviceName
    ,count(*) FullCount, SUM(CASE WHEN isnull(a.status, L.status) = 0 THEN 1 ELSE 0 END) WaitingCount
    ,sum(CASE WHEN isnull(a.status, L.status) = 1 THEN 1 ELSE 0 END) InProgressCount
from Legacies L 
full outer join Assigns a on a.serviceID = L.serviceID
right outer join Products P on P.productID = isnull(a.serviceID, L.serviceID)
where (P.clientID = @ClientID
        or L.client = @CompanyName
    )
group by isnull(a.serviceID, L.serviceID), p.serviceName

推荐阅读