1-
1
11
11
2
21
22
3
ab
ab
ac
csd
csd
caa
......
我需要把11 和ab和 csd 找出来
------解决方案--------------------
with cte as
(
select *,row_number() over(order by column,partition by column) num from table
)
select * from cte
where num=2
------解决方案--------------------
- SQL code
declare @T table (col varchar(3))insert into @Tselect '1-' union allselect '1' union allselect '11' union allselect '11' union allselect '2' union allselect '21' union allselect '22' union allselect '3' union allselect 'ab' union allselect 'ab' union allselect 'ac' union allselect 'csd' union allselect 'csd' union allselect 'caa'select col from @T group by col having(count(1)>1)/*col----11abcsd*/
------解决方案--------------------
;with T as
(
select row_number() over (partition by col order by (select 1)) as rid,* from tbl
)
select distinct col from T where rid>1
/*
col
11
ab
csd
*/
------解决方案--------------------
先找出所有的记录,然后再group by ,如下:
- SQL code
/*标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2008-05-12地点：广东深圳*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values('001' , null , '广东省')insert into tb values('002' , '001' , '广州市')insert into tb values('003' , '001' , '深圳市')insert into tb values('004' , '002' , '天河区')insert into tb values('005' , '003' , '罗湖区')insert into tb values('006' , '003' , '福田区')insert into tb values('007' , '003' , '宝安区')insert into tb values('008' , '007' , '西乡镇')insert into tb values('009' , '007' , '龙华镇')insert into tb values('010' , '007' , '松岗镇')go--查询指定节点及其所有子节点的函数create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)asbegin declare @level int set @level = 1 insert into @t_level select @id , @level while @@ROWCOUNT > 0 begin set @level = @level + 1 insert into @t_level select a.id , @level from tb a , @t_Level b where a.pid = b.id and b.level = @level - 1 end returnendgo--调用函数查询001(广东省)及其所有子节点select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 001 NULL 广东省002 001 广州市003 001 深圳市004 002 天河区005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇（所影响的行数为 10 行）*/--调用函数查询002(广州市)及其所有子节点select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 002 001 广州市004 002 天河区（所影响的行数为 2 行）*/--调用函数查询003(深圳市)及其所有子节点select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 003 001 深圳市005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇（所影响的行数为 7 行）*/drop table tbdrop function f_cid