如何将 2 个 select 语句合并为一个? [英] How do I combine 2 select statements into one?

问题描述

说到 SQL 语法，我是个菜鸟.

I am a noob when it comes to SQL syntax.

我当然有一个包含很多行和列的表格:P假设它看起来像这样:

I have a table with lots of rows and columns of course :P Lets say it looks like this:

      AAA BBB CCC DDD
-----------------------
Row1 | 1   A   D   X
Row2 | 2   B   C   X
Row3 | 3   C   D   Z

现在我想创建一个高级的选择语句，给我这个组合(这里是伪 SQLish):

Now I want to create an advanced select statement that gives me this combined (pseudo SQLish here):

select 'Test1', * from TABLE Where CCC='D' AND DDD='X'
select 'Test2', * from TABLE Where CCC<>'D' AND DDD='X'

输出将是:

Test1, 1, A, D, X
Test2, 2, B, C, X

如何将这两个 select 语句组合成一个不错的 select 语句?

How would I combine those two select statements into one nice select statement?

如果我像下面那样复杂化 SQL 会起作用吗(因为我自己的 SQL 语句包含一个存在语句)?我只想知道如何组合选择，然后尝试将其应用于我的更高级的 SQL.

Would it work if I complicated the SQL like below (because my own SQL statement contains an exists statement)? I just want to know how I can combine the selects and then try to apply it to my somewhat more advanced SQL.

select 'Test1', * from TABLE Where CCC='D' AND DDD='X' AND exists(select ...)
select 'Test2', * from TABLE Where CCC<>'D' AND DDD='X' AND exists(select ...)

我真正的 SQL 语句是这样的:

My REAL SQL statement is this one:

select Status, * from WorkItems t1
where  exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01) 
AND TimeStamp>'2009-02-12 18:00:00'

这给了我一个结果.但我想将它与此 select 语句的副本结合起来，并在末尾添加一个 AND，并且状态"字段将更改为像DELETED"这样的字符串.

which gives me a result. But I want to combine it with a copy of this select statement with an added AND on the end and the 'Status' field would be changed with a string like 'DELETED'.

select 'DELETED', * from WorkItems t1
where  exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01) 
AND TimeStamp>'2009-02-12 18:00:00'
AND NOT (BoolField05=1)

推荐答案

这里有两个选择.第一个是有两个结果集，它们将根据 WHERE 子句中的条件设置 'Test1' 或 'Test2'，然后将它们 UNION 放在一起:

You have two choices here. The first is to have two result sets which will set 'Test1' or 'Test2' based on the condition in the WHERE clause, and then UNION them together:

select 
    'Test1', * 
from 
    TABLE 
Where 
    CCC='D' AND DDD='X' AND exists(select ...)
UNION
select 
    'Test2', * 
from 
    TABLE
Where
    CCC<>'D' AND DDD='X' AND exists(select ...)

这可能是一个问题，因为您将在 TABLE 上进行两次有效的扫描/查找.

This might be an issue, because you are going to effectively scan/seek on TABLE twice.

另一种解决方案是从表中选择一次，然后根据表中的条件设置Test1"或Test2":

The other solution would be to select from the table once, and set 'Test1' or 'Test2' based on the conditions in TABLE:

select 
    case 
        when CCC='D' AND DDD='X' AND exists(select ...) then 'Test1'
        when CCC<>'D' AND DDD='X' AND exists(select ...) then 'Test2'
    end,
    * 
from 
    TABLE 
Where 
    (CCC='D' AND DDD='X' AND exists(select ...)) or
    (CCC<>'D' AND DDD='X' AND exists(select ...))

这里的问题是您必须在 CASE 语句和 WHERE 语句中复制过滤条件.

The catch here being that you will have to duplicate the filter conditions in the CASE statement and the WHERE statement.

这篇关于如何将 2 个 select 语句合并为一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！