时间戳

首页 > 解决方案 > 查询相关旧报表的表

sql - 查询相关旧报表的表

问题描述

我回到这个问题。这是我的一些记录表：

CREATE TABLE [dbo].[test](
    [reportID] [int] NOT NULL,
    [caseID] [int] NOT NULL,
    [carriedOver] [bit] NULL,
    [oldReportID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (304, 4, 1, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (305, 4, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (306, 4, 1, 304)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (309, 4, 1, 306)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (311, 4, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (312, 4, 1, 309)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (100, 1, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (418, 8, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (436, 8, 1, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (438, 8, 0, 436)
GO

所需的输出如下：

对于 CaseID = 4，我应该返回 ReportID：304、306、309 和 312

对于 CaseID = 1，我应该只返回 ReportID : 100

对于 CaseID = 8，我应该只返回 ReportID：418

当第一个报告连同它所携带的内容一起被结转时，它基本上得到一连串携带的报告ID。如果第一个报告没有被结转，那么它应该只返回那个报告。

建议的解决方案是： 

declare @caseID int = 4
SELECT t.reportID
FROM dbo.test t
 WHERE ( exists ( SELECT 1 FROM dbo.test t1 WHERE t1.reportID = t.oldReportID and t1.caseID = @caseID ) or
         exists ( SELECT 1 FROM dbo.test t2 WHERE t2.oldReportID = t.reportID and t2.caseID = @caseID )  )
and caseID = @caseID
ORDER BY 1 asc

这适用于 CaseID=4，但不适用于 CaseID=1 或 CaseID=8。

有什么建议么？

标签： sqlsql-servertsql

解决方案

我认为您可以使用以下查询来解决它

    DECLARE @caseID INT = 4
    DECLARE @ftrptID INT = (Select MIN(reportID) from dbo.test where caseID=@caseID )
    if EXISTS(Select 1 from dbo.test where carriedOver=1 and reportID=@ftrptID)
    BEGIN
        SELECT t.reportID
        FROM dbo.test t
         WHERE ( exists ( SELECT 1 FROM dbo.test t1 WHERE t1.reportID = t.oldReportID and t1.caseID = @caseID ) or
                 exists ( SELECT 1 FROM dbo.test t2 WHERE t2.oldReportID = t.reportID and t2.caseID = @caseID )  )
        and caseID = @caseID
        ORDER BY 1 asc
    END
    ELSE

        SELECT t.reportID
        FROM dbo.test t
        where reportID=@ftrptID

推荐阅读