过滤 LEFT JOIN 结果

发布于 2024-08-02 01:45:08 字数 720 浏览 5 评论 0原文

我有两个表：授权表和结算表。 “结算”包含对授权的外键引用。

和解也可以有状态（错误、已接受等）。

鉴于此数据：

Authorizations           Settlements
id                id   |   auth_id   | status
-----             ---------------------------
1                  1         1          ERROR
2                  2         1          ACCEPTED

我正在尝试编写一个 SQL 查询来查找所有没有 ACCEPTED 结算记录的授权。我尝试过 LEFT OUTER JOIN，但它返回太多行。例如：

SELECT * FROM 授权 a LEFT OUTER JOIN 结算 s ON a.id = s.auth_id WHERE s.status is null OR s.status != 'ACCEPTED'

这样做的问题是，如果它有多个结算记录，并且其中一个是 ACCEPTED，它仍然会返回授权记录。或者，如果ERROR记录多于1条，则授权会返回两次。

如何只获取没有对应状态为“已接受”的结算记录的单个授权记录？是否可以直接使用 SQL，或者我必须在代码中过滤结果？

原文

I have two tables: authorizations and settlements. 'Settlements' contains a forign key reference to authorizations.

A settlement can also have a status (ERROR, ACCEPTED, etc).

Given this data:

Authorizations           Settlements
id                id   |   auth_id   | status
-----             ---------------------------
1                  1         1          ERROR
2                  2         1          ACCEPTED

I'm trying to write a SQL query to find all authorizations that don't have an ACCEPTED settlement record. I've tried a LEFT OUTER JOIN, but it returns too many rows. For example:

SELECT * FROM authorizations a LEFT OUTER JOIN settlements s ON a.id = s.auth_id WHERE s.status is null OR s.status != 'ACCEPTED'

The problem with this is that it will still return an authorization record if it has more than one settlement record, and one of those is ACCEPTED. Or, if there is more than one ERROR record, the authorization will be returned twice.

How can I only fetch single authorization records that don't have a corresponding settlement record with a status of "ACCEPTED"? Is it possible with straight SQL, or will I have to filter the results in my code?

分享到QQ

分享到微博

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

发布评论

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

愛上了 2024-08-09 01:45:08

SELECT  *
FROM    authorizations a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    Settlements s
        WHERE   s.auth_id = a.id
                AND s.status = 'ACCEPTED'
        )

SELECT  *
FROM    authorizations a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    Settlements s
        WHERE   s.auth_id = a.id
                AND s.status = 'ACCEPTED'
        )

回复收藏 0 原文

与风相奔跑 2024-08-09 01:45:08

尝试

SELECT a.* FROM authorizations a
LEFT OUTER JOIN (SELECT S.* from settlements s1 
WHERE  s1.status = 'ACCEPTED')
ON a.id = s.auth_id
WHERE s.auth_id is null

此操作会挑选出所有已接受的记录，然后获取不属于该组的授权。

Try

SELECT a.* FROM authorizations a
LEFT OUTER JOIN (SELECT S.* from settlements s1 
WHERE  s1.status = 'ACCEPTED')
ON a.id = s.auth_id
WHERE s.auth_id is null

This picks out all the records which are accepted and then takes the authorizations which are not inthat group.

回复收藏 0 原文

梦回旧景 2024-08-09 01:45:08

根据您的示例，如果您将 JOIN 更改为 RIGHT join，则无需检查 s.status 是否为 null。

将与 SQL Server 2005+ 或 Oracle 9i+ 配合使用：

WITH unacceptedSettlements AS (
     SELECT s.auth_id
       FROM SETTLEMENTS s
      WHERE s.status != 'ACCEPTED'
   GROUP BY s.auth_id)
SELECT t.*
  FROM AUTHORIZATIONS t
  JOIN unacceptedSettlements us ON us.auth_id = t.auth_id

任何数据库替代方案：

SELECT t.*
  FROM AUTHORIZATIONS t
  JOIN (SELECT s.auth_id 
         FROM SETTLEMENTS s
         WHERE s.status != 'ACCEPTED'
      GROUP BY s.auth_id) us ON us.auth_id = t.auth_id

Based on your example, checking for the s.status being null is unnecessary if you change the JOIN to be a RIGHT join.

Will work with SQL Server 2005+ or Oracle 9i+:

WITH unacceptedSettlements AS (
     SELECT s.auth_id
       FROM SETTLEMENTS s
      WHERE s.status != 'ACCEPTED'
   GROUP BY s.auth_id)
SELECT t.*
  FROM AUTHORIZATIONS t
  JOIN unacceptedSettlements us ON us.auth_id = t.auth_id

Any database alternative:

SELECT t.*
  FROM AUTHORIZATIONS t
  JOIN (SELECT s.auth_id 
         FROM SETTLEMENTS s
         WHERE s.status != 'ACCEPTED'
      GROUP BY s.auth_id) us ON us.auth_id = t.auth_id

回复收藏 0 原文

~没有更多了~