admin

SQL Server查询以隐藏重复的行列数据。不想删除重复的行

sql

SQL Server查询以隐藏重复的行列数据。不想删除重复的行。有条件地将数据显示为空白。

何时,我运行此SQL查询:

select 
    [Vch No.], [Vch Type], [Vch Ref],
    [Date], [Party Name], [Sales Ledger],
    [Amt], [GST Ledger], [TaxAmount], [Total]
from 
    [AccountData]

在第二个打印屏幕中,我没有显示[Vch Ref],[Date],[Party Name],[Sales Ledger],[Amt]和Total值的内容。


阅读 204

收藏
2021-07-01

共1个答案

admin

这看起来像是一个疯狂的解决方案,但是您可以使用窗口函数ROW_NUMBER()并使用CASE表达式检查行数是否大于1来实现它,例如:

select 
    [Vch No.],
    [Vch Type],
    case when rn > 1 then '' else [Vch Ref] end as [Vch Ref],
    case when rn > 1 then '' else [Date] end as [Date],
    case when rn > 1 then '' else [Party Name] end as [Party Name],
    case when rn > 1 then '' else [Sales Ledger] end as [Sales Ledger],
    case when rn > 1 then '' else [Amt] end as [Amt],
    [GST Ledger],
    [TaxAmount],
    case when rn > 1 then '' else [Total] end as [Total]
from (  
    select 
        [Vch No.],
        [Vch Type],
        [Vch Ref],
        [Date],
        [Party Name],
        [Sales Ledger],
        [Amt],
        [GST Ledger],
        [TaxAmount],
        [Total], 
        row_number() over (partition by [Vch No.],[Vch Type],[Vch Ref],[Date],[Party Name],[Sales Ledger],[Amt],[GST Ledger],[TaxAmount],[Total] order by [Vch No.]) rn
    from [AccountData]
)x

查看数据类型,如果有AmtINT,则要获取空白值,应将其转换为字符串。

2021-07-01