SQL查询显示购买苹果但没有土豆的客户

发布于 2025-02-09 01:42:37 字数 1179 浏览 0 评论 0原文

不确定如何解释这一点。

我有一个类似的表，但是我已经用以下内容简化了它：

我有一张运送到不同的库司机的货物表。有些人只买了苹果，另一些人买了苹果和钳子。

我希望SQL查询仅返回“被收费”的客户=是，并且客户没有购买任何蔬菜。

因此，例如，如果表看起来像这样：

项目	名称	组	to_be_bill	customerno。
2000	苹果	水果	是	1
2000	苹果	水果	否	22000
水果	苹果	水果	否	3
2000	苹果	水果	是	4
2000	苹果	薯片	否	2
54000	是	否	我	4000
组	薯条	名称	4	希望

查询返回

：	to_be_bill	项目	custicerno	。
2000	苹果	水果	是	1
2000	苹果	水果	是	5

原因4的原因是要收取苹果的原因，但客户也买了土豆，因此也要忽略...

原文

Not sure how to explain this..

I have a similar table, but i have simplified it with the following:

I have a table of goods shipped to different cusotmers. Some have bought apples only, others have bought apples and potates.

I want an SQL query to return only customers where "To be billed" = Yes AND the customer hasnt bought any vegetables.

So for example if the table looks like this:

Item	Name	Group	To_be_billed	CustomerNo.
2000	Apple	Fruit	Yes	1
2000	Apple	Fruit	No	2
2000	Apple	Fruit	No	3
2000	Apple	Fruit	Yes	4
2000	Apple	Fruit	Yes	5
4000	Potato	Vegetable	No	2
4000	Potato	Vegetable	No	4

I want the query to return:

Item	Name	Group	To_be_billed	CustomerNo.
2000	Apple	Fruit	Yes	1
2000	Apple	Fruit	Yes	5

The reason 4 has bought apples, and is to be billed, but the customer also bought Potatoes, so is to be ignored...

分享到QQ

分享到微博

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

发布评论

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

月竹挽风 2025-02-16 01:42:37

您可以创建一个CTE检查customerno

with bought_veg as
(
    select "CustomerNo."
    from tbl
    where tbl."Group" like 'Vegetable'
)
select tbl.*
from tbl
where not exists (select 1 from bought_veg where tbl."CustomerNo." = bought_veg."CustomerNo.")
    and tbl.To_be_billed = 'Yes'

。

select tbl.*
from tbl
where not exists (select "CustomerNo." from tbl t2 where tbl.[CustomerNo.] = t2.[CustomerNo.] and "Group" like 'Vegetable')
    and tbl.To_be_billed = 'Yes'

You can create a CTE to check for CustomerNo.s that you need to ignore, and then use not exists:

with bought_veg as
(
    select "CustomerNo."
    from tbl
    where tbl."Group" like 'Vegetable'
)
select tbl.*
from tbl
where not exists (select 1 from bought_veg where tbl."CustomerNo." = bought_veg."CustomerNo.")
    and tbl.To_be_billed = 'Yes'

Example without CTE:

select tbl.*
from tbl
where not exists (select "CustomerNo." from tbl t2 where tbl.[CustomerNo.] = t2.[CustomerNo.] and "Group" like 'Vegetable')
    and tbl.To_be_billed = 'Yes'

回复收藏 0 原文

~没有更多了~