如何组合两个查询？

I am trying to find the results of this query:

我试图找到此查询的结果:

SELECT * 
FROM   Table1 
WHERE  ColumnX in (SELECT DISTINCT ColumnX FROM Table2 
                   UNION
                   SELECT DISTINCT ColumnY FROM Table2)

That also filters the results by DateAddedToMarket, a column in Table1 using this query:

这也通过DateAddedToMarket过滤结果,这是Table1中使用此查询的列:

         DateAddedToMarket >= '2018-01-01' 
AND      DateAddedToMarket < '2018-02-01' 
ORDER BY DateAddedToMarket DESC

I tried this:

我试过这个:

SELECT * 
FROM   Table1 
WHERE  ColumnX in (SELECT DISTINCT ColumnX FROM Table2 
                   UNION
                   SELECT DISTINCT ColumnY FROM Table2) 
AND    DateAddedToMarket >= '2018-01-01' 
AND    DateAddedToMarket < '2018-02-01' 
ORDER BY DateAddedToMarket DESC

I received the proper dates but also received results outside of values I wanted to match from Table2.

我收到了正确的日期,但也收到了我希望从表2中匹配的值之外的结果。

+--------+--------------+
|ColumnX |    ColumnY   |
+--------+--------------+
| 872510 |       879962 |
| 872514 |       879963 |
| 872511 |              |
| 872515 |              |
| 872512 |              |
| 879529 |              |
| 872513 |              |
| 879530 |              |
| 879959 |              |
| 879960 |              |
| 879961 |              |
+--------+--------------+

2 个解决方案

#1

This query will not work if DateAddedToMarket is intended to be searched in the subquery table (which is Table 2 in this case).

如果要在子查询表(在本例中为表2)中搜索DateAddedToMarket,则此查询将不起作用。

In here, the main query obtains the data from Table1 WHERE ColumnX is found in Table 2. So the date filter (DateAddedToMarket ) does not apply to this. It is just grabbing EVERYTHING that is given to it by Table1 through ColumnX...

在这里,主查询从表1中获取数据。在表2中找到了ColumnX.因此日期过滤器(DateAddedToMarket)不适用于此。它只是抓住Table1到ColumnX给它的一切......

In practice, if you are trying to search within Table 2, then this query would not achieve this. You'd need to do this:

实际上,如果您尝试在表2中进行搜索,那么此查询将无法实现此目的。你需要这样做:

SELECT * FROM Table1 WHERE ColumnX in (

SELECT DISTINCT ColumnX FROM Table2 WHERE DateAddedToMarket ... UNION

SELECT DISTINCT ColumnY FROM Table2 WHERE DateAddedToMarket ...)

#2

The select clauses "IN" function is terribly inefficient unless the query optimizer can make it a join. Change you "in(... union ...)" clause and join the tables. Then add your filters in the where clauses and Union two nearly identical Selects.

除非查询优化器可以使其成为连接,否则select子句“IN”函数非常低效。改变你“in(... union ...)”子句并加入表格。然后在where子句和Union两个几乎相同的选择中添加过滤器。

SELECT Table1.* FROM Table1 
JOIN Table2 on Table1.ColumnX = Table2.ColumnX
where Table1.DateAddedToMarket >= '2018-01-01' 
AND    Table1.DateAddedToMarket < '2018-02-01' 
UNION
SELECT Table1.* FROM Table1 
JOIN Table2 on Table1.ColumnX = Table2.ColumnY
where    Table1.DateAddedToMarket >= '2018-01-01' 
AND    Table1.DateAddedToMarket < '2018-02-01' 
ORDER BY Table1.DateAddedToMarket DESC

#1