选择条件匹配的数据,如果不匹配,则选择全部?

 收藏
select * from (values
    ('dept1','user1'),
    ('dept2','user2'),
    ('dept3','user3'),
    ('dept4','user4')
    )table1([department],[user])
where [user] = @id

方案1:      @id ='user1'

dept1

方案2:     @id ='user5'

dept1
dept2
dept3
dept4

这是从一个笨拙的查询中看起来的样子

declare @id varchar(12) = 'user1'
declare @var int = (select count(*) from table1 where [user] = @id)

select * from table1 where [user] = @id or @var = 0
回复
  • 惊魂者 回复

    declare @tab table (id int , value varchar(10))
    declare @id int = 4
    
    insert into @tab
    select 1,'Ajay'
    union all
    select 2,'Ajay1'
    union all
    select 3,'Ajay2'
    union all
    select 4,'Ajay3'
    union all
    select 5,'Ajay4'
    
    select * from @tab 
    where id = case when exists (select * from @tab where id = @id) then @id else id end
    

  • 哼~ 回复

    您可能需要先进行类似这样的检查

    Declare @RowCount int
    Select @RowCount = (select count(*) from [Table] Where [Column] = 'xxx')
    
    If @RowCount > 0
     begin 
      Select 1 -- put code here if records
     end
    else
     begin
      Select 2 -- put code here if no records
     end
    

  • 静止oo 回复

    如果将“如果存在”逻辑与实际查询分开,则可以有一个更好的执行计划:

    DECLARE @id varchar(10) = 'user5'
    DECLARE @table TABLE ([department] varchar(10), [user] varchar(10))
    
    insert into @table values 
                        ('dept1','user1'),
                        ('dept2','user2'),
                        ('dept3','user3'),
                        ('dept4','user4')
    
    DECLARE @exists BIT = 
    (SELECT 1 FROM @table WHERE [user] = @id)
    
    SELECT department FROM @table
    WHERE [user] = CASE @exists WHEN 1 THEN @id ELSE [user] END 
    

  • sit_id 回复

    DECLARE @id VARCHAR(5) = 'user1';
    --DECLARE @id VARCHAR(5) = 'user5';
      WITH  UsersAndDepartments
    AS ( SELECT   *
        FROM     ( VALUES ( 'dept1', 'user1'), ( 'dept2', 'user2'),
                ( 'dept3', 'user3'), ( 'dept4', 'user4') ) x ( [department], [user] )
        )
    SELECT  *
    FROM    UsersAndDepartments ud1
    WHERE   ud1.[user] = 
        CASE 
            WHEN EXISTS ( SELECT 1 FROM UsersAndDepartments ud2 WHERE ud2.[user] = @id ) THEN @id
            ELSE ud1.[user] 
        END
    

    上面只是检查用户列是否存在ID的任何行,否则全部匹配。

  • 回到最初 回复

    I would do this with a simple OR, not a CASE expression in the WHERE.

    In general, you want to avoid CASE expressions in the WHERE clause for several reasons:

    • The logic can almost be written concisely using basic boolean operations.
    • Adding additional constructs (in addition to AND, OR, and NOT) just makes the logic harder for people to follow.
    • It pretty much kills any optimization paths.

    我会建议:

    with table1 as 
          select v.*
          from (values ('dept1', 'user1'),
                       ('dept2', 'user2'),
                       ('dept3', 'user3'),
                       ('dept4', 'user4')
               ) v([department], [user])
         )
    select t1.*
    from table1 t1
    where t1.[user] = @id or
          not exists (select 1 from table1 t1 where t1.user = @id);
    

  • dquia 回复

    您可以尝试以下方法:

    DECLARE @id varchar(12) = 'user1'
    
    IF EXISTS(SELECT COUNT(*) FROM table1 WHERE [user] = @id)
    BEGIN
    SELECT * FROM table1 WHERE [user] = @id
    END
    ELSE
    BEGIN
    SELECT * FROM table1
    END
    

    you can also read more about "EXISTS" syntax on: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017