求一条语句

Question

数据结构:
name       shuxing   date
lixiao     0(IPV6)   2011-01-01
lixiao     0(IPV4)   2011-01-01
lixiao     0         2011-01-01susan     20(IPV6)   2011-01-01
susan     20(IPV4)   2011-01-01
susan     20         2011-01-01sz     0+max(ss-2000)(IPV6)   2011-01-01
sz     0+max(ss-2000)(IPV4)   2011-01-01
sz     0+max(ss-2000)         2011-01-01
如何删除日期为2011-01-01号的,且shuxing的值里不包含(IPV6),(IPV4)的记录,我要的结果是:name       shuxing   date
lixiao     0(IPV6)   2011-01-01
lixiao     0(IPV4)   2011-01-01susan     20(IPV6)   2011-01-01
susan     20(IPV4)   2011-01-01sz     0+max(ss-2000)(IPV6)   2011-01-01
sz     0+max(ss-2000)(IPV4)   2011-01-01

Answer 1

delete from [TABLE] where DATEDIFF (day,[date],'2011-01-01')=0 and shuxing 
not like '%(IPV6)%' and shuxing not like '%(IPV4)%'

Answer 2

select * from tableName where date='2011-01-01' and (shuxing like '%(IPV6)%' or shuxing like '%(IPV4)%'

Answer 3

delete from tableName where date='2011-01-01' and shuxing not like '%(IPV6)%' and shuxing not like '%(IPV4)%'

Answer 4

写错，应该是：delete from [TABLE] where DATEDIFF (day,[date],'2011-01-01')=0 and （shuxing 
not like '%(IPV6)%' or shuxing not like '%(IPV4)%'）

Answer 5

use tempdb;
/*
create table test
(
name nvarchar(10) not null,
shuxing nvarchar(50) not null,
[date] date not null
);
insert into test(name,shuxing,[date])
values
('lixiao','0(IPV6)','2011-01-01'),
('lixiao','0(IPV4)','2011-01-01'),
('lixiao','0','2011-01-01'),
('susan','20(IPV6)','2011-01-01'),
('susan','20(IPV4)','2011-01-01'),
('susan','20','2011-01-01'),
('sz','0+max(ss-2000)(IPV6)','2011-01-01'),
('sz','0+max(ss-2000)(IPV4)','2011-01-01'),
('sz','0+max(ss-2000)','2011-01-01');
*/
delete
from test
where [date] = '2011-01-01'
and shuxing not like '%(IPV6)%'
and shuxing not like '%(IPV4)%';