英文:
SQL Server COALESCE() Function
问题
我有一个使用Power Automate流的工作流，用于从SQL服务器检索一些数据，并执行SQL查询。如果返回的值为空，我需要返回一个值。请问有人能告诉我如何使用"COALESCE"函数吗？以下是我使用的脚本。
select * from dbo.Employee
where DATEDIFF(dd, convert(date, startDate, 103), CAST(GETUTCDATE() AS DATE)) = 90
<details><summary>英文:</summary>I have a power automate flow that using to retrieve some data from the SQL server and I'm executing a SQL query. I need to return a value if it is nullCan anyone please tell me how to use a "COALESCE" function for this ?Below is the script I'm using.
select * from dbo.Employee
where DATEDIFF(dd,convert(date,startDate,103),CAST(GETUTCDATE() AS DATE))=90
</details># 答案1**得分**: 1不要使用 `COALESCE`，使用 `OR` 来检查列 `IS NULL` 的值。而且，不要获取经过的天数，使用日期范围并检查 `startDate` 的值是否在该范围内；然后你的查询就是 SARGable 的。如果你的列 `startDate` 是日期和时间值，那么你应该这样做：```sqlSELECT * --这里应该是一个明确的列列表，而不是 *FROM dbo.Employee EWHERE (E.StartDate >= CAST(DATEADD(DAY, -90, GETUTCDATE()) AS DATE)AND E.StartDate < CAST(DATEADD(DAY, -89, GETUTCDATE()) AS DATE))OR E.StartDate IS NULL;
如果它只是一个 date 值，那么这样就可以：
SELECT * --这里应该是一个明确的列列表，而不是 *FROM dbo.Employee EWHERE E.StartDate = CAST(DATEADD(DAY, -90, GETUTCDATE()) AS DATE)OR E.StartDate IS NULL;
这还假定你没有将日期和时间值存储为 varchar 的致命设计缺陷，因为你的条件 convert(date, startDate, 103) 暗示你是这样做的。如果你确实将日期（和时间）值存储为 varchar（或其他基于字符串的数据类型），那么修复你的设计是第一步；已经有很多问答解释了如何做到这一点。
英文:
Don't use COALESCE, use an OR to check if the value of the column IS NULL. Also, don't get the number of days that have passed, use a date range and check if the value of startDate is between that range; then you will be a SARGable query.
If your columnm startDate is a date and time value, then you'd do this:
SELECT * --This should be an explicit list of columns, not *FROM dbo.Employee EWHERE (E.StartDate >= CAST(DATEADD(DAY, -90,GETUTCDATE()) AS DATE)AND E.StartDate < CAST(DATEADD(DAY, -89,GETUTCDATE()) AS DATE))OR E.StartDate IS NULL;
If it's just a date value, then this will work:
SELECT * --This should be an explicit list of columns, not *FROM dbo.Employee EWHERE E.StartDate = CAST(DATEADD(DAY, -90,GETUTCDATE()) AS DATE)OR E.StartDate IS NULL;
This also assumes you don't have the fatal design flaw of storing a date and time value as a varchar, as your clause convert(date,startDate,103) implies you are. If you are storing your date (and time) values as a varchar (or other string based data type), then fixing your design is step 1; there are plenty of Q&As out there already explaining how to do this.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论