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

w20011025 2009-03-23 12:23:55
查询前六位相同的值(注意必须有两行以上相同的才显示)SQL语句怎么写?
数据库查询内容如下:
192189101
192190056
192193580
192193644
192193722
192193833
193873699
查询结果如下:
192193580
192193644
192193722
192193833
SQL语句怎么写?
...全文
380 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
sdhdy 2009-03-23
  • 打赏
  • 举报
回复
select  * from tb a where (select count(*) from tb where left(col,6)=left(a.col,6) )>=2
claro 2009-03-23
  • 打赏
  • 举报
回复
帮顶
百年树人 2009-03-23
  • 打赏
  • 举报
回复

---测试数据---
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 行)
zzxap 2009-03-23
  • 打赏
  • 举报
回复
select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
ks_reny 2009-03-23
  • 打赏
  • 举报
回复

select * from 表 where 字段 in(select 字段 from 表 group by left(字段,6) having count(*)>1)
chuifengde 2009-03-23
  • 打赏
  • 举报
回复
select * from [Table] a where (select count(1) from [Table] where left([Field],6)=left(a.[Field],6))>1
子陌红尘 2009-03-23
  • 打赏
  • 举报
回复

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
*/
子陌红尘 2009-03-23
  • 打赏
  • 举报
回复
select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
playwarcraft 2009-03-23
  • 打赏
  • 举报
回复
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
w20011025 2009-03-23
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 libin_ftsafe 的回复:]
SQL codeselect * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
[/Quote]

分数给低拉!!!
w20011025 2009-03-23
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 zzxap 的回复:]
select * from 表 where left(字段,6) in(select left(字段,6) from 表 group by left(字段,6) having count(*)>1)
[/Quote]
执行效率最高!!
肥龙上天 2009-03-23
  • 打赏
  • 举报
回复

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

(4 row(s) affected)*/
肥龙上天 2009-03-23
  • 打赏
  • 举报
回复
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
*/
dawugui 2009-03-23
  • 打赏
  • 举报
回复
[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)

22,210

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧