跳过后续行中相同值的输出 [英] Skipping the output of identical values in subsequent rows

问题描述

朋友

我想使用 tbale tblABC 中的 sql 查询来跟踪输出类型

I want following type of output with sql query from tbale tblABC

  OUTPUT
  ======

  SrNo.         Date               BillNo            Amount
  =================================================
     1         10-02-2012            VAT-1            1200.00
     2                               RET-4              50.00
     3                               RET-5             150.50
     4         11-02-2012            VAT-2            1500.00
     5                               RET-6            2505.00
     6         12-02-2012            VAT-3              05.00

来自具有以下数据的表

     tblABC
     ======

     SrNo.         Date               BillNo            Amount
     =================================================
     1         10-02-2012            VAT-1            1200.00
     2         10-02-2012            RET-4              50.00
     3         10-02-2012            RET-5             150.50
     4         11-02-2012            VAT-2            1500.00
     5         11-02-2012            RET-6            2505.00
     6         12-02-2012            VAT-3              05.00

我想要的 OUTPUT 和 table 之间的区别在于输出我不想要日期重复，因为它在表格中.

The diffrence between the OUTPUT i want and table is that in OUTPUT i don't want Date Reptetion as it is in table.

请帮助我如何拥有它.

推荐答案

DECLARE @tblABC TABLE
(
    SrNo INT, [Date] DATETIME, BillNo CHAR(5), Amount DECIMAL(10,2)
);

INSERT @tblABC SELECT 1,'20120210','VAT-1',1200.00
UNION ALL SELECT 2,'20120210','RET-4',50.00
UNION ALL SELECT 3,'20120210','RET-5',150.50
UNION ALL SELECT 4,'20120211','VAT-2',1500.00
UNION ALL SELECT 5,'20120211','RET-6',2505.00
UNION ALL SELECT 6,'20120212','VAT-3',05.00;

;WITH x AS 
(
  SELECT SrNo, [Date] = REPLACE(CONVERT(CHAR(10), [Date], 103), '/', '-'), 
    BillNo, Amount, rn = ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY SrNo)
    FROM @tblAbC
)
SELECT SrNo, [Date] = CASE rn WHEN 1 THEN [Date] ELSE '' END, BillNo, Amount
  FROM x 
  ORDER BY SrNo;

这篇关于跳过后续行中相同值的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！