SQL server中的子查询

Question

下面的代码在SQL server中创建一个表，跟踪我的客户必须每年提交的年度证书。证书是在历年(2018年、2019年等)之后提交的。已经结束了。我需要一个查询，为每个客户提供最近历年的年度证书收到的日期，以及预期认证所针对的日历年。我的一些客户是啤酒，他们可能会提交过去一年的认证后，最近几年的认证已经收到。下表显示了预期的结果。我只想用一个查询。下面的查询在我的代码末尾，针对这种情况，不起作用。我在这里错过了什么？谢谢。

这些是预期的结果：

CLIENT_ID DATE_RECEIVED CERTIFICATION_YEAR

1         2019-01-02    2018

2         2020-01-07    2019

3         2021-01-10    2020

样本数据：

CREATE TABLE [dbo].[CERTIFICATION](
    [CERTIFICATION_ID] [numeric](11, 0) IDENTITY(1,1) NOT NULL,
    [CLIENT_ID] [numeric](11, 0) NOT NULL,
    [DATE_RECEIVED] [date] NOT NULL,
    [CERTIFICATION_YEAR] [numeric](4, 0) NOT NULL,
    
    
 CONSTRAINT [CERTIFICATION_PK] PRIMARY KEY CLUSTERED 
(
    [CERTIFICATION_ID] ASC
)
) ON [PRIMARY]
GO

DECLARE @YEAR_COUNTER int
DECLARE @YEAR_INC int

SET @YEAR_COUNTER = 2014
SET @YEAR_INC = 1

WHILE @YEAR_COUNTER <= 2018

BEGIN

INSERT INTO [dbo].[CERTIFICATION] (CLIENT_ID, DATE_RECEIVED, CERTIFICATION_YEAR)
VALUES (1, DATEADD(year, @YEAR_INC, '1-02-2014'),@YEAR_COUNTER)

INSERT INTO [dbo].[CERTIFICATION] (CLIENT_ID, DATE_RECEIVED, CERTIFICATION_YEAR)
VALUES (2, DATEADD(year, @YEAR_INC, '1-07-2015'),@YEAR_COUNTER+1)

INSERT INTO [dbo].[CERTIFICATION] (CLIENT_ID, DATE_RECEIVED, CERTIFICATION_YEAR)
VALUES (3, DATEADD(year, @YEAR_INC, '1-10-2016'),@YEAR_COUNTER+2)

SET @YEAR_COUNTER = @YEAR_COUNTER + 1
SET @YEAR_INC = @YEAR_INC + 1

END 

GO

INSERT INTO [dbo].[CERTIFICATION] (CLIENT_ID, DATE_RECEIVED, CERTIFICATION_YEAR)
VALUES (1, '2-2-2020',2013)

GO

下面的查询将触发一个错误。如果删除了查询的第三列，则查询将正常工作，但它只检索提交证书的最后一年。

SELECT        CERTIFICATION_ID, MAX(CERTIFICATION_YEAR) AS LAST_CERTIFICATION_YEAR,
                             (SELECT        DATE_RECEIVED
                               FROM            dbo.CERTIFICATION AS CERT
                               WHERE        (CLIENT_ID = dbo.CERTIFICATION.CLIENT_ID) AND (CERTIFICATION_YEAR = dbo.CERTIFICATION.CERTIFICATION_YEAR)) AS LAST_CERTIFICATION_DATE
FROM            dbo.CERTIFICATION
GROUP BY CERTIFICATION_ID

Answer 1

我相信你想要一个这样的查询：

SELECT
    C.CLIENT_ID,
    C.DATE_RECEIVED,
    C.CERTIFICATION_YEAR
FROM
    dbo.CERTIFICATION C
WHERE
    C.CERTIFICATION_YEAR =
    (SELECT MAX(D.CERTIFICATION_YEAR) FROM dbo.CERTIFICATION D WHERE C.CLIENT_ID = D.CLIENT_ID)