--是不是这样,就是在你的表里面选择只出现一行的行
select col1,col2,...,coln from yourtable group by col1,col2,...,coln having count(*)=1
select col1,col2,...,coln from yourtable group by col1,col2,...,coln having count(*)=1
select * from a t
where not exists( select * from a where NH<>t.NH
and ',,,'+NH+',,,' like '%,,,'+t.NH+',,,%')
and not exists(select * from a where NH<>t.NH
and ',,,'+t.NH +',,,'like '%,,,'+NH+',,,%')
FROM A M
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM A
WHERE NH <> M.NH
AND M.NH LIKE '%' + NH + '%'
)这样不行吗?
where not exists(select * from a where NH<>t.NH and NH like '%'+rtrim(t.NH)+'%')
and not exists(select * from a where NH<>t.NH and t.NH like '%'+rtrim(NH)+'%')再试试!
declare @a table(NH varchar(10))
insert into @a(NH)
select 'a' union all
select 'a1' union all
select 'a12' union all
select 'b' union all
select 'c' union all
select 'c1' union all
select 'c2'select * from @a t
where not exists(select * from @a where NH<>t.NH and (NH like '%'+t.NH+'%' or t.NH like '%'+NH+'%'))
就是一个字段。
nh char(10)
)insert a
select
'a'
union all select
'a1'
union all select
'a12'
union all select
'b'
union all select
'c'
union all select
'c1'
union all select
'c2' select * from a t
where not exists(select * from a where NH<>t.NH and NH like '%'+rtrim(t.NH)+'%')
and not exists(select * from a where NH<>t.NH and t.NH like '%'+rtrim(NH)+'%')--结果
/*
nh
----------
b (所影响的行数为 1 行)
*/
如果你的字段是varchar类型,pbsql(风云)原来的语句就是对的
用你创建的表就可以,查询我在SQLSERVER上手动创建的表就返回所有记录,不知为啥?
select * from a t
where not exists(select * from a where NH<>t.NH and NH like '%'+rtrim(t.NH)+'%')
and not exists(select * from a where NH<>t.NH and t.NH like '%'+rtrim(NH)+'%')实现了我想要的结果,看来是我少见多怪了 :)
SELECT *
FROM A M
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM A
WHERE NH <> M.NH
AND NH LIKE '%' + M.NH + '%'
)