在办公室,我们需要合并一个 MS SQL 表,每个 Quotaion 编号应该得到 1 行。见表:https : //drive.google.com/file/d/0B8AuHxQASEWyaEhfSVpDT3lIV1U/view?usp=sharing
尝试使用SELECT DISTINCT
但无法获取。
SQL Server 查询:
SELECT DISTINCT ([QuotationNumber])
,[CreationDate]
,[QuotationDate]
,[CustomerNumber]
,[CustomerName]
,[SalesPersonName]
,[Rreg]
,[ProductDescription]
,[FamilyDescription]
,[NameVariant]
,[StringValue]
FROM [ABC].[dbo].[DEF]
WHERE [CreationDate] > "2017-05-08 00:00:00.000"
ORDER BY [CreationDate] DESC
如果您保留所有列,则不能。您可以从您的 [FamilyDescription]、[NameVariant]、[StringValue] 中删除,select
或者为目标结果中的这些值添加新列。
SELECT DISTINCT ([QuotationNumber])
,[CreationDate]
,[QuotationDate]
,[CustomerNumber]
,[CustomerName]
,[SalesPersonName]
,[Rreg]
,[ProductDescription]
FROM [ABC].[dbo].[DEF]
WHERE [CreationDate] > "2017-05-08 00:00:00.000"
ORDER BY [CreationDate] DESC
您应该执行以下操作来保留所有值:
SELECT DISTINCT [QuotationNumber]
,[CreationDate]
,[QuotationDate]
,[CustomerNumber]
,[CustomerName]
,[SalesPersonName]
,[Rreg]
,[ProductDescription]
,MIN([FamilyDescription]) [FamilyDescription]
,MAX(CASE WHEN NameVariant = 'DT_residual' THEN StringValue END) [DT_residual]
,MAX(CASE WHEN NameVariant = 'DT_interestRateSum' THEN StringValue END) [DT_interestRateSum]
,MAX(CASE WHEN NameVariant = 'DT_depositValue' THEN StringValue END) [DT_depositValue]
FROM [ABC].[dbo].[DEF]
GROUP BY [QuotationNumber]
,[CreationDate]
,[QuotationDate]
,[CustomerNumber]
,[CustomerName]
,[SalesPersonName]
,[Rreg]
,[ProductDescription]
HAVING [CreationDate] > "2017-05-08 00:00:00.000"
ORDER BY [CreationDate] DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句