gpt4 book ai didi

MySQL查询: select group_id but if any client_id in said group is = X then don't select

转载 作者:行者123 更新时间:2023-11-29 14:05:49 25 4
gpt4 key购买 nike

Sqlfiddle 是 http://sqlfiddle.com/#!2/7df50/4

基本上,我有 3 个表:组、成员资格、客户。

tbl.client = client_id (PK, AI), industry_id (FK), status
tbl.membership = membership_id (PK, AI), Client_id (FK to tbl.client),
group_id (FK to group), status
tbl.group = group_id (PK, AI), target_market_id (FK), geography_id (FK)

基本上,我想通过连接所有 3 个表来选择一个 group_id,其中没有一个客户端的 client.industry_id 等于给定的输入 ($client_industry_id)。

到目前为止我的查询是:

"select g.group_id from `group` g join membership m on m.group_id=g.group_id ".
"join client c on c.client_id=m.client_id ".
"where g.status=1 and m.status=1 and c.status=1 and ".
"g.geography_id=$target_geography and ".
"g.target_market_id=$target_market ".
"c.industry_id <> $client_industry_id";

该查询的问题在于,它仍然会从组中选择一个 group_id,因为所有客户端都不能具有 client_id = $client_industry_id 才能触发 <>。我希望这是有道理的?

我可以通过分组来解决这个问题吗?如果语句?

编辑:

insert into client (email, industry_id, status) VALUES ('email1@gmail.com', '1', '1')
insert into client (email, industry_id, status) VALUES ('email2@gmail.com', '2', '1')
insert into client (email, industry_id, status) VALUES ('email3@gmail.com', '2', '1')

insert into membership (client_id, group_id) VALUES (1, 1)
insert into membership (client_id, group_id) VALUES (2, 1)
insert into membership (client_id, group_id) VALUES (3, 2)

insert into group (geography_id, target_market_id) VALUES (1, 1)
insert into group (geography_id, target_market_id) VALUES (1, 1)

#psuedo code
"select group_id from group join membership on group_id, join client on client_id where
all status=1 and group.geography_id=1 and group.target_market_id=1 and
NONE of the clients have client.industry_id=1

-- query should result in group_id=2

最佳答案

你和@Strawberry是对的,之前的代码是行不通的。对于那个很抱歉。以下是您可能想尝试的快速破解方法:

select g.group_id from groupp g 
join membership m on m.group_id=g.group_id and m.status=1 //to consider check on status
join client c on c.client_id=m.client_id and c.status=1 //to consider check on status
where g.group_id not in (select mm.group_id from membership mm join client cc on mm.client_id=cc.client_id and cc.industry_id = $client_industry_id) and //to exclude groups with clients that have industry
g.status=1 and
g.geography_id=$target_geography and
g.target_market_id=$target_market;

关于MySQL查询: select group_id but if any client_id in said group is = X then don't select,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14353173/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com