合并'2 if condition'查询 [英] make union of '2 if condition' queries

问题描述

IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END

–UNION ALL

IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END

大家好，我需要使用groupby子句对这个两个条件"查询进行合并，
请帮忙……在此先感谢

hi all, i need to make union of this ''two if condition'' Queries with groupby clause,
please help…… Thanks in advance

推荐答案

这不会解决您眼前的问题，但是重复调用GETDATE是一个非常糟糕的主意-如果数据库服务器时间接近午夜，您可能会遇到一些非常奇怪的间歇性错误，几乎无法追踪.而是声明包含当前日期和/或相关组件的服务器变量.您可能会发现这同时提高了您的执行性能.

This won''t solve your immediate problem, but it is a very bad idea to do repeated calls to GETDATE - if the database server time approaches midnight, you can get some very strange, intermittent bugs which can be next to impossible to track down. Instead, declare server variables which hold the current date and / or the relevant components. You may find this improves your execution performance at the same time.

我同意OriginalGriff！

尝试使用变量并查看结果:

I agree with OriginalGriff!

Try to use variables and see results:

SET DATEFORMAT ymd; 

DECLARE @aDay VARCHAR(25)
DECLARE @eDay VARCHAR(25)
DECLARE @aQry1 VARCHAR(2000)
DECLARE @aQry2 VARCHAR(2000)
DECLARE @aQry3 VARCHAR(2000)

SET @aDay = CONVERT(VARCHAR, GETDATE())
SET @eDay = CONVERT(VARCHAR, DATEADD(dd,-(DAY(@aDay)-1),@aDay))

SET @aQry1 = 'SELECT ''' + @aDay + ''' AS [Today]'
--EXEC(@aQry1)

SET @aQry2 = 'SELECT ''' + @eDay + ''' AS [ExpectedDate]'
--EXEC(@aQry2)

SET @aQry3 = @aQry1 + ' UNION ALL ' + @aQry2
EXEC(@aQry3)

GROUP BY子句保留用于SELECT查询中的聚合函数，以提供有关每个组而不是单个行的信息.有关此条款的更多信息，请参见msdn 站点 [

GROUP BY clause is reserved for aggregate functions in the SELECT queries to provide information about each group instead of individual rows. More about this clause you''ll find at msdn site[^].

这篇关于合并'2 if condition'查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！