如果第一个查询中的结果存在于第二个查询中，如何删除结果？ [英] How do I remove results from the first query, if they exist in a second query?

问题描述

如果第一个查询中的结果存在于第二个查询中，如何删除结果？



我查看过以前的stackoverflow线程但仍无法使其工作。



我的 MySQL 目前的查询如下：

How do I remove results from the first query, if they exist in a second query?

I've looked over previous stackoverflow threads and still can't get it to work.

My MySQL queries so far are below:

SELECT roomL.CityCode, roomL.TableCode
FROM (SELECT roomL.CityCode, roomL.TableCode
      FROM roomL
      WHERE roomL.StatusEnum = 1
      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
      AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now())

WHERE NOT EXISTS (SELECT roomL.CityCode , roomL.TableCode
                  FROM roomL
                  WHERE roomL.StatusEnum = 1
                  AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                  AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())

目前我收到错误消息：



SQL错误（1248）：每个派生表必须有自己的别名

推荐答案

试试这个希望它会帮到你

try this hope it will help you

SELECT roomL.CityCode, roomL.TableCode
    FROM (SELECT roomL.CityCode, roomL.TableCode
          FROM roomL
          WHERE roomL.StatusEnum = 1
          AND roomL.TableCode NOT IN (select stock.TableCode from stock)
          AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now()) as x

    WHERE roomL.CityCode+'#'+roomL.TableCode Not in
    (SELECT roomL.CityCode+'#'+roomL.TableCode
                      FROM roomL
                      WHERE roomL.StatusEnum = 1
                      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                      AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())

SELECT RL.CityCode, RL.TableCode
    FROM (SELECT roomL.CityCode, roomL.TableCode
          FROM roomL
          WHERE roomL.StatusEnum = 1
          AND roomL.TableCode NOT IN (select stock.TableCode from stock)
          AND roomL.TimeSeen BETWEEN 10/10/2014 AND Now()) RL

    WHERE RL.CityCode Not in
    (SELECT roomL.CityCode 
                      FROM roomL
                      WHERE roomL.StatusEnum = 1
                      AND roomL.TableCode NOT IN (select stock.TableCode from stock)
                      AND roomL.TimeSeen BETWEEN DATE_ADD( 17/10/2014 ,INTERVAL 1 DAY) AND Now())

这篇关于如果第一个查询中的结果存在于第二个查询中，如何删除结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！