id name sign
1 a 1
2 b 1
3 c 1
4 c 8
5 a 7
6 a 6获取sign只等于1的name数据(同一name不允许有多条数据,只取符合1的)
id name sign
2 b 1
1 a 1
2 b 1
3 c 1
4 c 8
5 a 7
6 a 6获取sign只等于1的name数据(同一name不允许有多条数据,只取符合1的)
id name sign
2 b 1
from ta
where sign = 1
order by newid() ?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[sign] int)
insert [tb]
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'c',8 union all
select 5,'a',7 union all
select 6,'a',6
---查询---
select *
from tb t
where (select count(1) from tb where name=t.name)=1---结果---
id name sign
----------- ---- -----------
2 b 1(所影响的行数为 1 行)
where not exists(select * from tb
where a.name=b.name and sign <> 1)
汗select *
from ta a
where not exists(select 1 from ta where name = a.name and sign !=1)
and sign = 1
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-17 17:00:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[sign] int)
insert [tb]
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'c',8 union all
select 5,'a',7 union all
select 6,'a',6
--------------开始查询--------------------------
select * from tb where name in (select name from tb group by name having count(1)=1)
----------------结果----------------------------
/*id name sign
----------- ---- -----------
2 b 1(1 行受影响)
*/
insert into tb values(1 , 'a' , 1 )
insert into tb values(2 , 'b' , 1 )
insert into tb values(3 , 'c' , 1 )
insert into tb values(4 , 'c' , 8 )
insert into tb values(5 , 'a' , 7 )
insert into tb values(6 , 'a' , 6 )
GOSELECT * FROM TB where sign = 1 and name in (select name from tb group by name having count(1) = 1)DROP TABLE TB/*
id name sign
----------- ---------- -----------
2 b 1(所影响的行数为 1 行)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-17 17:00:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[sign] int)
insert [tb]
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'c',8 union all
select 5,'a',7 union all
select 6,'a',6
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select * from tb where name=t.name and [sign] <> 1)
----------------结果----------------------------
/*id name sign
----------- ---- -----------
2 b 1(1 行受影响)
*/
只要有一个sign的
go
create table [tb]([id] int,[name] varchar(1),[sign] int)
insert [tb]
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'c',8 union all
select 5,'a',7 union all
select 6,'a',6
go
select *
from tb k
where
not exists(select * from tb where k.name=name and k.id<>id or k.name=name and sign<>1)
---查询---
select *
from tb t
where (select count(1) from tb where name=t.name)=1
and sign=1---结果---
id name sign
----------- ---- -----------
2 b 1(所影响的行数为 1 行)
select * from tb t where sign=1 and
(select count(1) from tb where name=t.name)=1
就是name在表中只出现一次的? 4楼
use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([id] INT,[name] varchar(50),[sign] INT)
insert into tb
select 1,'a',1 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'c',8 union all
select 5,'a',7 union all
select 6,'a',6
goselect * from tb t
where not exists(select * from tb where t.name = name and sign <> 1)