表A
列1 列2
001 AA
001 AB
001 AC
002 AC
002 AB
002 EE需要查询出结果为 将列1的重复记录设为空 只保留第一行的记录
列1 列2
001 AA
AB
AC
002 AC
AB
EE
哪位高手能帮忙说下思路
列1 列2
001 AA
001 AB
001 AC
002 AC
002 AB
002 EE需要查询出结果为 将列1的重复记录设为空 只保留第一行的记录
列1 列2
001 AA
AB
AC
002 AC
AB
EE
哪位高手能帮忙说下思路
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 11:32:19
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([列1] NVARCHAR(10),[列2] NVARCHAR(10))
INSERT [tb]
SELECT '001','AA' UNION ALL
SELECT '001','AB' UNION ALL
SELECT '001','AC' UNION ALL
SELECT '002','AC' UNION ALL
SELECT '002','AB' UNION ALL
SELECT '002','EE'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select 列1=case when 列2=(select top 1 列2 from tb where 列1=t.列1) then 列1 else '' end,列2 from tb t
/*
列1 列2
---------- ----------
001 AA
AB
AC
002 AC
AB
EE(6 行受影响)
*/
if object_id('[表A]') is not null drop table [表A]
create table [表A]([列1] varchar(3),[列2] varchar(2))
insert [表A]
select '001','AA' union all
select '001','AB' union all
select '001','AC' union all
select '002','AC' union all
select '002','AB' union all
select '002','EE'select * from [表A]
SELECT [列1] = CASE WHEN num = 1 THEN [列1] ELSE '' END ,[列2]
FROM (
SELECT *,[num]=ROW_NUMBER()OVER (PARTITION BY [列1] order BY [列1])
FROM [表A])t/*列1 列2
001 AA
AB
AC
002 AC
AB
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列1] varchar(3),[列2] varchar(2))
insert [tb]
select '001','AA' union all
select '001','AB' union all
select '001','AC' union all
select '002','AC' union all
select '002','AB' union all
select '002','EE'select *,id=identity(int,1,1)into #t from tb
select [列1]=case when exists(select 1 from #t where [列1]=a.[列1] and id<a.id) then ''
else [列1] end,
[列2]
from #t a
/*
列1 列2
---- ----
001 AA
AB
AC
002 AC
AB
EE(6 行受影响)*/
drop table #t
select * from combinestring
---------------------------
a 1
a 2
b 1
b 2
b 3-----------------------------
select
case
when a.seq = 1 then a.col1 else ''
end as col1,
a.col2
from
(
select *, row_number() over(partition by col1 order by col1) as seq from combinestring
) as a
group by a.col1, a.col2, seq
---------------------------------------
a 1
2
b 1
2
3