sql - 将表与自身连接有什么问题？

Question

我有一张 table 叫 TempAllAddresses与以下列 - ID , Address , State .我想用 Address 填充一个新表, State , 和 Count . Count应该表示 TempAllAddresses 表中有多少记录具有像此地址这样的地址，后跟通配符。如果这没有意义，这里有一个例子来说明——
假设我有这样的记录:

ID      Address      State
12345   13 Phoenix   NY

我想要做的是在名为 AddressCount 的新表中插入一条新记录。有 13 凤凰为 Address , NY 为 State ，以及表中以 NY 作为州和地址 LIKE '13 Phoenix%' 的记录数，用于 Count .

我想通过 TempAllAddresses 自身的内部连接来实现这一点。这是我尝试过的，但它似乎并没有完成我正在寻找的东西:

SELECT t1.Address, t1.State, COUNT(t2.address) As NumEntities
FROM TempAllAddresses t1
INNER JOIN TempAllAddresses t2
 ON t1.state = t2.state
 AND T2.Address LIKE t1.address + '%'
GROUP BY t1.State, t1.Address

不过，伯爵肯定不在。它应该相当于运行“ SELECT COUNT(*) FROM TempAllAddresses WHERE State=thisRecordsState and Address LIKE thisRecordsAddress + '%'”。我怎样才能做到这一点？我究竟做错了什么？

编辑:

计数似乎以下列方式关闭 -
如果我有一个像上面提到的记录，然后我有 2 个其他记录也有 NY 州，然后有地址“凤凰路 13 号”和“凤凰路 13 号”，那么我想进入我的决赛表这样的记录:

13 Phoenix    NY    3

相反，我似乎得到:

13 Phoenix    NY    9

我不太确定这里发生了什么……某种笛卡尔积？排列……？谁能解释一下？

编辑 2:
进一步编辑，因为我似乎被误解了(并且确实需要一个解决方案 :( )......这是一个带有相关子选择的查询，它完成了我正在寻找的东西。我想用内部做同样的事情连接表本身而不是子选择。

SELECT Address, State, 
    (SELECT Count(*)
    FROM TempAllAddresses innerQry 
    WHERE innerQry.address LIKE outerQry.address + '%' 
        AND innerQry.state = outerQry.state) As NumEntities
FROM TempAllAddresses outerQry

基本上，对于每条记录，我想获取表中具有相同状态的记录数以及以该地址开头的地址(或等于...我确实希望将此地址作为计数的一部分)。

Answer 1

这是两种解决方案，一种使用 CROSS APPLY，另一种使用您最初想要的 INNER JOIN。我希望这有帮助。 :)

DECLARE @TempAllAddresses TABLE
(
    ID INT PRIMARY KEY IDENTITY(1, 1) NOT NULL
    , [Address] VARCHAR(250) NOT NULL
    , [State] CHAR(2) NOT NULL
)

INSERT INTO @TempAllAddresses
VALUES ('13 Phoenix', 'NY')
        , ('13 Phoenix St', 'NY')
        , ('13 Phoenix Street', 'NY')
        , ('1845 Test', 'TN')
        , ('1337 Street', 'WA')
        , ('1845 T', 'TN')

SELECT
    TempAddresses.ID
    , TempAddresses.[Address]
    , TempAddresses.[State]
    , TempAddressesCounted.AddressCount
FROM @TempAllAddresses TempAddresses
CROSS APPLY
(
    SELECT
        COUNT(*) AS AddressCount
    FROM @TempAllAddresses TempAddressesApply
    WHERE TempAddressesApply.[Address] LIKE (TempAddresses.[Address] + '%')
        AND TempAddressesApply.[State] = TempAddresses.[State]
) TempAddressesCounted

SELECT
    TempAddresses.ID
    , TempAddresses.[Address]
    , TempAddresses.[State]
    , COUNT(*) AS AddressCount
FROM @TempAllAddresses TempAddresses
INNER JOIN @TempAllAddresses TempAddressesJoin
    ON TempAddressesJoin.[Address] LIKE (TempAddresses.[Address] + '%')
            AND TempAddressesJoin.[State] = TempAddresses.[State]
GROUP BY TempAddresses.ID
    , TempAddresses.[Address]
    , TempAddresses.[State]