php - MySQL 从两个查询中获取匹配结果？

Question

我有一些 php 代码可以生成多个 MySql 查询并合并结果。我正在尝试找到一种组合查询的方法，以便输出两个查询中唯一匹配的字段。

PHP 代码输出示例。

SELECT LoadData_3 pow FROM tblLoadData WHERE LoadData_1 = 'test1' AND LoadData_2 = 'test2' 
UNION ALL
SELECT LoadData_3 pow FROM tblLoadData WHERE LoadData_1 = 'test3' AND LoadData_2 = 'test4'

我只想要两个查询中相同的 LoadData_3 字段。

Answer 1

使用INTERSECT操作:

(SELECT LoadData_3 pow 
  FROM tblLoadData 
 WHERE LoadData_1 = 'test1' 
   AND LoadData_2 = 'test2')
INTERSECT                     -- return the intersection of the two queries
(SELECT LoadData_3 pow 
  FROM tblLoadData 
 WHERE LoadData_1 = 'test3' 
   AND LoadData_2 = 'test4')

编辑

对于 MySQL，您可以使用 INNER JOIN USING (..)

SELECT pow -- Use DISTINCT pow instead if you want unique values (like INTERSECT)
  FROM (SELECT LoadData_3 pow 
          FROM tblLoadData 
         WHERE LoadData_1 = 'test1' 
           AND LoadData_2 = 'test2') T1
  INNER JOIN
       (SELECT LoadData_3 pow 
          FROM tblLoadData 
         WHERE LoadData_1 = 'test3' 
           AND LoadData_2 = 'test4') T2
  USING (pow)

或者使用 WHERE IN 子句:

SELECT LoadData_3 pow -- Use DISTINCT pow instead if you want unique values (like INTERSECT)
  FROM tblLoadData 
 WHERE LoadData_1 = 'test1' 
   AND LoadData_2 = 'test2'
   AND LoadData_3 IN (SELECT LoadData_3 pow 
                        FROM tblLoadData 
                       WHERE LoadData_1 = 'test3' 
                         AND LoadData_2 = 'test4')