在非聚合查询中嵌入聚合查询

Question

我正在尝试创建一个数据库视图，该视图具有对列值的聚合查询的结果(关于所使用的聚合查询，请参见this post )，例如

MESSAGEVIEW
----------------------------------------------
MESSAGEID    SENDER       PARTCOUNT        
----------------------------------------------
1            Tim          2
2            Bridgekeeper 0

我尝试过以下选择视图数据的代码：

SELECT m.MESSAGEID, m.SENDER,
    (SELECT COUNT(mp.MESSAGEID)
         FROM MESSAGE m LEFT JOIN MESSAGEPART mp
         ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID) AS PARTCOUNT
    FROM MESSAGE m;

返回ORA-01427: single-row subquery returns more than one row。

我也试过

SELECT m.MESSAGEID, m.SENDER, COUNT(mp.MESSAGEID) AS PARTCOUNT
FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID 
GROUP BY m.MESSAGEID;

返回ORA-00979: not a GROUP BY expression。

如何才能获得正确显示这些信息的视图？

Answer 1

这样就行了。

SELECT m.MESSAGEID, m.SENDER,
    (SELECT COUNT(mp.MESSAGEID)
         FROM MESSAGEPART mp
         WHERE mp.MESSAGEID = m.MESSAGEID) AS PARTCOUNT
    FROM MESSAGE m;

Answer 2

您可以使用子查询(请参阅@Jva答案)或使用以下联接来完成此操作：

SELECT m.MESSAGEID, m.SENDER,
       mp.PartCount AS PARTCOUNT

       FROM MESSAGE m
           LEFT JOIN 
             (SELECT MESSAGEID,COUNT(*) as PartCount 
                     FROM MESSAGEPART GROUP BY MESSAGEID) as mp 
             ON m.MESSAGEID=mp.MESSAGEID

Answer 3

如果在窗口函数格式中使用count()，则可以将其放入一个没有子查询的查询中：

SELECT m.MESSAGEID, m.SENDER, COUNT(mp.MESSAGEID) OVER (PARTITION BY m.MESSAGEID) AS PARTCOUNT
FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID;