时间戳

首页 > 解决方案 > 在三个连接表中计数

sql - 在三个连接表中计数

问题描述

我有三张桌子。第一个是PrivteOwner5 列 ( ownerno, fname, lname, address, telno)，第二个是PropertyForRent10 列 ( propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno)，第三个是Viewing4 列 ( clientno, propertyno, viewdate, comment)。

我想找到没有看房的房产最多的业主。我的代码如下：

    SELECT 
        CONCAT (A.fname, ' ', A.lname) AS OwnerName, 
        A.ownerno, B.propertyno, B.ownerno
    FROM 
        PrivateOwner AS A 
    INNER JOIN 
        PropertyforRent AS B ON A.ownerno = B.ownerno
    LEFT JOIN
        viewing AS C

   SELECT 
        ownerno, COUNT(ownerno), viewdate
   FROM 
       Max_Property
       GROUP BY ownerno
       ORDER BY COUNT(ownerno) DESC
   WHERE
       ROWNUM = 1 and viewdate IS NULL;

此代码是否正常工作？如果是，我们如何有效地编写它？

标签： sqlsql-server

解决方案

如果您不提供数据定义、数据或您的预期结果，则很难回答问题。

无论如何，根据您的描述，我认为这可能会给您带来您想要的结果。

SELECT
    TOP 1
    PrivteOwner.ownerno,
    PrivteOwner.fname,
    PrivteOwner.lname,
    COUNT(ViewNumber) AS PropertyNumber
FROM
(
    SELECT
        PropertyForRent.propertyno AS propertyno
        , COUNT(Viewing.propertyno) AS ViewNumber
    FROM PropertyForRent
    LEFT JOIN Viewing ON Viewing.propertyno = PropertyForRent.propertyno
    GROUP BY PropertyForRent.propertyno
) AS NoView
JOIN PropertyForRent ON PropertyForRent.propertyno = NoView.propertyno
JOIN PrivteOwner ON PrivteOwner.ownerno = PropertyForRent.ownerno
WHERE ViewNumber = 0
GROUP BY PrivteOwner.ownerno,
    PrivteOwner.fname,
    PrivteOwner.lname
ORDER BY PropertyNumber DESC

推荐阅读