设有表t(a,b,c),a可以有重复值请问2个SQL语句
1.找出含有2个以上(包括2个)c<10的a值 的SQL语句2.找出既包含b=1也包含b=2的a的值e.g:
a b c
------------
1 1 3
1 2 11
2 2 7
3 2 8
3 2 6
------------answer:
1. a=3
2. a=1(最好用mysql)
先谢过各位了
1.找出含有2个以上(包括2个)c<10的a值 的SQL语句2.找出既包含b=1也包含b=2的a的值e.g:
a b c
------------
1 1 3
1 2 11
2 2 7
3 2 8
3 2 6
------------answer:
1. a=3
2. a=1(最好用mysql)
先谢过各位了
where c<10 group by a having count(*)>=2第一个我初学第2个一句话好像写不出来
select a from table1
where b=1 and a in (select a from table1 where b=2 )
--1.
select t.a from t where t.c<10 group by t.a having count(*)>=2--2.第一个效率高,第二个效率比较低
select t1.a from t t1 join t t2 on t1.a=t2.a and t1.b=1 and t2.b=2select tab.a from(
select distinct(t.a) from t where t.b=1
union all
select distinct(t.a) from t where t.b=2
) tab group by tab.a having count(tab.a)>=2
第二个
select a from table1
where b=1 and a in (select a from table1 where b=2 )
------------------------------------------------------------------------------
另修改4楼方法,提高效率select t1.a from t t1 where t1.b=1 and exists (select 'x' from t t2 where t1.a=t2.a and t2.b=2)
select t.a from t where t.c<10 group by t.a having count(*)>=2--2.第一个效率高,第二个效率比较低
select t1.a from t t1 join t t2 on t1.a=t2.a and t1.b=1 and t2.b=2select tab.a from(
select distinct(t.a) from t where t.b=1
union all
select distinct(t.a) from t where t.b=2
) tab group by tab.a having count(tab.a)>=2
赞成楼上的说法 顶
select a from t
where c <10 group by a having count(*)> =2
第二题正解:select a from table1
where b=1 and a in(select a from table1 where b=2 )
2。
select distinct(a) from table where b in(1,2)
select a
from t
where c < 10
group by a
haing count(*) > 2(2)
select distinct t1.a
from t t1
where exists ( select *
from t t2
where t1.a = t2.a and
t2.b = 1
) and
exists ( select *
from t t3
where t1.a = t3.a and
t3.b = 2
)
(1)
select a
from t
where c < 10
group by a
having count(*) >= 2(2)
select distinct t1.a
from t as t1
where exists ( select *
from t as t2
where t1.a = t2.a and
t2.b = 1
) and
exists ( select *
from t as t3
where t1.a = t3.a and
t3.b = 2
)
1.select a from t where c<10 group by a having count(*)>=2;2.有3种写法:
一、select t1.a from t t1 join t t2 on t1.a=t2.a and t1.b=1 and t2.b=2;
二、select a from t where b=1 and a in (select a from t where b=2);
三、select t1.a from t t1 where t1.b=1 and exists (select t2.a from t t2 where t1.a=t2.a and t2.b=2);
==
select distinct(a1.a) from test a1,test a2 where a1.a=a2.a and a1.b=1 and a2.b=2