tsql - 使用生成的值和子查询进行选择

Question

我有一个包含以下表格的产品数据库：

• 产品*
• 证书*
• 报告*
• 说明*
• 产品_证书*
• 证书_报告
• 证书_描述

产品与证书、证书与报告、证书与说明之间存在多对多的关系。

每个标有 * 的表都有一个带有时间戳的列（可以为空）。

期望的输出

产品表的所有列+一个新列

• 0，如果行的时间戳为空
• 1，如果行的时间戳不为空，但任何相关实体（或其相关实体）的时间戳为空
• 2，如果行的时间戳不为空且所有相关实体（或其相关实体）的时间戳不为空

我什至不知道如何解决这个问题。

---

这是显示所有表如何连接在一起的查询：

   SELECT Products.*
     FROM Products
LEFT JOIN Products_Certificates
       ON Products_Certificates.ProductNumber = Products.ProductNumber
LEFT JOIN Certificates
       ON Certificates.ID = Products_Certificates.CertificateID
LEFT JOIN Certificates_Reports
       ON Certificates_Reports.CertificateID = Certificates.ID
LEFT JOIN Reports
       ON Reports.ID = Certificates_Reports.ReportID
LEFT JOIN Certificates_Descriptions
       ON Certificates_Descriptions.CertificateID = Certificates.ID
LEFT JOIN Descriptions
       ON Descriptions.ID = Certificates_Descriptions.DescriptionID

标签： tsqlsql-server-2012

Answer 1

使用模拟数据并用 SQL Server 2014 编写，这里有一些代码可以帮助您入门 -

 declare @Products table
(
  ProductNumber int not null,
  Created date
);

insert into @Products values
(1, '1/7/2019'),
(2, null),
(3, '3/4/2019'),
(4, '3/7/2019');

declare @Products_Certificates table
(
  CertificateID int not null,
  ProductNumber int not null,
  Created date
);

insert into @Products_Certificates values
(50, 1, null),
(51, 1, '2/2/2019'),
(52, 3, '2/2/2019'),
(53, 4, null),
(54, 4, null);

declare @Certificates table
(
  ID int not null,
  Created date
);

insert into @Certificates values
(50, null),
(51, '3/1/2019'),
(52, null);

with
detail as
(
  SELECT
  p.ProductNumber,
  p.Created,
  pc.CertificateID,
  c.ID,
  Products_Certs_Date_Flag = iif(pc.Created is null, 0, 1),
  Certificates_Date_Flag = iif(c.Created is null, 0, 1)
  FROM @Products p
  LEFT OUTER JOIN @Products_Certificates pc     ON pc.ProductNumber = p.ProductNumber
  LEFT OUTER JOIN @Certificates c               ON c.ID = pc.CertificateID
  /*
  LEFT OUTER JOIN @Certificates_Reports cr      ON cr.CertificateID = c.ID --no timestamp in this table
  LEFT OUTER JOIN @Reports r                    ON r.ID = cr.ReportID
  LEFT OUTER JOIN @Certificates_Descriptions cd ON cd.CertificateID = c.ID --no timestamp in this table
  LEFT OUTER JOIN @Descriptions d               ON d.ID = cd.DescriptionID;
  */
),
summary as
(
  select
  ProductNumber,
  Created,
  DateStatus = iif(Products_Certs_Date_Flag + Certificates_Date_Flag > 0, 'date exists', 'no date')
  from detail
),
pivoted as
(
  select
  ProductNumber,
  Created,
  [date exists],
  [no date]
  from summary
  pivot (count(DateStatus) for DateStatus in ([date exists], [no date])) p
)
select
ProductNumber,
Created,
Outcome =
case
  when Created is null then 0
  when [no date] > 0 then 1
  else 2
end
from pivoted
order by
ProductNumber;

以下是 CTE 中的中间结果集的屏幕截图：
详细信息

概括

枢转的

以下是最终结果：

需要做更多的工作才能使其他 4 个表在detailCTE 中处于活动状态。上面的代码让您了解如何解决原始帖子中概述的问题。此外，请仔细测试该case语句 - 如果未正确分配 1 和 2 值，则在针对实时数据运行此语句后，可能需要添加其他标准。