查询前六位相同的值（注意必须有两行以上相同的才显示）SQL语句怎么写？

[w20011025]

查询前六位相同的值（注意必须有两行以上相同的才显示）SQL语句怎么写？
数据库查询内容如下：
192189101
192190056
192193580
192193644
192193722
192193833
193873699
查询结果如下：
192193580
192193644
192193722
192193833
SQL语句怎么写？

[dawugui]

[Quote=引用楼主 w20011025 的帖子:]
查询前六位相同的值（注意必须有两行以上相同的才显示）SQL语句怎么写？
数据库查询内容如下：
192189101
192190056
192193580
192193644
192193722
192193833
193873699
查询结果如下：
192193580
192193644
192193722
192193833
SQL语句怎么写？
[/Quote]

select * from tb where left(col,6) in (select left(col,6) from tb group by left(col,6) having count(1) > 1)

[肥龙上天]

declare @t table(code varchar(10))
insert into @t select '192189101' 
insert into @t select '192190056' 
insert into @t select '192193580' 
insert into @t select '192193644' 
insert into @t select '192193722' 
insert into @t select '192193833' 
insert into @t select '193873699' 

select * from @t a
where (select count(1) from @t where code like left(a.code,6)+'%') >1
/*
code
----------
192193580
192193644
192193722
*/

[肥龙上天]

/*code
----------
192193580
192193644
192193722
192193833

(4 row(s) affected)*/

[w20011025]

[Quote=引用 6 楼 zzxap 的回复:]
select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
[/Quote]
执行效率最高！！

[w20011025]

[Quote=引用 2 楼 libin_ftsafe 的回复:]
SQL codeselect * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
[/Quote]

分数给低拉！！！

[playwarcraft]

select A.*
from A
inner join
(
select left(字段,6) as col
from T
group by left(字段,6)
having count(*)>1
) B
on left(A.字段,6)=B.col

[子陌红尘]

select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)

[子陌红尘]

declare @t table(code varchar(10))
insert into @t select '192189101' 
insert into @t select '192190056' 
insert into @t select '192193580' 
insert into @t select '192193644' 
insert into @t select '192193722' 
insert into @t select '192193833' 
insert into @t select '193873699' 

select * from @t where left(code,6) in(select left(code,6) from @t group by left(code,6) having count(*)>1)
/*
code       
---------- 
192193580
192193644
192193722
192193833
*/

select * from @t t where exists(select 1 from @t where left(code,6)=left(t.code,6) and code!=t.code)
/*
code       
---------- 
192193580
192193644
192193722
192193833
*/

[chuifengde]

select * from [Table] a where (select count(1) from [Table] where left([Field],6)=left(a.[Field],6))>1

[ks_reny]

select * from 表 where 字段 in(select 字段 from 表 group by left(字段,6) having count(*)>1)

[zzxap]

select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 192189101 union all
select 192190056 union all
select 192193580 union all
select 192193644 union all
select 192193722 union all
select 192193833 union all
select 193873699
 
 
---查询---
select * 
from [tb]  t
where (select count(1) from tb where left(col,6)=left(t.col,6))>1

---结果---
col         
----------- 
192193580
192193644
192193722
192193833

（所影响的行数为 4 行）

[claro]

帮顶

[sdhdy]

select  * from tb a where (select count(*) from tb where left(col,6)=left(a.col,6) )>=2