mysql 选择 id 不在

Question

表induscial_division

| industrial_division_id || ---------------------- || 1                      || 2                      || 3                      || 4                      || 5                      || 6                      |

Table members

| member_id | industrial_division_id || --------- | ---------------------- || 19961     | 1                      || 21381     | 3                      || 21582     | 4                      || 21947     | 3                      || 24466     | 5                      || 29168     | 1                      || 29184     | 1                      || 29218     | 1                      |

Table products

| product_id || ---------- || 2177       || 2196       || 2234       || 2356       || 2523       || 2779       |

Table industrial_division_product

| ID   | industrial_division_id | product_id || ---- | ---------------------- | ---------- || 2282 | 6                      | 2177       || 2301 | 6                      | 2196       || 2339 | 1                      | 2234       || 2461 | 5                      | 2356       || 3018 | 1                      | 2356       || 2628 | 6                      | 2523       || 2884 | 1                      | 2779       |

Table product_member

| product_member_id | product_id | member_id || ----------------- | ---------- | --------- || 21997             | 2423       | 19961     || 21998             | 2424       | 19961     || 21999             | 2425       | 19961     || 22000             | 2426       | 19961     || 22001             | 2427       | 19961     || 22002             | 2356       | 19961     || 24504             | 2196       | 21381     || 24748             | 2205       | 21582     || 24749             | 2523       | 21582     || 25116             | 2177       | 21947     || 28485             | 2356       | 24466     || 34257             | 2234       | 29168     || 34271             | 2779       | 29184     || 34298             | 2779       | 29218     |

i want to make sql appear the members who have products and product's division not in the same division for members i tried this sql but the member 19961 appear

the member 19961 locate in division (1) and he has product 2356 locate in two division (5,1)

SELECT
    product_member.product_member_id,
    product_member.member_id,
    product_member.product_id,
    GROUP_CONCAT(industrial_division_product.industrial_division_id) as product_division,
    members.industrial_division_id as member_division

FROM
    product_member

JOIN members ON members.member_id = product_member.member_id
JOIN industrial_division_product ON industrial_division_product.product_id = product_member.product_id
WHERE industrial_division_product.industrial_division_id != members.industrial_division_id
GROUP BY product_member.product_member_id

| product_member_id | member_id | product_id | product_division | member_division || ----------------- | --------- | ---------- | ---------------- | --------------- || 22002             | 19961     | 2356       | 5                | 1               || 24504             | 21381     | 2196       | 6                | 3               || 24749             | 21582     | 2523       | 6                | 4               || 25116             | 21947     | 2177       | 6                | 3               || 28485             | 24466     | 2356       | 1                | 5               |

Answer 1

这似乎是一个带有聚合的基本 JOIN 查询 - 但聚合应该处于正确的级别，我认为是成员/产品级别。然后，您可以使用 having 子句进行过滤，而不是使用 where 子句:

select pm.product_member_id as product_member_id, m.member_id,
       p.product_id, 
       group_concat(idp.industrial_division_id) as product_divisions,
       m.member_division as industrial_division_id
from product_member pm join
     members m
     on pm.member_id = m.member_id join
     industrial_division_product idp
     on ipd.product_id = pm.product_id
group by pm.product_member_id, m.member_division, p.product_id, m.member_id
having sum(m.industrial_division_id = idp.industrial_division_id) = 0;

sum() 正在计算与成员部门匹配的产品部门的数量。由于可能有多个产品部门，因此您只需要没有匹配的行。

sum() 本身使用 MySQL 快捷方式。在其他数据库中，可能会写成:

sum(case when m.industrial_division_id = idp.industrial_division_id then 1 else 0 end) = 0;