问条sql 回复有分

[shuai45]

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

[-狙击手-]

select top 1 *
from ta
where sign = 1
order by newid() ?

[dawugui]

select * from tb where sign = 1

??

[SQL77]

SELECT * FROM TB T WHERE ID=(SELECT MAX(ID) FROM TB WHERE SIGN=T.SIGN) AND SING=1

[sgtzzc]

---测试数据---
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 行）

[vivian_lanlan]

select * from tb as a
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

[--小F--]

----------------------------------------------------------------
-- 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 行受影响) 
*/

[dawugui]

create table tb(id int, name varchar(10), sign int)
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 )
GO

SELECT * 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 行）

*/

[SQL77]

[Quote=引用 5 楼 vivian_lanlan 的回复:]
SQL codeselect*from tbas awherenotexists(select*from tbwhere a.name=b.nameandsign<>1)
[/Quote]
应该是这个意思,同一NAME不允许有相同

[--小F--]

----------------------------------------------------------------
-- 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 行受影响) 
*/

[hery2002]

.

[shuai45]

我的意思是说同一id 不允许有多个sign,
只要有一个sign的

[feixianxxx]

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
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)

[sgtzzc]

[Quote=引用 4 楼 sgtzzc 的回复:]
SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([id]int,[name]varchar(1),[sign]int)insert[tb]select1,'a',1unionallselect2,'b',1unionallselect3,'c',1unionallselect4,'c',8un?-
[/Quote]
加一个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 行）

[荷包蛋他娘]

....题目貌似有问题..

[lang071234]

select * from tb t where sign=1 and 
(select count(1) from tb where name=t.name)=1

[sgtzzc]

[Quote=引用 12 楼 shuai45 的回复:]
我的意思是说同一id 不允许有多个sign,
只要有一个sign的
[/Quote]

就是name在表中只出现一次的？ 4楼

[shuai45]

[Quote=引用 8 楼 dawugui 的回复:]
SQL codecreatetable tb(idint, namevarchar(10),signint)insertinto tbvalues(1 ,'a' ,1 )insertinto tbvalues(2 ,'b' ,1 )insertinto tbvalues(3 ,'c' ,1 )insertinto tbvalues(4 ,'c' ,8 )insertinto tbvalues(5 ?-
[/Quote]

每错,就这意思~~~ 牛

[smntbk]

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 
go

select * from tb t
where not exists(select * from tb where t.name = name and sign <> 1)