数据表结构如下:表t1
字段:
id text
1 T
1 T
2 T
2 F
3 T
3 T
4 T
4 F要求:通过一个sql查询,查出所有text字段值都是T的id,如例子的查询结果应该是
1和3
字段:
id text
1 T
1 T
2 T
2 F
3 T
3 T
4 T
4 F要求:通过一个sql查询,查出所有text字段值都是T的id,如例子的查询结果应该是
1和3
解决方案 »
- SQL语句操作Excel无效
- 求一条SQL语句啊!
- 求一数据库 各国家名 和省名 英文的
- 如何把SQLServer的库或表导入到ACCESS中?
- 请问这个问题怎么解决?? 郁闷两天
- ***************我该装什么版本的sql2000啊 **********
- SQL与WinGate的冲突问题!!!
- 数据表字段被意外的修改
- 救命啊,谁帮帮忙,关于检索excel数据的问题
- ===请问怎样判断一个表是否为空表?===
- sql server 2005大概有多大?我看网上有300多M,500多M,600多M,1.2G,1.54G很多版本,我做开发的
- CPU-Z看到:指令集:MMX,SSE,SSE2,SSE3,SSSE3,EM64T ,cpu是intel E2200的,是32位还是64位的CPU?安装SQL Server2005的32位还是64
group by id
having(max(text)='T' and min(text)='T')
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[text] varchar(1))
insert [tb]
select 1,'T' union all
select 1,'T' union all
select 2,'T' union all
select 2,'F' union all
select 3,'T' union all
select 3,'T' union all
select 4,'T' union all
select 4,'F'select id from tb
group by id
having(max(text)='T' and min(text)='T')
/*
id
-----------
1
3(2 行受影响)
*/drop table tb
insert into t1 values(1 , 'T')
insert into t1 values(1 , 'T')
insert into t1 values(2 , 'T')
insert into t1 values(2 , 'F')
insert into t1 values(3 , 'T')
insert into t1 values(3 , 'T')
insert into t1 values(4 , 'T')
insert into t1 values(4 , 'F')
go
select distinct id from t1 where id not in (select id from t1 where [text] <> 'T')drop table t1/*id
-----------
1
3(所影响的行数为 2 行)
*/
select id from t1 where text ='t' group by id
select m.* from tb m where exists (select 1 from
(
select name , sex from tb group by name , sex having count(*) > 1
) n where n.name = m.name and n.sex = m.sex
)/*
name sex age
---------- ---------- -----------
A M 17
A M 17(所影响的行数为 2 行)
*/
发个例子看看
select distinct id from t1 where id not in (select id from t1 where [text] <> 'T')
select 1 ,'T' union all
select 1 ,'T' union all
select 2 ,'T' union all
select 2 ,'F' union all
select 3 ,'T' union all
select 3 ,'T' union all
select 4 ,'T' union all
select 4 ,'F'
---------------------------------
select id from Data
where [text]='T'
group by [text],id
having count(*)>1
---------------------------------
id
---------
1
3
select distinct id from Data where id not in (select id from Data where [text] <> 'T')
思路也很好。
select 1 ,'T' union all
select 1 ,'T' union all
select 2 ,'T' union all
select 2 ,'F' union all
select 3 ,'T' union all
select 3 ,'T' union all
select 4 ,'T' union all
select 4 ,'F'
-------------------------------------------------
select distinct id from Data
where id not in (select id from Data where [text] <> 'T')
-------------------------------------------------
id
---------
1
3