这样?? select * from tb where left(cast(id_card as char(40),2)='12'
select *,left(id_card,2) from tablename where left(id_card,2)=left(id_card,2)
select * from table_name where left(id_card,2) in ( select left(id_card,2) from table_name group by left(id_card,2) having count(*)>1 )
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (emp_name varchar(11),id_card bigint) insert into #T select '默默',12000000 union all select '默默1',450000000000 union all select '默默2',120000000select * from #T as t where exists (select 1 from #T where id_card<>t.id_card and left(id_card,2)=left(t.id_card,2))/* emp_name id_card ----------- -------------------- 默默 12000000 默默2 120000000 */
select a.* from tb a where exists(select * from tb where emp_name<>a.emp_name and left(id_card,2)=left(a.id_card,2))
--> --> (Ben)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([emp_name] nvarchar(3),[id_card] varchar(15)) Insert #T select '默默',12000000 union all select '默默1',450000000000 union all select '默默2',120000000 Go select * from #T a,#T b where substring(a.id_card,1,2)=substring(b.id_card,1,2) and a.id_card<>b.id_card and a.emp_name<b.emp_name
select a.* from tb a ,tb b where substring(b.id_card,1,2)=substring(a.id_card,1,2) and a.emp_name <>b.emp_name
--> --> (Ben)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([emp_name] nvarchar(3),[id_card] varchar(15)) Insert #T select '默默',12000000 union all select '默默1',450000000000 union all select '默默2',120000000 union all select '2默2','120000' Go select distinct a.* from #T a,#T b where substring(a.id_card,1,2)=substring(b.id_card,1,2) and a.id_card<>b.id_card
select a.* from tb a ,tb b where substring(b.id_card,1,2)=substring(a.id_card,1,2) and a.emp_name <>b.emp_name 你这个语句是把相同数字开头的排列在一起了,我现在是只要筛选出相同一组来就可以,怎么写呀,谢谢
Select emp_name,id_card from pperson where left(id_card,2)=12 出来了,大家把问题想复杂了,说了偶是菜鸟,呵呵
select * from tb where left(cast(id_card as char(40),2)='12'
(
select left(id_card,2) from table_name group by left(id_card,2) having count(*)>1
)
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (emp_name varchar(11),id_card bigint)
insert into #T
select '默默',12000000 union all
select '默默1',450000000000 union all
select '默默2',120000000select * from #T as t where exists (select 1 from #T where id_card<>t.id_card and left(id_card,2)=left(t.id_card,2))/*
emp_name id_card
----------- --------------------
默默 12000000
默默2 120000000
*/
from tb a
where exists(select * from tb where emp_name<>a.emp_name and left(id_card,2)=left(a.id_card,2))
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([emp_name] nvarchar(3),[id_card] varchar(15))
Insert #T
select '默默',12000000 union all
select '默默1',450000000000 union all
select '默默2',120000000
Go
select * from #T a,#T b
where substring(a.id_card,1,2)=substring(b.id_card,1,2) and a.id_card<>b.id_card
and a.emp_name<b.emp_name
我是说筛选出这样的效果
emp_name id_card
默默 12000000
默默1 120000670000
默默2 120008900 就是把前面两位相同数字的人名筛选出来
from tb a ,tb b
where substring(b.id_card,1,2)=substring(a.id_card,1,2)
and a.emp_name <>b.emp_name
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([emp_name] nvarchar(3),[id_card] varchar(15))
Insert #T
select '默默',12000000 union all
select '默默1',450000000000 union all
select '默默2',120000000 union all
select '2默2','120000'
Go
select distinct a.* from #T a,#T b
where substring(a.id_card,1,2)=substring(b.id_card,1,2) and a.id_card<>b.id_card
select a.*
from tb a ,tb b
where substring(b.id_card,1,2)=substring(a.id_card,1,2)
and a.emp_name <>b.emp_name 你这个语句是把相同数字开头的排列在一起了,我现在是只要筛选出相同一组来就可以,怎么写呀,谢谢
出来了,大家把问题想复杂了,说了偶是菜鸟,呵呵