name address
a aaa
a bbb
c ccc
d ddd
e eee
e fff
e bbb如果address有值等于bbb,则bbb这条数据名字相等的数据都不显示上面要结果为
name address
c ccc
d ddd
a aaa
a bbb
c ccc
d ddd
e eee
e fff
e bbb如果address有值等于bbb,则bbb这条数据名字相等的数据都不显示上面要结果为
name address
c ccc
d ddd
解决方案 »
- SQL语句不会写。
- 请教--数据库联接不上
- 选择一张表中间的10个字段(第11到第20)的SQL语句怎么写??
- 如果将EXCEL的记录导入到MS-SQL Servce?
- sql server2000能与access自动抛转数据的工具
- 把select 随机排序的结果保存在存储过程中的临时表中
- sql语句求助
- sql语句单引号?,双引号?
- 安装oracle personal edition,为什么ORA-12541: TNS:no listener?
- 使用U8执行某个功能,如何查看执行了哪些SQL语句,或者存储过程(在线等)
- 求sql2000中pubs 数据库中authors表结构
- SQL查询中字符转数字问题。。。
from tb t
where not exists(select 1 from tb where name=t.name and address='bbb')
--> Author : wufeng4552
--> Date : 2010-02-23 11:26:37
if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(1),[address] nvarchar(3))
Insert tb
select N'a',N'aaa' union all
select N'a',N'bbb' union all
select N'c',N'ccc' union all
select N'd',N'ddd' union all
select N'e',N'eee' union all
select N'e',N'fff' union all
select N'e',N'bbb'
Go
Select * from tb t
where not exists(select 1 from tb where name=t.name and [address]='bbb')
/*
name address
---- -------
c ccc
d ddd(2 個資料列受到影響)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[address] varchar(3))
insert [tb]
select 'a','aaa' union all
select 'a','bbb' union all
select 'c','ccc' union all
select 'd','ddd' union all
select 'e','eee' union all
select 'e','fff' union all
select 'e','bbb'
---查询---
select *
from tb t
where not exists(select 1 from tb where name=t.name and address='bbb')---结果---
name address
---- -------
c ccc
d ddd(所影响的行数为 2 行)
Select * from tb t
where NAME NOT IN (select NAME from tb where [address]='bbb')
create table #tt
(name varchar(5),
address varchar(5))insert #tt
select 'a','aaa' union all
select 'a','bbb' union all
select 'c','ccc' union all
select 'd','ddd' union all
select 'e','eee' union all
select 'e','fff' union all
select 'e','bbb' select * from #tt a
where not exists(select 1 from #tt b where a.name=b.name and b.address='bbb')
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[address] varchar(3))
insert [tb]
select 'a','aaa' union all
select 'a','bbb' union all
select 'c','ccc' union all
select 'd','ddd' union all
select 'e','eee' union all
select 'e','fff' union all
select 'e','bbb'
Select * from tb t
where NAME NOT IN (select NAME from tb where [address]='bbb')
(所影响的行数为 7 行)name address
---- -------
c ccc
d ddd(所影响的行数为 2 行)
select 'a','aaa' union all
select 'a','bbb' union all
select 'c','ccc' union all
select 'd','ddd' union all
select 'e','eee' union all
select 'e','fff' union all
select 'e','bbb'select [name],address from @myTable t where not exists
(select 1 from @myTable where t.[name]=[name] and address = 'bbb')