文章/答案/技术大牛

发布

社区首页 >问答首页 >无法在语句的select中使用子查询

问无法在语句的select中使用子查询
EN

Stack Overflow用户

提问于 2014-04-24 06:30:30

回答 1查看 46关注 0票数 0

手头的问题是无法在select语句中使用具有以下所需输出的子查询：

已注册ActivityName出席进度已取消

 - ProdA     8           3      1         2

 - ProdB    16           5      3         4

这是我当前代码的一个示例。有没有办法不使用子查询？

select  
    (select act.ActivityName
        from [dbo].[dimActivity] act
        WHERE act.Code in ('SJM-GTD-HMM-A01')
    ) ActivityName,
    (Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Attend%') 
    ) Attended,
    (Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Progress%') 
    ) Inprogess,
        (Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Cancel%') 
    ) Cancelled,
    (Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%NA%') 
    ) Registered

sql-server

sql

回答 1

Stack Overflow用户

发布于 2014-04-24 06:56:40

您对'ActivityName‘的主选择应该是主查询，每个子查询都嵌套在其中，格式如下：

SELECT act.ActivityName, {otherSubqueriesGoHere} 
FROM [dbo].[dimActivity] act 
WHERE act.Code in ('SJM-GTD-HMM-A01')

然后，在子查询中，您可以引用主结果集中的值，以便将子查询结果仅筛选为当前行的活动。

下面是一个修改过的查询示例。您需要更改每个子查询中的and act.ID = actMain.ID部分，以使用dimActivity表的主键而不是ID。我之所以使用ID，是因为我不知道您的表结构是什么。

SELECT  
    actMain.ActivityName,
    (   Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Attend%') 
        and act.ID = actMain.ID

    ) Attended,
    (   Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Progress%') 
        and act.ID = actMain.ID
    ) Inprogess,
    (   Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%Cancel%') 
        and act.ID = actMain.ID
    ) Cancelled,
    (   Select count( emp.ID) 
        FROM [dbo].[factAttempt] fact
        INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
        INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
        INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
        INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
        INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

        WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
        and att.name like ('%NA%') 
        and act.ID = actMain.ID
    ) Registered
FROM [dbo].[dimActivity] actMain
WHERE actMain.Code in ('SJM-GTD-HMM-A01')

编辑：我刚刚注意到，您的每个子查询基本上都是重复的，只有att.name的比较在变化，所以您也可以使用这样的查询，它包含一个group by和att.name来计算每个sum值出现的次数。

SELECT act.ActivityName
    , SUM(case when att.name like ('%Attend%') then 1 else 0 end) as Attended
    , SUM(case when att.name like ('%Progress%') then 1 else 0 end) as Inprogess
    , SUM(case when att.name like ('%Cancel%') then 1 else 0 end) as Cancelled
    , SUM(case when att.name like ('%NA%') then 1 else 0 end) as Registered
FROM [dbo].[factAttempt] fact
    INNER JOIN [dbo].[dimActivity] act ON act.ID = fact.ActivityID
    INNER JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
    INNER JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
    INNER JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
    INNER JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID
WHERE (act.Code in ('SJM-GTD-HMM-A01','SJM-GTD-HMM-MAN01','SJM-GTD-HMM-PRT01','SJM-GTD-HMM-LAS01'))
GROUP BY act.ActivityName

注意:此查询将不包括所有4个存储桶(已参加、正在进行、已取消、已注册)计数为0的ActivityName。如果您需要包含全为0的记录，则可以将连接更改为LEFT JOIN，如下所示：

FROM [dbo].[dimActivity] act
    LEFT JOIN [dbo].[factAttempt] fact ON act.ID = fact.ActivityID
    LEFT JOIN [dbo].[dimUser] emp ON emp.ID = fact.UserID
    LEFT JOIN [dbo].[dimDate] dt ON fact.EndDtID = dt.DateID
    LEFT JOIN [dbo].[dimCompletionStatus] comp ON fact.CompletionStatusID = comp.ID
    LEFT JOIN [dbo].[dimAttendanceStatus] att ON fact.AttendanceStatusID = att.ID

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/23256415

复制

相似问题

问无法在语句的select中使用子查询
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法在语句的select中使用子查询EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法在语句的select中使用子查询
EN