如何选择Sql Server中不存在的记录 [英] How to Select records that don't exist in Sql Server

问题描述

这不是关于在选择记录之前检查记录是否存在，反之亦然.

This isn't about checking if a record exists before selecting it, or viceversa.

问题是这样的，我有一个有很多记录的数据库，它们是按日期存储的，具体来说是按天存储的，每一天都应该有一组值，但有些日子可能根本没有任何值，它们可能不存在.所以当我尝试做一个查询时，我需要显示所有的数据，即使它在技术上不存在，它至少可以显示为一个空格，但它应该显示.

The issue is this, I have a database with many records, they are stored by date, by day to be specific, each day should have a set of values, but some days may not have any values at all, they may not exist. So when I try to do a query, I need to show all the data, even if it doesn't technically exist, it could show at least as a blank space but it should show.

想想这张桌子:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|  2 | 02/03/2014 |    2   |    2   |
|  3 | 02/04/2014 |    3   |    3   |
|  4 | 02/06/2014 |    4   |    4   |

现在查询需要做的是返回某个日期范围内的所有数据，比如从 02/01/2014 到 02/03/2014，它应该返回该日期范围之间的所有值，例如:

Now what the query needs to do is to return all the data that's in between a certain date range, say from 02/01/2014 to 02/03/2014, it should return all the values between that date range, say:

SELECT * FROM myTable WHERE date>=`02/01/2014` AND date<=`02/03/2014`

但这只会返回:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|  2 | 02/03/2014 |    2   |    2   |

现在我知道这是相当明显的，它根据我发送给查询的范围选择表中存在的所有数据，但我正在处理一个不关心是否有值的报告给定日期与否，它必须每天返回，即使那天有记录与否，这意味着即使 02/02/2014 不在表上，也必须返回

Now I know this is fairly obvious, it is selecting all the data that exists in the table given the range I sent to the query, but I'm working with a report that doesn't care if there were values for that given day or not, it must return every single day, even if there were records for that day or not, meaning that even if 02/02/2014 is not on the table, it has to be returned as well.

有点像这样:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|    | 02/02/2014 |        |        |
|  2 | 02/03/2014 |    2   |    2   |

没有数据没关系，它只是不能离开那一天就好像它没有发生一样.

It doesn't matter it has no data, it just can't leave the day as if it didn't happen.

我已经尝试过使用 COALESCE 和 ISNULL，但是由于那些日子没有值，也没有空值，所以它们根本无法替换 Null 与其他内容.

I already tried using COALESCE and ISNULL, but since there are no values for those days, there are no null values either, so they simply just cannot replace Null with something else.

有人有什么想法吗?

推荐答案

这应该有效:

WITH
    -- Numbers CTE, courtesy Itzik Ben-Gan
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L4),
    -- Turn into Dates
  Dates AS(SELECT CAST('18991231' AS DATETIME)+n AS d FROM Nums)
SELECT * 
FROM Dates d
LEFT JOIN myTable t ON d.d = t.[date]
WHERE d.d>='02/01/2014' AND d.d<='02/03/2014'

以下是针对较小日期范围优化的版本:

Here is a version that is optimized for smaller date ranges:

DECLARE @startDate datetime = '2/1/2014'
DECLARE @endDate datetime = '2/3/2014'
DECLARE @days as INT = DATEDIFF(dd, @startDate, @enddate) + 1
;
WITH
    -- Numbers CTE, courtesy Itzik Ben-Gan
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  T4   AS(SELECT TOP (@days) 1 AS c FROM L4),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS n FROM T4),
    -- Turn into Dates
  Dates AS(SELECT @startDate+n AS d FROM Nums)
SELECT * 
FROM Dates d
LEFT JOIN myTable t ON d.d = t.[date]
WHERE d.d>=@startDate AND d.d<=@endDate

这篇关于如何选择Sql Server中不存在的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！