将SQL查询链接在一起

Question

我正在尝试获取我的子查询的最大值，称为num_of_cust列，并与TGE主查询中的艺术家名称一起显示，以便显示这样的显示：

Artist_name |num_of_cust

Select A.Lastname ||''|| A.FirstName as Artist_Name from dhey.ARTIST A 
LEFT JOIN
(SELECT * FROM(
SELECT COUNT(*) As Num_of_Customer From Dhey.CUSTOMER_ARTIST_INT CAI
GROUP BY ARTISTID
ORDER BY COUNT(*) desc) WHERE ROWNUM = 1) B on A.ArtistID = B.ArtistID;

但是，sql正在扔我b"。

任何人都可以建议吗？

谢谢！

Answer 1

我不太了解您想做什么，但我认为这是您想要每个艺术家的客户数量

Select  A.Lastname ||''|| A.FirstName as Artist_Name 
        COUNT(*) as nb_cust
from dhey.ARTIST A 
LEFT outer JOIN Dhey.CUSTOMER_ARTIST_INT B on A.ArtistID = B.ArtistID
group by A.ArtistID, A.Lastname ||''|| A.FirstName
order by nb_cust desc

，或者您希望总体上的最大客户数量并将其链接到您的艺术家……这是一条杂交产品。

Select  A.Lastname ||''|| A.FirstName as Artist_Name 
        nb_cust_max
from dhey.ARTIST A 
cross join  (   select max(nb_cust) as nb_cust_max
                from    (   select count(*) as nb_cust 
                            From Dhey.CUSTOMER_ARTIST_INT 
                            group by ARTISTID
                        )
            )

我关闭了吗？还是完全错？

否则，如 Matbailie 所建议的，您可以添加缺失的字段

Select A.Lastname ||''|| A.FirstName as Artist_Name from dhey.ARTIST A 
LEFT JOIN
(SELECT * FROM(
SELECT ARTISTID, COUNT(*) As Num_of_Customer From Dhey.CUSTOMER_ARTIST_INT CAI
GROUP BY ARTISTID
ORDER BY COUNT(*) desc) WHERE ROWNUM = 1) B on A.ArtistID = B.ArtistID;

Answer 2

我已经解决了我的答案

SELECT A.lastname ||''||A.firstname as Artist_Name, COUNT(CAI.ARTISTID) AS NumberOfCustomers
FROM dhey.CUSTOMER_ARTIST_INT CAI
LEFT JOIN dhey.CUSTOMER C
ON CAI.CUSTOMERID = C.CUSTOMERID
LEFT JOIN dhey.ARTIST A
ON CAI.ARTISTID = A.ARTISTID
group by  A.lastname,  A.firstname
having count(*) = (select max(count(ArtistID))
                     from dhey.CUSTOMER_ARTIST_INT
                    group by ArtistID);

谢谢所有帮助！