php - SQL查询要剔除多个表中的相同数据

Question

     **Table :1**                                         **Table:2**          

 id folio num  num1  num2  num3  num4  num5           id  number  folio
                                                      -------------------
 ------------------------------------------
 1   abcde 1000  2000  3000  4000  5000  0           1    6000    abcde
                                                     2    1000    abcde
 2   abcde 6000                                      3    7000    abcde
                                                     4    5000    abcde
                                                     5    10000   abcde




 output want to be 

 id number folio
 ---------------
 1   7000   abcde
 2   10000  abcde

 ----------------------
 I USED THE SQL QUERY OF 
 SELECT * FROM Table2 WHERE id_t = 'abcde'
 AND  number NOT IN(SELECT CONCAT(num,num1,num2,num3,num4,num5) FROM Table1 WHERE id_t = 'abcde')

 -----------------------------------------------

SQL查询要剔除多个表中的相同数据。

要比较两个不同字段的表，如果表中的数据只剔除要显示的唯一数据

1.table2 number(field) 想查table1 num,num1,num2,num3,num4,num5(field)
2.未在表1中单独显示的值

Answer 1

在子查询上执行 LEFT OUTER JOIN。子查询返回每个作品集的每个数字。

然后 WHERE 子句确保只返回没有匹配项的行(即，在子查询中找不到任何匹配项)。

SELECT Table2.id,
        Table2.number,
        Table2.folio
FROM Table2
LEFT OUTER JOIN
(
    SELECT folio, num AS aNum FROM table1
    UNION 
    SELECT folio, num1 AS aNum FROM table1
    UNION 
    SELECT folio, num2 AS aNum FROM table1
    UNION 
    SELECT folio, num3 AS aNum FROM table1
    UNION 
    SELECT folio, num4 AS aNum FROM table1
    UNION 
    SELECT folio, num5 AS aNum FROM table1
) sub0
ON Table2.folio = sub0.folio
AND Table2.number = sub0.num
WHERE sub0.folio IS NULL

可能更好地利用索引的替代方案:-

SELECT Table2.id,
        Table2.number,
        Table2.folio
FROM Table2
LEFT OUTER JOIN table1 t1a ON Table2.folio = t1a.folio AND Table2.number = t1a.num
LEFT OUTER JOIN table1 t1b ON Table2.folio = t1b.folio AND Table2.number = t1b.num1
LEFT OUTER JOIN table1 t1c ON Table2.folio = t1c.folio AND Table2.number = t1c.num2
LEFT OUTER JOIN table1 t1d ON Table2.folio = t1d.folio AND Table2.number = t1d.num3
LEFT OUTER JOIN table1 t1e ON Table2.folio = t1e.folio AND Table2.number = t1e.num4
LEFT OUTER JOIN table1 t1f ON Table2.folio = t1f.folio AND Table2.number = t1f.num5
WHERE COALESCE(t1a.num, t1b.num1, t1c.num2, t1d.num3, t1e.num4, t1f.num5) IS NULL

编辑

另一种方法，虽然不热衷于这种方法。使用子查询连接各种 num 字段的所有值，然后使用 GROUP_CONCAT 在多行上执行此操作。然后加入反对使用 FIND_IN_SET。可能效率低下，但为了娱乐，它是:-

SELECT Table2.id,
        Table2.number,
        Table2.folio
FROM Table2
LEFT OUTER JOIN
(
    SELECT folio, GROUP_CONCAT(CONCAT_WS(',', num, num1, num2, num3, num4, num5)) AS all_num
    FROM table1
    GROUP BY folio
) sub0
ON Table2.folio = sub0.folio
AND FIND_IN_SET(Table2.number, sub0.all_num) > 0
WHERE sub0.folio IS NULL