开发者

SQL Use grouped values to use in a subquery + "where column in())"

开发者 https://www.devze.com 2023-02-15 07:00 出处:网络
I have the folowing table: Case with columns > ID,ResponsibleID Action with columns > ID,CaseID,Action

I have the folowing table:

Case with columns > ID,ResponsibleID
Action with columns > ID,CaseID,Action

I want to get a list of all responsibles with their number of cases and their number of total actions over all those cases.

This could be the query if it worked, but it does not

Select 
    Case.ResponsibleID,
    CaseCount=count(*),
    --how can i tell sql that this is the CaseID sublist of the group?
    --It would be possible with a subquery, but i rather not do that for performance reasons. 
    --the real query gets the caseID list from a table 3 or 4 joins later.
    ActionCount=(select count(*) from Action wh开发者_如何学JAVAere CaseID in Case.CaseID) 
From Case
Group by ResponsibleID


SELECT  c.ResponsibleID
        , COUNT(DISTINCT c.ID)
        , COUNT(*)
FROM    Case c
        LEFT JOIN Action a ON a.CaseID = c.CaseID
GROUP BY
        c.ResponsibleID
0

精彩评论

暂无评论...
验证码 换一张
取 消