使用可比列在MySQL中相交

Question

我想使用mySQL创建以下两个查询的交集：

1) SELECT atn_nid, atn_select_flag, atn_image_name
FROM city, attraction WHERE cty_nid = atn_cty_nid AND cty_nid = 5;
2) SELECT atn_nid, s2a_image_select_flag, atn_image_name 
FROM segment, seg2atn, attraction WHERE seg_nid = s2a_seg_nid AND
s2a_atn_nid = atn_nid and seg_nid = 68 AND
s2a_image_select_flag = true;  

注意：查询1中的ATN_SELECT_FLAG和S2A_IMAGE_SELECT_FLAG都是boolean

由于MySQL出于某种原因不支持Intersect，因此我对实现这一目标的语法一无所知。任何帮助将不胜感激。

Answer 1

如果我正确地假设：

• 分别属于atn_...，cty_...，seg_...和s2a_...的字段分别属于attraction，city，segment和seg2atn。
• 名为..._nid的字段是主要密钥。
• atn_cty_nid，s2a_seg_nid等是外国密钥。

那么您的第一个查询等效于此：

SELECT atn_nid, atn_select_flag, atn_image_name
  FROM attraction
 WHERE aty_cty_nid = 5
;

您的第二个查询等同于此：

SELECT atn_nid, TRUE, atn_image_name
  FROM attraction
 WHERE atn_id IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;

所以他们的交点是：

SELECT atn_nid, TRUE AS atn_select_flag, atn_image_name
  FROM attraction
 WHERE atn_cty_nid = 5
   AND atn_nid IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;

和（编辑为添加）他们的结合是：

SELECT atn_nid, atn_select_flag, atn_image_name
  FROM attraction
 WHERE aty_cty_nid = 5
UNION
SELECT atn_nid, TRUE, atn_image_name
  FROM attraction
 WHERE atn_id IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;