查询以找到一个月的价值

Question

我有一个表格：

CREATE TABLE TestT (Id INT, Dates DATE)
INSERT TestT VALUES
(1,'2018-01-01'),
(2,'2018-01-01'),
(3,'2018-01-01'),
(4,'2018-01-01'),
(1,'2018-02-01'),
(3,'2018-02-01'),
(4,'2018-02-01'),
(1,'2018-03-01'),
(2,'2018-03-01'),
(4,'2018-03-01')

---

我需要输出，例如：

Id      Date      Status
1       1/1/2018    Y
2       1/1/2018    Y
3       1/1/2018    Y
4       1/1/2018    Y
1       2/1/2018    Y
2       NULL        N
3       2/1/2018    Y
4       2/1/2018    Y
1       3/1/2018    Y
2       3/1/2018    Y
3       NULL        N
4       3/1/2018    Y

Answer 1

我的方法是生成所有可能的记录，然后重新加入原始表以检测缺失的记录。

SELECT DISTINCT
  a.Dates,
  b.VAL,
  CASE 
    WHEN c.Id IS NOT NULL THEN 'Y'
    ELSE 'N'
  END AS Status
FROM
  TestT a
LEFT JOIN
  (SELECT 1 AS VAL UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) b
  ON 1 = 1
LEFT JOIN
  TestT c
  ON c.Dates = a.Dates
  AND c.Id = b.VAL

Answer 2

此处的想法是要与两个字段的交叉加入并将其放回现有表。

Select a.id, b.dates, case when c.id is null then 'n' else 'y' end status from
(Select distinct Id from testt) a
Cross join (select distinct dates from testt) b
Left outer join testt c on a.id=c.id and b.status=c.status