SQL Server 去重复记录问题

[yanboss]

有如下数据表格

单据号 销售单位 商品 数量
001 客户A Q1 100
001 客户A Q2 200
001 客户A Q3 300

002 客户B Q4 400
002 客户B Q5 500

希望的查询结果是
单据号 销售单位 商品 数量
001 客户A Q1 100
--- ----- Q2 200
--- ---- Q3 300
002 客户B Q4 400
--- ---- Q5 500
像这样的查询 在SQL SERVER中如何实现

[nalnait]

[code=SQL]
-- E.G

DECLARE @TABLE TABLE(A VARCHAR(50),B VARCHAR(50),C INT,D VARCHAR(50))
INSERT INTO @TABLE(A,B,C)
SELECT 'AA','BB',1 UNION ALL
SELECT 'AA','BB',2 UNION ALL
SELECT 'AA','BB',3 UNION ALL
SELECT 'AAA','BBB',22 UNION ALL
SELECT 'AAA','BBB',33


DECLARE @A VARCHAR(50),@B VARCHAR(50)
SET @A='';SET @B='';

UPDATE @TABLE SET @B=@A,D=CASE WHEN @B=A THEN '---' ELSE A END,@A=A

SELECT * FROM @TABLE

/*
A B C D
---------- ---------- ----------- ----------
AA BB 1 AA
AA BB 2 ---
AA BB 3 ---
AAA BBB 22 AAA
AAA BBB 33 ---

(5 行受影响)
*/

[/code=SQL]

[xiequan2]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([单据号] varchar(3),[销售单位] varchar(5),[商品] varchar(2),[数量] int)
insert [tb]
select '001','客户A','Q1',100 union all
select '001','客户A','Q2',200 union all
select '001','客户A','Q3',300 union all
select '002','客户B','Q4',400 union all
select '002','客户B','Q5',500

 

--
alter table tb add id int identity(1,1)

select [单据号]=case when exists(select 1 from tb where id>t.id and [单据号]=t.[单据号])  then [单据号] else  '---' end ,


[销售单位]=case when exists(select 1 from tb where id>t.id and [销售单位]=t.[销售单位])  then [销售单位] else  '---' end,
[商品], [数量] from tb t

/*
单据号  销售单位  商品   数量
---- ----- ---- -----------
001  客户A   Q1   100
001  客户A   Q2   200
---  ---   Q3   300
002  客户B   Q4   400
---  ---   Q5   500
*/

[nalnait]

[code=SQL]
-- E.G

DECLARE @TABLE TABLE(A VARCHAR(50),B VARCHAR(50),C INT,D VARCHAR(50))
INSERT INTO @TABLE(A,B,C)
SELECT 'AA','BB',1 UNION ALL
SELECT 'AA','BB',2 UNION ALL
SELECT 'AA','BB',3 UNION ALL
SELECT 'AAA','BBB',22 UNION ALL
SELECT 'AAA','BBB',33


DECLARE @A VARCHAR(50),@B VARCHAR(50)
SET @A='';SET @B='';

UPDATE @TABLE SET @B=@A,D=CASE WHEN @B=A THEN '---' ELSE A END,@A=A

SELECT * FROM @TABLE

/*
A B C D
---------- ---------- ----------- ----------
AA BB 1 AA
AA BB 2 ---
AA BB 3 ---
AAA BBB 22 AAA
AAA BBB 33 ---

(5 行受影响)
*/


[code=/SQL]

[nalnait]

-- E.G 

DECLARE @TABLE TABLE(A VARCHAR(50),B VARCHAR(50),C INT,D VARCHAR(50)) 
INSERT INTO @TABLE(A,B,C) 
SELECT 'AA','BB',1 UNION ALL 
SELECT 'AA','BB',2 UNION ALL 
SELECT 'AA','BB',3 UNION ALL 
SELECT 'AAA','BBB',22 UNION ALL 
SELECT 'AAA','BBB',33 


DECLARE @A VARCHAR(50),@B VARCHAR(50) 
SET @A='';SET @B=''; 

UPDATE @TABLE SET @B=@A,D=CASE WHEN @B=A THEN '---' ELSE A END,@A=A 

SELECT * FROM @TABLE 

/* 
A          B          C          D 
---------- ---------- ----------- ---------- 
AA        BB        1          AA 
AA        BB        2          --- 
AA        BB        3          --- 
AAA        BBB        22          AAA 
AAA        BBB        33          --- 

(5 行受影响) 
*/ 

[xiequan2]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([单据号] varchar(3),[销售单位] varchar(5),[商品] varchar(2),[数量] int)
insert [tb]
select '001','客户A','Q1',100 union all
select '001','客户A','Q2',200 union all
select '001','客户A','Q3',300 union all
select '002','客户B','Q4',400 union all
select '002','客户B','Q5',500

 

--
alter table tb add id int identity(1,1)

select [单据号]=case when exists(select 1 from tb where id<t.id and [单据号]=t.[单据号])  then '---' else [单据号]  end ,


[销售单位]=case when exists(select 1 from tb where id<t.id and [销售单位]=t.[销售单位])  then '---' else [销售单位]  end,
[商品], [数量] from tb t
 

/*
单据号  销售单位  商品   数量
---- ----- ---- -----------
001  客户A   Q1   100
---  ---   Q2   200
---  ---   Q3   300
002  客户B   Q4   400
---  ---   Q5   500
*/