SQL if语句

Question

我有一个SQL查询：

Select 
   * from dbo.Users 
WHERE 
   numOfScans == 0 feedback >= {0} 
AND 
   feedback <= {1} 
ORDER BY 
   lastScan ASC

它给我带来了numOfScans为零的所有用户。还有一种情况是，如果这个SELECT给我带来的结果为零，所以我不想使用numOfScans，如下所示：

Select 
  * from dbo.Users 
WHERE 
  feedback >= {0} AND feedback <= {1} 
ORDER BY 
  lastScan ASC

在同一个查询中可以这样做吗？还是我需要一个新的查询？

Answer 1

你可以用一个联盟来形容这个词：

SELECT *
FROM dbo.Users 
WHERE numOfScans == 0 AND feedback BETWEEN {0} AND {1}
UNION ALL
SELECT *
FROM dbo.Users 
WHERE
    feedback BETWEEN {0} AND {1} AND
    (SELECT COUNT(*) FROM dbo.Users
     WHERE numOfScans == 0 AND feedback BETWEEN {0} AND {1}) = 0
ORDER BY lastScan;

这里的基本思想是，如果联盟的前半部分返回任何记录，那么下半部分将是空集，反之亦然。

Answer 2

您可以将第一个查询的结果放在temp变量中，并检查

@temp==0 then
Select * from dbo.Users 
WHERE feedback >= {0} AND feedback <= {1} 
ORDER BY lastScan ASC

Answer 3

备选方案：

使用存储过程

create procedure  procsample  @first varchar(10),@second varchar(10) as
begin
  declare @sql varchar(10);

    Select @sql= count(1) from dbo.Users 
     WHERE numOfScans == 0 feedback >= @first AND feedback <= @second;


   if(@sql=0)

   Select * from dbo.Users 
   WHERE feedback >= @first AND feedback <= @second
   ORDER BY lastScan ASC;

   else

   Select * from dbo.Users 
   WHERE numOfScans == 0 feedback >= @first AND feedback <= @second
   ORDER BY lastScan ASC;

end