SQL 如何将 2 个 select 语句合并为一个？

How do I combine 2 select statements into one?

提问by Wolf5

I am a noob when it comes to SQL syntax.

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

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

我当然有一个包含很多行和列的表格：P 可以说它看起来像这样：

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

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

现在我想创建一个高级的 select 语句，它给我这个组合（这里是伪 SQLish）：

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

The output would be:

输出将是：

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

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

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

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.

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

My REAL SQL statement is this one:

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

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'

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 语句的副本结合起来，并在末尾添加一个 AND，并且“状态”字段将被更改为像“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)

回答by casperOne

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 WHEREclause, and then UNIONthem together:

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

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 ...)

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

这可能是一个问题，因为您将在 TABLE 上有效地扫描/搜索两次。

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

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

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 ...))

The catch here being that you will have to duplicate the filter conditions in the CASEstatement and the WHEREstatement.

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

回答by Tomas Aschan

If they are from the same table, I think UNIONis the command you're looking for.

如果它们来自同一张表，我认为这UNION就是您要查找的命令。

(If you'd ever need to select values from columns of different tables, you should look at JOINinstead...)

（如果您需要从不同表的列中选择值，您应该查看JOIN......）

回答by Wolf5

Thanks for the input. Tried the stuff that has been mentioned here and these are the 2 I got to work:

感谢您的投入。尝试了这里提到的东西，这些是我开始工作的 2 个：

(
select 'OK', * 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 (BoolField05=1)
)
UNION
(
select 'DEL', * 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)
)

AND

和

select 
    case
        when
            (BoolField05=1)
    then 'OK'
    else 'DEL'
        end,
        *
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'

Which would be the most efficient of these (edit: the second as it only scans the table once), and is it possible to make it even more efficient? (The BoolField=1) is really a variable (dyn sql) that can contain any where statement on the table.

哪个是最有效的（编辑：第二个，因为它只扫描一次表格），是否有可能使它更有效？(BoolField=1) 实际上是一个变量 (dyn sql)，它可以包含表上的任何 where 语句。

I am running on MS SQL 2005. Tried Quassnoi examples but did not work as expected.

我在 MS SQL 2005 上运行。尝试了 Quassnoi 示例，但没有按预期工作。

回答by arthur bryant

select t1.* from 
(select * from TABLE Where CCC='D' AND DDD='X') as t1,
(select * from TABLE Where CCC<>'D' AND DDD='X') as t2

Another way to do this!

另一种方法来做到这一点！

回答by Fredou

use a case into the select and use in the where close a OR

在 select 中使用 case 并在 where close a OR 中使用

something like this, I didn't tested it but it should work, I think...

像这样的东西，我没有测试过，但它应该可以工作，我想......

select case when CCC='D' then 'test1' else 'test2' end, *
from table
where (CCC='D' AND DDD='X') or (CCC<>'D' AND DDD='X')

回答by Quassnoi

I think that's what you're looking for:

我认为这就是你要找的：

SELECT CASE WHEN BoolField05 = 1 THEN Status ELSE 'DELETED' END AS MyStatus, t1.*
FROM WorkItems t1
WHERE (TextField01, TimeStamp) IN(
  SELECT TextField01, MAX(TimeStamp)
  FROM WorkItems t2
  GROUP BY t2.TextField01
  )
AND TimeStamp > '2009-02-12 18:00:00'

If you're in Oracle or in MS SQL 2005 and above, then you could do:

如果您使用的是 Oracle 或 MS SQL 2005 及更高版本，那么您可以执行以下操作：

SELECT *
FROM (
  SELECT CASE WHEN BoolField05 = 1 THEN Status ELSE 'DELETED' END AS MyStatus, t1.*,
     ROW_NUMBER() OVER (PARTITION BY TextField01 ORDER BY TimeStamp DESC) AS rn
  FROM WorkItems t1
) to
WHERE rn = 1

, it's more efficient.

，效率更高。

回答by Kezzer

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'

UNION

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)

Perhaps that'd do the trick. I can't test it from here though, and I'm not sure what version of SQL you're working against.

也许那样做就行了。不过，我无法从这里对其进行测试，而且我不确定您所使用的 SQL 版本是什么。

回答by JB King

The Union command is what you need.If that doesn't work, you may need to refine what environment you are in.

Union 命令正是您所需要的。如果这不起作用，您可能需要改进您所处的环境。