sybase - 一个表中的值不在另一个表中，位于 3 表连接的两端

发布于 2024-08-28 20:36:26 字数 1487 浏览 7 评论 0原文

假设情况：我在一家定制标牌制作公司工作，我们的一些客户提交的标牌设计比他们目前使用的标牌设计多。我想知道哪些标志从未被使用过。

涉及 3 个表：

表 A - 公司标志

sign_pk(unique) |公司PK |标志_描述
1 --------------------1 ---------------- 小
2 --------------------------------1 ---------------- 大
3 --------------------2 ---------------- 中等
4 --------------------------------2 ---------------- 巨型
5 --------------------3 ---------------- 横幅

表 B - 公司地点

company_pk |公司位置（唯一）
1 ------|------ 987
1 ------|------ 876
2 ------|-------- 456
2 ------|------ 123

表 C - 位置标志（有点夸张，但每行可以有 2 个标志，并且与公司位置是一对多关系到地点的标志）

company_location |正面标志 |后退标志
987 ------------ 1 ------------ 2
第987章 ------------ 2 ------------ 1
第876章 ------------ 2 ------------ 1
456 ------------ 3 ------------ 4
123 ------------ 4 ------------ 3

因此，a.company_pk = b.company_pk 且 b.company_location = c.company_location。我想尝试找到的是如何查询并返回sign_pk 5 不在任何位置。针对所有 front_sign 和 back_sign 值查询每个sign_pk 有点不切实际，因为所有表都有数百万行。表a 在sign_pk 和company_pk 上建立索引，表b 在两个字段上建立索引，表c 仅在公司位置上建立索引。我尝试编写的方式是“每个标志都属于一家公司，因此在属于与该标志相关的公司的任何位置找到不是正面或背面标志的标志。”

我原来的计划是：
选择a.sign_pk 从 a、b、c 其中 a.company_pk = b.company_pk 并且 b.company_location = c.company_location 和 a.sign_pk *= c.front_sign group by a.sign_pk 具有 count(c.front_sign) = 0

只是为了执行前面的符号，然后重复后面的操作，但这不会运行，因为 c 是外部联接的内部成员，并且也在内部联接中。

整件事相当复杂，但如果有人能理解它，我将成为你最好的朋友。

原文

Hypothetical situation: I work for a custom sign-making company, and some of our clients have submitted more sign designs than they're currently using. I want to know what signs have never been used.

3 tables involved:

table A - signs for a company

sign_pk(unique) | company_pk | sign_description

1 --------------------1 ---------------- small

2 --------------------1 ---------------- large

3 --------------------2 ---------------- medium

4 --------------------2 ---------------- jumbo

5 --------------------3 ---------------- banner

table B - company locations

company_pk | company_location(unique)

1 ------|------ 987

1 ------|------ 876

2 ------|------ 456

2 ------|------ 123

table C - signs at locations (it's a bit of a stretch, but each row can have 2 signs, and it's a one to many relationship from company location to signs at locations)

company_location | front_sign | back_sign

987 ------------ 1 ------------ 2

987 ------------ 2 ------------ 1

876 ------------ 2 ------------ 1

456 ------------ 3 ------------ 4

123 ------------ 4 ------------ 3

So, a.company_pk = b.company_pk and b.company_location = c.company_location. What I want to try and find is how to query and get back that sign_pk 5 isn't at any location. Querying each sign_pk against all of the front_sign and back_sign values is a little impractical, since all the tables have millions of rows. Table a is indexed on sign_pk and company_pk, table b on both fields, and table c only on company locations. The way I'm trying to write it is along the lines of "each sign belongs to a company, so find the signs that are not the front or back sign at any of the locations that belong to the company tied to that sign."

My original plan was:
Select a.sign_pk from a, b, c where a.company_pk = b.company_pk and b.company_location = c.company_location and a.sign_pk *= c.front_sign group by a.sign_pk having count(c.front_sign) = 0

just to do the front sign, and then repeat for the back, but that won't run because c is an inner member of an outer join, and also in an inner join.

This whole thing is fairly convoluted, but if anyone can make sense of it, I'll be your best friend.

分享到QQ

分享到微博

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

发布评论

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

尸血腥色 2024-09-04 20:36:26

像这样的事情怎么样：

  SELECT DISTINCT sign_pk
    FROM table_a
   WHERE sign_pk NOT IN
    (   
      SELECT DISTINCT front_sign sign
        FROM table_c
       UNION
      SELECT DISTINCT rear_sign sign
       FROM  table_c
    )

How about something like this:

  SELECT DISTINCT sign_pk
    FROM table_a
   WHERE sign_pk NOT IN
    (   
      SELECT DISTINCT front_sign sign
        FROM table_c
       UNION
      SELECT DISTINCT rear_sign sign
       FROM  table_c
    )

回复收藏 0 原文

秉烛思 2024-09-04 20:36:26

ANSI 外连接是您的朋友。 *= 具有狡猾的语义，应该避免

select distinct a.sign_pk, a.company_pk
from a join b on a.company_pk = b.company_pk 
left outer join c on b.company_location = c.company_location 
                  and (a.sign_pk = c.front_sign or a.sign_pk = c.back_sign) 
where c.company_location is null

注意，where 子句是对连接返回的行的过滤器，因此它表示“执行连接，但只给我没有加入到 c"

外连接几乎总是比 NOT EXISTS 和 NOT IN 更快

ANSI outer join is your friend here. *= has dodgy semantics and should be avoided

select distinct a.sign_pk, a.company_pk
from a join b on a.company_pk = b.company_pk 
left outer join c on b.company_location = c.company_location 
                  and (a.sign_pk = c.front_sign or a.sign_pk = c.back_sign) 
where c.company_location is null

Note that the where clause is a filter on the rows returned by the join, so it says "do the joins, but give me only the rows that didn't to join to c"

Outer join is almost always faster than NOT EXISTS and NOT IN

回复收藏 0 原文