如何排除Mysql查询中的特定行

Question

我在下面提到了两个表：

Table1

ID        Part1        key       Unique_1
ARF-11    Aekjh        aer-ert   IYT-15
ARF-12    Aferf        aee-sta   IYT-15
ARF-13    Berfe        aer-ert   IYT-15
ARF-14    Aeret        aty-ert   IYT-16
ARF-15    xrete        ant-ert   IYT-16
ARF-16    srete        ant-ert   IYT-17

Table2

Key      Value
aer-ert  Some
aee-sta  ALL
aty-ert  Few
ant-ert  Less

我只想获取那些对于每个Unique_1值都不应该有value=ALL或(key!=aee-sta)的行。

删除这样的行，其中任何Unique_1值都有Value=ALL。

所需输出：

ID        Part1        key       Unique_1  Value
ARF-14    Aeret        aty-ert   IYT-16    Few
ARF-15    xrete        ant-ert   IYT-16    Less
ARF-16    srete        ant-ert   IYT-17    Less

Answer 1

你可以尝试下面这样的方法

SELECT t1.ID, t1.part1, t1.key, t1.unique_1, t2.value FROM table1 t1 
    INNER JOIN (
        SELECT DISTINCT t1.unique_1 FROM table1 t1 INNER JOIN table2 t2 
        ON t1.key = t2.key AND (t2.`value` IN('All') OR t2.`key` IN('aee-sta'))
    ) AS tt
    ON tt.unique_1 != t1.Unique_1  
    INNER JOIN table2 t2 ON t2.key = t1.key

尝试此Demo