目前数据库有a和b列,值如下
a b
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]现在想一次性删除@后面不一致的记录,也就是a和b的记录里面@后台的要一致,不一致的就删除,SQL怎么写?
a b
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]现在想一次性删除@后面不一致的记录,也就是a和b的记录里面@后台的要一致,不一致的就删除,SQL怎么写?
where right(a,len(a)-charindex('@',a)<>right(b,len(b)-charindex('@',b)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(10),[b] varchar(10))
insert [tb]
select '[email protected]','[email protected]' union all
select '[email protected]','[email protected]' union all
select '[email protected]','[email protected]'
---删除---
delete from tb
where right(a,len(a)-charindex('@',a))<>right(b,len(b)-charindex('@',b))---查询---
select * from [tb]---结果---
a b
---------- ----------
[email protected] [email protected]
[email protected] [email protected](所影响的行数为 2 行)