首页 文章

如何编写一个有多个case的where子句？

提问于
浏览 次
0

我有一个具有可为空参数的存储过程，以下是我的查询

select * 
from table1
where table1.id = isnull(@id, table1.id)

现在有一些特殊的ID我用不同的方式对待它们 . 我正在添加另一个表table2，如下所示

combid    id
1         abc01
1         abc02
1         abc03
2         hig01
2         hig02

我必须更改查询以满足以下情况

  • 如果 @id 为null，则where子句为 table1.id = table1.id

  • 如果 @id 不为null，
    2.1如果table2中存在 @id ，则where子句将为 table1.id in (select id from table2 where combid in (select combid from table2 where id=@id))
    2.2否则where子句将是 table1.id = @id

我试过以下查询，但不起作用 .

select  * from table1
where (table1.id=@id and not exists(select * from table2 where id=@id)
or @id is null
or table1.id in (select id from table2 where combid in (select combid where id=@id)) and  exists(select * from table2 where id=@id))

如何更改存储过程的查询？

sql where-clause

2 回答

  • 0
    SELECT * FROM table1
WHERE
    @id IS NULL
    OR
    (
        @id IS NOT NULL
        AND
        (      
            (
                EXISTS (SELECT * FROM TABLE2 WHERE id = @id)
                AND
                table1.id IN (SELECT id FROM table2 WHERE combid in (SELECT combid FROM table2 WHERE id=@id))
            ) 
            OR
            (
                NOT EXISTS (SELECT * FROM TABLE2 WHERE id = @id)
                AND         
                table1.id = @id
            )
        )
    )
    回复于
  • 1

    您可以使用if ... else ...而不是只编写一个SELECT语句，使用 if else 更容易阅读 .

    if(@id is null)
    select * from table1
else if exists (select 1 from table2 where id = @id)
    select * from table1 
    where table1.id in 
                  (select id from table2 where combid in 
                         (select combid from table2 where id=@id)
                  )
else 
   select * from table1 where table1.id=@id
    回复于

相关问题