Group by子句

Question

我有以下表格问题，IssueStatus和CustomTable(CT) .Following中的数据是相同的

Issue:                       Issuestatus:                    CT

Issue|pkey|issuestatus       id | pname                     Issue|Referred
----------------------------------------------------------------------------
100     T-1  1                  1   Open                       100     NULL
200     T-2  2                  2   Closed                     200     NULL 
300     T-3  3                  3   Acknowledged               700     Dev
400     T-4  4                  4   In Progress                800     QA
500     T-5  1                  5   Referred                   800     NULL 
600     T-6  2                                                 400     NULL
700     T-7  5                                                 500     NULL   
800     T-8  5                                                 700     NULL

我需要如下输出

pname      |  Count
Open           2
Closed         2
Acknowledged   1
In Progress    1
Dev            1 
QA             1

因此，如果您看到，需要按issuestatus分组并获取pkey的计数，但是当issuestatus是“引用”时，您需要在CT表中查找，并在group by中获取相应的引用列文本。CT中的问题链接到问题表中的问题。

这就是我所尝试的，但不是正确的输出

select pname = case 
    when pname='Referred' then CT.Referred 
    else pname end,
    COUNT(pkey) 
from  CT,issue a,issuestatus
where a.issuestatus=issuestatus.id and a.issue=CT.ISSUE 
group by pname,CT.Referred

Answer 1

我测试了下面的查询，给出了你想要的

试试看

WITH T1 AS (
  SELECT (CASE WHEN pname='Referred' THEN Referred ELSE pname END)AS [pn]
  FROM (SELECT i.Issue,c.Referred,iss.pname
        FROM Issue i
        INNER JOIN IssueStatus iss ON i.issuestatus=iss.id
        LEFT OUTER JOIN CT c ON c.Issue=i.Issue
       ) t
)


SELECT pn,count(*) AS cnt
FROM T1
WHERE pn IS NOT NULL
GROUP BY pn
ORDER BY cnt DESC

这是SQLFiddle

Answer 2

尝尝这个

 SELECT pname, COUNT(pname)
    FROM (SELECT (CASE WHEN Pname='Referred'  THEN CT.referred else st.pname END)AS pname
            FROM Issue a JOIN IssueStatus st
            ON a.issuestatus = st.id LEFT OUTER JOIN CT
            ON a.Issue = CT.Issue) tbl
    GROUP BY pname
    HAVING pname IS NOT NULL;

这是SQLFiddle

Answer 3

尝尝这个。没有测试它，因为没有测试数据的脚本。

   select case when pname='Referred' then CT.Referred else pname end pname, COUNT(*) cnt
    from  CT,issue a,issuestatus
    where a.issuestatus=issuestatus.id and a.issue=CT.ISSUE 
    group by case when pname='Referred' then CT.Referred else pname end