显示结果集中重复值的空格 [英] shows blanks for repeating values in a result set

问题描述

在下面的查询中，对于 o.EventSetName、o.EventSetDisplay、o.EventSetDescription - 任何时候结果中的 3 列有重复的 ROWS - 只应显示第一个这样的行，其余的重复行应为空白.....

In the query below, for o.EventSetName, o.EventSetDisplay, o.EventSetDescription - any time the 3 columns in the result have duplicate ROWS - only the first such row should be shown and blank for the rest of the duplicate rows.....

这里是sql:

 Select  distinct top 100000 o.EventSetName,       
                             o.EventSetDisplay,
                             o.EventSetDescription,
                             o.ChildSetName,
                             ROW_NUMBER() Over (Order By f.ChildSetName) RN,
                             f.DocumentDispSequence,
                             f.SectionDispSequence,
                             o.ObsSetDispSequence,
                             null                          
                      From   ##ObsSetLevel o,
                             ##Final f
                      Where  f.ChildSetName = o.EventSetName and 
                             o.EventSetName = @variableName
                      Order By RN asc, f.DocumentDispSequence asc, f.SectionDispSequence asc, o.ObsSetDispSequence asc

我没有报告工具，所以现在很多报告逻辑需要在存储过程本身中完成...

I dont have a reporting tool so for now a lot of the reporting logic needs to be done in the stored proc itself...

所以代替:

val 1   val2    val3  val7
val 1   val2    val3  val8
val 1   val2    val3  val 10
val 1   val2    val3  x
val 1   val2    val3  y

我应该得到第 2、3、4 和 5 行的前 3 列空白的结果

I should get the resullt with blanks for the first 3 columns for rows 2,3,4 and 5

推荐答案

在 CTE 中包装您现有的查询，添加 ROW_NUMBER OVER PARTITION BY 您的列，这将为每组值创建 RN.在外部查询中，只需使用 CASE 选择 GRP_RN = 1 的值，否则为空字符串.

Wrap your existing query in CTE adding ROW_NUMBER OVER PARTITION BY your columns, which will create RNs for each group of values. In outer query just use CASE to select values where GRP_RN = 1 and empty string otherwise.

WITH CTE AS 
(
    Select  distinct top 100000 
        o.EventSetName,       
        o.EventSetDisplay,
        o.EventSetDescription,
        o.ChildSetName,
        ROW_NUMBER() Over (Order By f.ChildSetName) RN,
        f.DocumentDispSequence,
        f.SectionDispSequence,
        o.ObsSetDispSequence,
        null  as NullColumnNeedsName,
        ROW_NUMBER() OVER (PARTITION BY o.EventSetName, o.EventSetDisplay,o.EventSetDescription ORDER BY f.ChildSetName) GRP_RN
    From   ##ObsSetLevel o,
    INNER JOIN ##Final f ON f.ChildSetName = o.EventSetName and o.EventSetName = @variableName
)
SELECT
    CASE WHEN GRP_RN = 1 THEN o.EventSetName ELSE '' AS EventSetName,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDisplay ELSE '' AS EventSetDisplay,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDescription ELSE '' AS EventSetDescription,
    other columns
FROM CTE  
Order By RN asc, DocumentDispSequence asc, SectionDispSequence asc, o.ObsSetDispSequence asc

PS:我还更正了您对旧式连接的使用.这种用法在 20 多年前随着 SQL-92 标准的引入而过时.你应该避免使用它们.

PS: I have also corrected your use of old-style joins. That usage is outdated more than 20 years ago with introduction of SQL-92 standards. You should avoid using them.

这篇关于显示结果集中重复值的空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！