sql 查询，在 where 类中进行行比较

发布于 2024-12-11 14:13:15 字数 259 浏览 0 评论 0原文

我有两个表：Customers 和 Products。一个客户可以拥有多个产品。

我正在尝试检索没有特定产品的客户。

例如，10 位客户购买了产品 A 和 B，另外 10 位客户购买了 A、B 和 >C。如何找回那些没有 C 产品的客户？

原文

分享到QQ

分享到微博

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

发布评论

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

缱倦旧时光 2024-12-18 14:13:15

对于您当前的数据库结构，这就是您正在寻找的：

select c.id, c.name, c.phone, c.address 
from Customers c
where not exists (select * from products p 
                  where p.customer_id = c.id and p.id = 'c')

但是，您应该考虑创建第三个表来存储单独的购买。

For your current DB structure, this is what you are looking for:

select c.id, c.name, c.phone, c.address 
from Customers c
where not exists (select * from products p 
                  where p.customer_id = c.id and p.id = 'c')

However, you should consider creating a third table to store the individual purchases.

回复收藏 0 原文

入怼 2024-12-18 14:13:15

select *
FROM customer c
WHERE NOT EXISTS (SELECT 1 from products p 
                  WHERE p.customer_id = c.id)

您确实应该（正如@Tony andrews 和@Adrian 已经建议的那样）有第三个表来存储哪些客户购买了哪些产品的详细信息。

类似于：

**Customer**
Id
Name
Address
Phone

**Product**
Id
Name
Price

**Customer_Product**
customer_id
product_id

这意味着您正在从产品表中删除冗余。考虑一下如果产品名称发生轻微更改，您需要做什么 - 您只需更新 1 行，而不是更新多行（就像您现在必须做的那样），并且不需要触摸您的交易历史都没有..

select *
FROM customer c
WHERE NOT EXISTS (SELECT 1 from products p 
                  WHERE p.customer_id = c.id)

You should really (as suggested already by @Tony andrews and @Adrian) have a third table to store details of which customers bought which product.

Somthing like:

**Customer**
Id
Name
Address
Phone

**Product**
Id
Name
Price

**Customer_Product**
customer_id
product_id

This means you're removing redundancy from your product table. Consider what you'd need to do if a product name changed slightly - instead of updating multiple rows (as you'd have to do now), you'd only have to update 1 row, and you wouldn't need to touch your transaction history at all..

回复收藏 0 原文

~没有更多了~