表与自身的连接有什么问题？

发布于 2024-10-07 15:18:07 字数 1780 浏览 6 评论 0原文

我有一个名为 TempAllAddresses 的表，其中包含以下列 - ID、Address、State。我想用 Address、State 和 Count 填充新表。 Count 应表示 TempAllAddresses 表中有多少条记录具有类似此地址的地址，后跟通配符。如果这没有意义，这里有一个例子来说明 - 假设我有这样的记录：

ID      Address      State
12345   13 Phoenix   NY

我想要做的是将一条新记录插入到名为 AddressCount 的新表中，该表的 Address 为 13 Phoenix，Address 为 NY State，以及表中以 NY 作为 State 且地址为“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 个记录也有纽约州，然后有地址“13 Phoenix Road”和“13 Phoenix Rd”，那么我想进入我的最终记录表这样的记录：

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

，我想获取表中具有相同状态的记录数以及以此地址开头的地址（或等于...我）确实希望将此地址包含在计数中）。

原文

I have a table called TempAllAddresses with the following columns - ID, Address, State. I want to populate a new table with Address, State, and Count. Count should represent how many records there are in the TempAllAddresses table that have an address like this address followed by a wildcard. If that made no sense, here's an example to illustrate -
Let's say I have a record like this:

ID      Address      State
12345   13 Phoenix   NY

What I want to do is insert a new record into a new table called AddressCount that has 13 Phoenix for the Address, NY for the State, and the number of records in the table that have NY as the State and an address LIKE '13 Phoenix%' for the Count.

I want to accomplish this with an inner join of TempAllAddresses on itself. This is what I've tried, but it doesn't seem to accomplish what I'm looking for:

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

The Count is definitely off, though. It should be equivalent to running "SELECT COUNT(*) FROM TempAllAddresses WHERE State=thisRecordsState and Address LIKE thisRecordsAddress + '%'". How can I accomplish this? What am I doing wrong?

Edit:

The count seems to be off in the following way -
If I have a record like I mentioned above, and then I have 2 other records that also have a state of NY, and then have addresses of "13 Phoenix Road" and "13 Phoenix Rd", then I want to get in my final table a record like this:

13 Phoenix    NY    3

Instead, I seem to be getting:

13 Phoenix    NY    9

I'm not quite sure what's happening here... some sort of cartesian product? Permutations...? Can anyone explain this?

Edit 2:
A further edit since I seem to be misunderstood (and really need a solution :( )... Here is a query with a correlated subselect that accomplishes what I'm looking for. I'd like to do the same thing with an inner join of the table on itself rather than a subselect.

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

Basically, for each record, I want to get the number of records in the table that have the same state and an address that begins with this address (or is equal to... I do want to include this address as part of the count).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

丘比特射中我 2024-10-14 15:18:07

这里有两种解决方案，一种使用 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]

Here's two solutions, one using a CROSS APPLY and the other using an INNER JOIN like you wanted originally. I hope this helps. :)

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]

回复收藏 0 原文

注定孤独终老 2024-10-14 15:18:07

试试这个：

SELECT Orig_Address, State, COUNT(Similar_Address)
From
(
  SELECT t1.Address Orig_Address, 
         t1.State   State, 
         t2.address Similar_Address
    FROM TempAllAddresses t1
   INNER JOIN TempAllAddresses t2
      ON t1.state = t2.state
     AND T2.Address LIKE t1.address + '%'
     AND t1.address <> t2.address
)
GROUP BY State, Orig_Address

编辑：忘记包含 t1.address 和 t2.address 之间的差异，正如 @Spiny Norman 所说，因为您可能不想将地址与其自身进行比较。

华泰

Try this instead:

SELECT Orig_Address, State, COUNT(Similar_Address)
From
(
  SELECT t1.Address Orig_Address, 
         t1.State   State, 
         t2.address Similar_Address
    FROM TempAllAddresses t1
   INNER JOIN TempAllAddresses t2
      ON t1.state = t2.state
     AND T2.Address LIKE t1.address + '%'
     AND t1.address <> t2.address
)
GROUP BY State, Orig_Address

EDIT: forgot to include the difference between t1.address and t2.address, as @Spiny Norman said, since you probably do not want to compare an address to itself.

HTH

回复收藏 0 原文

三月梨花 2024-10-14 15:18:07

编辑：[剪掉旧的东西]

试试这个：

SELECT t1.Address, t1.State, COUNT(distinct t2.id) 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

EDIT: [snip old stuff]

Try this:

SELECT t1.Address, t1.State, COUNT(distinct t2.id) 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

回复收藏 0 原文

唠甜嗑 2024-10-14 15:18:07

QUERY A:

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

不等于

QUERY B:

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

因为 B 为原始表 (TempAllAddresses) 中的每一行生成 1 行，而 A 会将原始表中具有相同状态和地址的行分组在一起。要解决此问题，请改为 GROUP BY t1.ID、t1.State、t1.Address。

QUERY A:

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

is not equivalent to

QUERY B:

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

because B produces 1 row for each row in the original table (TempAllAddresses), whereas A will group together rows in the original table that have the same state and address. To solve this, GROUP BY t1.ID, t1.State, t1.Address instead.

回复收藏 0 原文

兔姬 2024-10-14 15:18:07

当多行具有完全相同的地址时，就会发生重复计数。

尝试：

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

There is double counting going on when there are multiple rows with exactly the same address.

Try:

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

回复收藏 0 原文

把梦留给海 2024-10-14 15:18:07

Nested GroupBy：

子查询将为每个不同的地址找到最短的地址。
这不考虑区分大小写。
然后对这些地址的每个版本进行计数。

SQL：

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

Nested GroupBy:

The subquery will find the shortest address for each distinct address.
This doesn't consider case sensitivity.
Then each version of these addresses are counted.

SQL:

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

回复收藏 0 原文

生生不灭 2024-10-14 15:18:07

您是否尝试过分析函数 - 它们通常是最简单的解决方案。我不熟悉你的表结构，但它应该是这样的：

SELECT t1.Address, t1.State, 
COUNT(t2.address) OVER (PARTITION BY t2.state) 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

你甚至可以在 OVER 子句中添加 ORDER BY 。请参阅 Oracle 常见问题解答了解一些说明。

Have you tried analytical functions - they are often the easiest solution. I am not familiar with your table structure, but it should be something like this:

SELECT t1.Address, t1.State, 
COUNT(t2.address) OVER (PARTITION BY t2.state) 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

You can even add ORDER BY in the OVER clause. See Oracle FAQs for some explanation.

回复收藏 0 原文

~没有更多了~