代码之家 › 专栏 › 技术社区 › Wolf5

如何将两个select语句组合成一个语句?

conditional select sql

Wolf5 · 技术社区 · 17 年前

说到SQL语法,我是个笨蛋。

我有一个有很多行和列的表当然是:P 我们可以这样说:

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

现在,我想创建一个高级select语句,该语句提供以下组合(此处为伪SQLish):

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

如果我像下面这样复杂化SQL(因为我自己的SQL语句包含exists语句),它会工作吗?我只是想知道如何组合选择,然后尝试将其应用到更高级的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语句是:

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,“Status”字段将被更改为一个类似“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)

8 回复 | 直到 13 年前

casperOne 14 年前

你有两个选择。第一个是有两个结果集,它们将根据中的条件设置“Test1”或“Test2” WHERE 子句,然后 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 ...)

这可能是一个问题,因为您要在表上有效地扫描/查找两次。

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 声明和陈述

Tomas Aschan 17 年前

如果他们来自同一张桌子,我想 UNION 是您要查找的命令。

JOIN 相反……)

Wolf5 17 年前

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

和

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'

其中哪一个效率最高(编辑:第二个,因为它只扫描表一次), ? (BoolField=1)实际上是一个变量(dynsql),可以包含表上的任何where语句。

我正在运行MS SQL 2005。尝试了Quassnoi示例,但未按预期工作。

arthur bryant 9 年前

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

另一种方法!

Kieran Senior 17 年前

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)

也许这样就行了。不过,我不能从这里测试它,我也不确定您使用的是哪个版本的SQL。

JB King 17 年前

The Union command is what you need.

Fredou 17 年前

在“选择并在何处使用”中使用案例关闭或

像这样的东西,我没有测试它,但它应该工作,我想。。。

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

Quassnoi 17 年前

我想这就是你想要的:

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'

如果您在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

,效率更高。