SQL - 如果计数结果 = 0，则运行第二个查询

Question

我的两个 SELECT 查询都在下面工作，并返回带有两个单独结果的值。我想做的是，如果第一个 SELECT 语句返回 0，有时由于我们工作的性质，我希望第二个语句运行时忽略第一个返回零的语句(如果可能)。如果第一个返回的结果大于零，那么我不希望第二个运行。

我一直在寻找并尝试不同的 IF ELSE 等，但由于使用了特定的日期/时间公式，我认为这可能是不可能的，或者我可能必须完全重写。这样做是有目的的，但我不会让你感到无聊。提前致谢!

Declare @StartDate as DateTime
Declare @EndDate as DateTime
Declare @TodaysDate as DateTime
Declare @Previous as DateTime
Declare @Previous2 as DateTime

set @TodaysDate = GETDATE()
set @Previous = DATEADD(day,-1,@TodaysDate)
set @Previous2 = DATEADD(day,-2,@TodaysDate)

-- SELECT Statetment one starts here

set @StartDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous)) + '-' +
convert(varchar(2), datepart(dd, @Previous)) + ' ' +
'05:00' as datetime)
set @EndDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous)) + '-' +
convert(varchar(2), datepart(dd, @Previous)) + ' ' +
'16:59' as datetime)

SELECT Count(*) as FirstShfitPrevious
FROM [TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] 
WHERE  Close_Time_Stamp between @StartDate and @EndDate

-- Query 2 Starts, Declarations already made at beginning

set @StartDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous2)) + '-' +
convert(varchar(2), datepart(dd, @Previous2)) + ' ' +
'05:00' as datetime)
set @EndDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous2)) + '-' +
convert(varchar(2), datepart(dd, @Previous2)) + ' ' +
'16:59' as datetime)

SELECT Count(*) as FirstShfitPrevious2
FROM [TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] 
WHERE  Close_Time_Stamp between @StartDate and @EndDate

Answer 1

您可以尝试将第一个查询结果的值存储在变量中，然后检查该值，例如

declare @myVar int

SELECT @myVar = Count(*) as FirstShfitPrevious
FROM [TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] 
WHERE  Close_Time_Stamp between @StartDate and @EndDate)

if(@myVar = 0)
//Here your second query logic
begin
set @StartDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous2)) + '-' +
convert(varchar(2), datepart(dd, @Previous2)) + ' ' +
'05:00' as datetime)
set @EndDate = cast(convert(varchar(4), datepart(yyyy, getdate())) + '-' +
convert(varchar(2), datepart(mm, @Previous2)) + '-' +
convert(varchar(2), datepart(dd, @Previous2)) + ' ' +
'16:59' as datetime)

SELECT Count(*) as FirstShfitPrevious2
FROM [TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] 
WHERE  Close_Time_Stamp between @StartDate and @EndDate
end