Sql查询以所述格式检索数据

Question

难以将此查询概念化。任何帮助都将不胜感激。

数据集

表:审计

Date   |Action  |PackageId
1/1/15 |Active  |1
1/2/15 |DeActive|1
11/3/16|Update  |2
12/3/16|Update  |2
13/3/16|Update  |2
14/3/16|Update  |2

表:包件

Id|Name
1 |package1
2 |package2

表:项目

Id|ItemName|PackageId
1 | item1  |1
2 | item2  |1
3 | item3  |1
4 | item4  |2
5 | item5  |2

这些表之间的关系是Audit.PackageID Is foreign key to Package.Id and Item.PackageId is foreign key to Pacakge.id

对于上面的数据，我想生成这样的报告

Package.Name|Item.ItemName|Audit.Date|Audit.Action
package1    |  item1      | 1/1/15   | Active
package1    |  item2      | 1/2/15   | DeActive
package1    |  item3      | NULL     | NULL
package2    |  item4      | 11/3/16  | update
package2    |  item5      | 12/3/16  | update
package2    |  NULL       | 13/3/16  | update
package2    |  NULL       | 14/3/16  | update 

基本上，如果项/审核信息大于包数，则项目或审核信息不会被重复，并且包信息会被重复。希望这有意义。

我基本上需要编写一个存储过程，它将返回如上所述的数据集。然后将这个结果输入到一个报表解析器中，该解析器将用NULL替换所有blanks并生成一个blanks报表。数据库是SQL-Server-2000。

Answer 1

实际上，您要做的是将两个报告放入一个表中；您有包项报告和包审核报告。通常，您使用子报表在报告层执行此操作。

如果必须这样做，则必须将这两个报告生成为带有键的表，以便将它们连接在一起。由于您使用的是SQL 2000，所以您没有ROW_NUMBER()函数来生成某些序列号.这很不幸。因此，您可以创建带有递增标识字段的临时表。

下面是您提供的模式：

CREATE TABLE #Audit
    (Date varchar(7), Action varchar(8), PackageId int)
;

INSERT INTO #Audit
    (Date, Action, PackageId)
VALUES
    ('1/1/15', 'Active', 1),
    ('1/2/15', 'DeActive', 1),
    ('11/3/16', 'Update', 2),
    ('12/3/16', 'Update', 2),
    ('13/3/16', 'Update', 2),
    ('14/3/16', 'Update', 2)
;

CREATE TABLE #Package
    (Id int, Name varchar(8))
;

INSERT INTO #Package
    (Id, Name)
VALUES
    (1, 'package1'),
    (2, 'package2')
;

CREATE TABLE #Item
    (Id int, ItemName varchar(5), PackageId int)
;

INSERT INTO #Item
    (Id, ItemName, PackageId)
VALUES
    (1, 'item1', 1),
    (2, 'item2', 1),
    (3, 'item3', 1),
    (4, 'item4', 2),
    (5, 'item5', 2)
;

然后再创建两个表来保存两个单独的报告并填充它们。报表表需要一个标识字段，以便为报告中的每一行生成一个唯一的编号：

create table #PackageItemsReport 
(   PackageItemsId int identity(1,1), 
    PackageId int, 
    PackageName varchar(8), 
    ItemName varchar(5)
)

insert into #PackageItemsReport
(PackageId,PackageName,ItemName)
select #Package.Id, #package.Name, #item.ItemName 
from #Package
join #Item
on #Package.Id = #item.PackageId
order by #Package.Id, #item.ItemName 

create table #PackageAuditReport 
(   PackageAuditId int identity(1,1), 
    PackageID int, 
    PackageName varchar(8), 
    AuditDate varchar(7), 
    AuditAction varchar(8)
)

insert into #PackageAuditReport
(PackageID,PackageName,AuditDate,AuditAction)
select #Package.Id, #Package.Name, #Audit.Date, #Audit.Action 
from #Package
join #Audit
on #Audit.PackageId = #Package.Id
order by #Package.Id, #Audit.Date, #Audit.Action 

然后，您需要使用包ID和生成的行号将两个报告连接在一起：

select ISNULL(PackageItemsReport.PackageName, PackageAuditReport.PackageName) Name,
    ItemName,
    AuditDate,
    AuditAction
from
(
    select #PackageItemsReport.*, PackageItemsId - MinPackageItemsId RowNum
    from #PackageItemsReport
    join
    (
    select PackageID, MIN(PackageItemsId) MinPackageItemsId
    From #PackageItemsReport
    group by PackageID
    ) MinPackageItemsIds
    on #PackageItemsReport.PackageID = MinPackageItemsIds.PackageID
) PackageItemsReport
full join
(
    select #PackageAuditReport.*, PackageAuditId - MinPackageAuditId RowNum
    from #PackageAuditReport
    join
    (
    select PackageID, MIN(PackageAuditId) MinPackageAuditId
    From #PackageAuditReport
    group by PackageID
    ) MinPackageAuditIds
    on #PackageAuditReport.PackageID = MinPackageAuditIds.PackageID
) PackageAuditReport
on PackageItemsReport.PackageID = PackageAuditReport.PackageID
    and PackageItemsReport.RowNum = PackageAuditReport.RowNum
order by ISNULL(PackageItemsReport.PackageID, PackageAuditReport.PackageID),
    ISNULL(PackageItemsReport.RowNum, PackageAuditReport.RowNum)

太乱了..。谢天谢地，ROW_NUMBER()函数是在Server 2000之后添加的。现在容易多了。