sql-server - SQL Server 2014：如何在 csv 导出中包含标题和详细信息
问题描述
我正在尝试生成一个脚本来导出 csv 文件以显示标题记录和相应的详细记录。
例如，输出需要是
20138000100012, H, 2013800010, 03.04.2017, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, D, INSURE, 0.71
20138000100012, D, RENT, 98.58
20138000200060, H, 2013800020, 02.04.2018, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, D, INSURE, 0.80
20138000200060, D, RENT, 98.58
20138000500052, D, LAUNDR, 1.29
我尝试使用下面的 T-SQL 脚本来生成它：
select *
from
(select
cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar) AS [0],
'H' as [RowLevel],
substring(convert(varchar(10),dbo.[RE-TENANCY].[TENANCY-REF]), 1, 10) AS [1],
-- convert(varchar,dbo.[IH-PLACE-CHG].[START-DATE],104) AS [ ]
format(dbo.[IH-PLACE-CHG].[START-DATE], 'dd.MM.yyyy') AS [2],
dbo.[RE-TENANCY].[RENT-GROUP] AS [3],
cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],11,3) as nvarchar) AS [4]
, cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],14,1) as nvarchar) AS [5]
, cast(format(dbo.[RE-TENANCY].[TNCY-START], 'dd.MM.yyyy') as nvarchar) AS [6]
, cast(format(dbo.[RE-TENANCY].[TNCY-END], 'dd.MM.yyyy') as nvarchar) AS [7]
, substring(dbo.[RE-TENANCY].[TNCY-STATUS],1,1) AS [8]
FROM
dbo.[RE-EX-RAISE-DEBIT-DTL]
INNER JOIN
dbo.[RE-TENANCY] ON dbo.[RE-EX-RAISE-DEBIT-DTL].REFERENCE = dbo.[RE-TENANCY].[TENANCY-REF]
INNER JOIN
dbo.[IH-PLACE-CHG] ON substring(dbo.[RE-TENANCY].[TENANCY-REF],1,10) = dbo.[IH-PLACE-CHG].[PLACE-REF]
union
select cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar) AS [0]
,'D' as [RowLevel]
, dbo.[RE-EX-RAISE-DEBIT-DTL].[ACCOUNT-CODE] AS [1]
, convert(varchar,[dbo].[RE-EX-RAISE-DEBIT-DTL].[CHARGE-AMT]) AS [2]
, '' AS [3]
, '' AS [4]
, '' AS [5]
, '' AS [6]
, '' AS [7]
, '' AS [8]
FROM dbo.[RE-EX-RAISE-DEBIT-DTL] INNER JOIN
dbo.[RE-TENANCY] ON dbo.[RE-EX-RAISE-DEBIT-DTL].REFERENCE = dbo.[RE-TENANCY].[TENANCY-REF] INNER JOIN
dbo.[IH-PLACE-CHG] ON substring(dbo.[RE-TENANCY].[TENANCY-REF],1,10) = dbo.[IH-PLACE-CHG].[PLACE-REF]
) AS HBfile order by cast(HBfile.[0] as bigint), HBfile.[6] desc
但此代码错误地生成以下结果：
20138000100012, H, 2013800010, 03.04.2017, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, H, 2013800010, 04.04.2016, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, H, 2013800010, 02.04.2018, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, D, INSURE, 0.71
20138000100012, D, RENT, 98.58
20138000200060, H, 2013800020, 02.04.2018, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, H, 2013800020, 03.04.2017, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, H, 2013800020, 04.04.2016, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, D, INSURE, 0.80
20138000200060, D, RENT, 98.58
20138000500060, D, LAUNDR, 1.29
任何想法，帮助表示赞赏。谢谢
解决方案
试图找出问题所在
由于重复在 Headers 部分中，因此您应该查看 headers 查询：
select *
from
(select
cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar) AS [0],
'H' as [RowLevel],
substring(convert(varchar(10),dbo.[RE-TENANCY].[TENANCY-REF]), 1, 10) AS [1],
-- convert(varchar,dbo.[IH-PLACE-CHG].[START-DATE],104) AS [ ]
format(dbo.[IH-PLACE-CHG].[START-DATE], 'dd.MM.yyyy') AS [2],
dbo.[RE-TENANCY].[RENT-GROUP] AS [3],
cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],11,3) as nvarchar) AS [4]
, cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],14,1) as nvarchar) AS [5]
, cast(format(dbo.[RE-TENANCY].[TNCY-START], 'dd.MM.yyyy') as nvarchar) AS [6]
, cast(format(dbo.[RE-TENANCY].[TNCY-END], 'dd.MM.yyyy') as nvarchar) AS [7]
, substring(dbo.[RE-TENANCY].[TNCY-STATUS],1,1) AS [8]
FROM
dbo.[RE-EX-RAISE-DEBIT-DTL]
INNER JOIN
dbo.[RE-TENANCY] ON dbo.[RE-EX-RAISE-DEBIT-DTL].REFERENCE = dbo.[RE-TENANCY].[TENANCY-REF]
INNER JOIN
dbo.[IH-PLACE-CHG] ON substring(dbo.[RE-TENANCY].[TENANCY-REF],1,10) = dbo.[IH-PLACE-CHG].[PLACE-REF]
此查询包含 2 个内部联接，这意味着如果未在 1-1 关系上进行联接，则生成的行可能会超过源计数。
检查您提供的输出后：
20138000100012, H, 2013800010, 03.04.2017, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, H, 2013800010, 04.04.2016, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, H, 2013800010, 02.04.2018, WK, 001, 2, 21.05.1984, NULL, C
20138000100012, D, INSURE, 0.71
20138000100012, D, RENT, 98.58
20138000200060, H, 2013800020, 02.04.2018, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, H, 2013800020, 03.04.2017, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, H, 2013800020, 04.04.2016, WK, 006, 0, 25.05.2009, NULL, C
20138000200060, D, INSURE, 0.80
20138000200060, D, RENT, 98.58
20138000500060, D, LAUNDR, 1.29
除了使用此命令生成的第四列之外，所有标题列都是相同的：
format(dbo.[IH-PLACE-CHG].[START-DATE], 'dd.MM.yyyy') AS [2]
解决方案
如果您只需要第一个值，那么您可以使用First_Value()窗口函数：
FIRST_VALUE(format(dbo.[IH-PLACE-CHG].[START-DATE], 'dd.MM.yyyy')) OVER(PARTITION BY cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar)) as [2]
您可以在标题查询中添加一个不同的运算符，整个 SQL 命令将如下所示：
select *
from
(select Distinct
cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar) AS [0],
'H' as [RowLevel],
substring(convert(varchar(10),dbo.[RE-TENANCY].[TENANCY-REF]), 1, 10) AS [1],
-- convert(varchar,dbo.[IH-PLACE-CHG].[START-DATE],104) AS [ ]
FIRST_VALUE(format(dbo.[IH-PLACE-CHG].[START-DATE], 'dd.MM.yyyy')) OVER(PARTITION BY cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar)) as [2],
dbo.[RE-TENANCY].[RENT-GROUP] AS [3],
cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],11,3) as nvarchar) AS [4]
, cast(substring( dbo.[RE-TENANCY].[TENANCY-REF],14,1) as nvarchar) AS [5]
, cast(format(dbo.[RE-TENANCY].[TNCY-START], 'dd.MM.yyyy') as nvarchar) AS [6]
, cast(format(dbo.[RE-TENANCY].[TNCY-END], 'dd.MM.yyyy') as nvarchar) AS [7]
, substring(dbo.[RE-TENANCY].[TNCY-STATUS],1,1) AS [8]
FROM
dbo.[RE-EX-RAISE-DEBIT-DTL]
INNER JOIN
dbo.[RE-TENANCY] ON dbo.[RE-EX-RAISE-DEBIT-DTL].REFERENCE = dbo.[RE-TENANCY].[TENANCY-REF]
INNER JOIN
dbo.[IH-PLACE-CHG] ON substring(dbo.[RE-TENANCY].[TENANCY-REF],1,10) = dbo.[IH-PLACE-CHG].[PLACE-REF]
union
select cast(dbo.[RE-TENANCY].[TENANCY-REF] as nvarchar) AS [0]
,'D' as [RowLevel]
, dbo.[RE-EX-RAISE-DEBIT-DTL].[ACCOUNT-CODE] AS [1]
, convert(varchar,[dbo].[RE-EX-RAISE-DEBIT-DTL].[CHARGE-AMT]) AS [2]
, '' AS [3]
, '' AS [4]
, '' AS [5]
, '' AS [6]
, '' AS [7]
, '' AS [8]
FROM dbo.[RE-EX-RAISE-DEBIT-DTL] INNER JOIN
dbo.[RE-TENANCY] ON dbo.[RE-EX-RAISE-DEBIT-DTL].REFERENCE = dbo.[RE-TENANCY].[TENANCY-REF] INNER JOIN
dbo.[IH-PLACE-CHG] ON substring(dbo.[RE-TENANCY].[TENANCY-REF],1,10) = dbo.[IH-PLACE-CHG].[PLACE-REF]
) AS HBfile order by cast(HBfile.[0] as bigint), HBfile.[6] desc
推荐阅读
- angular - bootstrap class.active 在 Angular 8 中不起作用
- reactjs - 从 React 应用程序在 Google 日历上插入事件
- amp-html - Google cdn 更改 amp 页面规范和其他一些标签
- bash - 意外擦除后恢复 PATH
- mysql - MySQL - 根据条件获取每个成员的最后一条记录
- angular - 如何使用属性读取MongoDB中的另一个集合？
- javascript - 当您单击页面的链接时，更改另一个页面中同一菜单的链接的颜色
- flutter - 如何在 Flutter 中禁用凸起的按钮单击动画？
- node.js - 带有 fork 的异步快速处理程序
- c# - 使用 Simple Injector，是否可以通过其实现类型获得单例？