select * from A where A.lessonid='002' and A.user = (select B.user from B where B.lessonid='003')
select [user] from #t A,(select [user] user1 from #t A where A.lessonid='003') d where A.lessonid='002' and [user] =user1
select [user] from A where A.lessonid='002' and exists (select 1 from A b where b.lessonid='003' and a.[user]=b.[user])
select User from 表 where lesson_name='数学' or lesson_name='化学' group by user having count(User)=2lessonid lesson_name user 001 语文 a 002 数学 b 002 数学 a 003 化学 c 004 物理 d 003 化学 b
create table stu ( id int, name varchar(10) )insert into stu values(1,'A') insert into stu values(2,'A') insert into stu values(1,'B') insert into stu values(1,'C') insert into stu values(2,'C') insert into stu values(3,'D') insert into stu values(1,'D')select count(*) ,name from stu where id in(1,2) group by name having count(*)=2
select A.* from Table_A A, ( select * from Table_A where lesson_name='数学' ) M, ( select * from Table_A where lesson_name='化学' ) H where M.[user]=H.[user] and A.[user]=H.[user] and A.[user]=M.[user]
create table stu ( id int, name varchar(10) )insert into stu values(1,'A') insert into stu values(2,'A') insert into stu values(1,'B') insert into stu values(1,'C') insert into stu values(2,'C') insert into stu values(3,'D') insert into stu values(1,'D')F1:select count(*) ,name from stu where id in(1,2) group by name having count(*)=2F2:select a.* from stu a,stu b where a.name=b.name and a.id = 1 and b.id = 2
F3:select name from stu where id = 1 and name in (select name from stu where id = 2)
select distict name from table as a, table as b where a.lessonname="shuxue" and b.lessonname="huaxue" and a.name=b.name
F2: 不对,假如a 两个都是1 或者两个都是 2列 很明显有bug select name from stu where id = 1 and name in (select name from stu where id = 2)
可以考虑用Case语句将行变成列,然后组合条件
select user from class where lesson_name="化學" and user in(select user from class where lesson_name="數學");
create table test ( lessonid varchar(10), lesson_name varchar(20), us varchar(5) )insert into test values('001','语文','a') insert into test values('002','数学','b') insert into test values('002','数学','a') insert into test values('003','化学','c') insert into test values('004','物理','d') insert into test values('003','化学','b')select us from test where us in(select us from test where lessonid='002') and us in(select us from test where lessonid='003') group by us结果: us b
declare @t table(lessonid char(3),lesson_name char(4),[user] char(1))insert @t select '001', '语文', 'a' union all select '002', '数学' , 'b' union all select '002' , '数学' , 'a' union all select '003' , '化学' , 'c' union all select '004' , '物理' , 'd' union all select '003' , '化学' , 'b' select [user] from @t as a where lesson_name = '数学' and exists (select 1 from @t as b where b.lesson_name = '化学' and a.[user] = b.[user])
declare @t table(lid varchar(3),nid varchar(10), stu varchar(4)) insert @t select '001' , '语文' , 'a' union all select '002' , '数学' , 'b' union all select '002' , '数学' , 'a' union all select '003' , '化学' , 'c' union all select '004' , '物理' , 'd' union all select '003' , '化学' , 'b' select t.lid ,t.nid,t.stu from(select *from @t where nid='数学') t join (select *from @t where nid='化学') m on t.stu=m.stu
楼上有兄弟说了,最简单的就是用having了!
恩..不过好像先做条件判断之才count了. select t.user from (select username,lessionid,lession_name from table where lessionid in (002,003)) as t group by user having count(*) = 2 办法比较纯.多包含
select user from a A,a B where a.lessonid='002' and B.lessionid='003' and B.user=A.user 这句话看看
select username from A where lesson_name in ('數學','化學') group by username having count(1)>1
lessonid lesson_name user 001 语文 a 002 数学 b 002 数学 a 003 化学 c 004 物理 d 003 化学 b 找出既修了数学又修了化学的人是查询语句吗? 我是这么写的: select user from 表名 where lesson_name='数学' and lesson_name='化学'
create table #t ( lessonid char(3), lesson_name varchar(10), [user] varchar(10) ) insert into #t (lessonid,lesson_name,[user]) select '001' , '语文' , 'a' union all select '002' , '数学' , 'b' union all select '002' , '数学' , 'a' union all select '003' , '化学' , 'c' union all select '004' , '物理' , 'd' union all select '003' , '化学' , 'b'--select * from #T select distinct a.[user] from #t a left join #t b on (a.[user]=b.[user]) where ((a.lessonid='002' and b.lessonid='003') or (a.lessonid='003' and b.lessonid='002')) and a.lessonid is not null and b.lessonid is not nulldrop table #t
--下列方法更好: create table #t ( lessonid char(3), lesson_name varchar(10), [user] varchar(10) ) insert into #t (lessonid,lesson_name,[user]) select '001' , '语文' , 'a' union all select '002' , '数学' , 'b' union all select '002' , '数学' , 'a' union all select '003' , '化学' , 'c' union all select '004' , '物理' , 'd' union all select '003' , '化学' , 'b'--select * from #Tselect [user] from #t where lessonid='002' and [user] in (select [user] from #t where lessonid='003') drop table #t
--总发一下吧,以本贴为准 create table #t ( lessonid char(3), lesson_name varchar(10), [user] varchar(10) ) insert into #t (lessonid,lesson_name,[user]) select '001' , '语文' , 'a' union all select '002' , '数学' , 'b' union all select '002' , '数学' , 'a' union all select '003' , '化学' , 'c' union all select '004' , '物理' , 'd' union all select '003' , '化学' , 'b'--方法一: select distinct [user] from #t where lessonid='002' and [user] in (select [user] from #t where lessonid='003') --方法二: select distinct a.[user] from #t a,#t b where a.[user]=b.[user] and a.lessonid = '002' and b.lessonid = '003' drop table #t
select [user] from 表 where lesson_name='化学' and [user] in (select [user] from 表 where lesson_name='数学')
from A
where A.lessonid='002'
and A.user = (select B.user from B where B.lessonid='003')
where A.lessonid='002'
and [user] =user1
and exists (select 1 from A b where b.lessonid='003' and a.[user]=b.[user])
where lesson_name='数学' or lesson_name='化学'
group by user
having count(User)=2lessonid lesson_name user
001 语文 a
002 数学 b
002 数学 a
003 化学 c
004 物理 d
003 化学 b
(
id int,
name varchar(10)
)insert into stu values(1,'A')
insert into stu values(2,'A')
insert into stu values(1,'B')
insert into stu values(1,'C')
insert into stu values(2,'C')
insert into stu values(3,'D')
insert into stu values(1,'D')select count(*) ,name from stu
where id in(1,2)
group by name
having count(*)=2
from Table_A A,
(
select * from Table_A where lesson_name='数学'
) M,
(
select * from Table_A where lesson_name='化学'
) H
where M.[user]=H.[user]
and A.[user]=H.[user]
and A.[user]=M.[user]
(
id int,
name varchar(10)
)insert into stu values(1,'A')
insert into stu values(2,'A')
insert into stu values(1,'B')
insert into stu values(1,'C')
insert into stu values(2,'C')
insert into stu values(3,'D')
insert into stu values(1,'D')F1:select count(*) ,name from stu
where id in(1,2)
group by name
having count(*)=2F2:select a.*
from stu a,stu b
where a.name=b.name
and a.id = 1 and b.id = 2
from stu
where id = 1
and name in (select name from stu where id = 2)
where a.lessonname="shuxue" and b.lessonname="huaxue" and a.name=b.name
很明显有bug
select name
from stu
where id = 1
and name in (select name from stu where id = 2)
(
lessonid varchar(10),
lesson_name varchar(20),
us varchar(5)
)insert into test values('001','语文','a')
insert into test values('002','数学','b')
insert into test values('002','数学','a')
insert into test values('003','化学','c')
insert into test values('004','物理','d')
insert into test values('003','化学','b')select us from test
where us in(select us from test where lessonid='002')
and us in(select us from test where lessonid='003')
group by us结果:
us
b
declare @t table(lessonid char(3),lesson_name char(4),[user] char(1))insert @t select '001', '语文', 'a'
union all select '002', '数学' , 'b'
union all select '002' , '数学' , 'a'
union all select '003' , '化学' , 'c'
union all select '004' , '物理' , 'd'
union all select '003' , '化学' , 'b'
select [user] from @t as a where lesson_name = '数学'
and exists (select 1 from @t as b where b.lesson_name = '化学' and a.[user] = b.[user])
insert @t select '001' , '语文' , 'a'
union all select '002' , '数学' , 'b'
union all select '002' , '数学' , 'a'
union all select '003' , '化学' , 'c'
union all select '004' , '物理' , 'd'
union all select '003' , '化学' , 'b'
select t.lid ,t.nid,t.stu from(select *from @t where nid='数学') t join (select *from @t where nid='化学') m on t.stu=m.stu
楼上有兄弟说了,最简单的就是用having了!
select t.user from (select username,lessionid,lession_name from table where lessionid in (002,003)) as t group by user having count(*) = 2
办法比较纯.多包含
where lesson_name in ('數學','化學')
group by username having count(1)>1
001 语文 a
002 数学 b
002 数学 a
003 化学 c
004 物理 d
003 化学 b
找出既修了数学又修了化学的人是查询语句吗?
我是这么写的:
select user from 表名 where lesson_name='数学' and lesson_name='化学'
(
lessonid char(3),
lesson_name varchar(10),
[user] varchar(10)
)
insert into #t
(lessonid,lesson_name,[user])
select '001' , '语文' , 'a'
union all select '002' , '数学' , 'b'
union all select '002' , '数学' , 'a'
union all select '003' , '化学' , 'c'
union all select '004' , '物理' , 'd'
union all select '003' , '化学' , 'b'--select * from #T
select distinct a.[user]
from #t a left join #t b
on (a.[user]=b.[user])
where ((a.lessonid='002' and b.lessonid='003') or (a.lessonid='003' and b.lessonid='002'))
and a.lessonid is not null and b.lessonid is not nulldrop table #t
create table #t
(
lessonid char(3),
lesson_name varchar(10),
[user] varchar(10)
)
insert into #t
(lessonid,lesson_name,[user])
select '001' , '语文' , 'a'
union all select '002' , '数学' , 'b'
union all select '002' , '数学' , 'a'
union all select '003' , '化学' , 'c'
union all select '004' , '物理' , 'd'
union all select '003' , '化学' , 'b'--select * from #Tselect [user]
from #t
where lessonid='002'
and [user] in (select [user] from #t where lessonid='003')
drop table #t
create table #t
(
lessonid char(3),
lesson_name varchar(10),
[user] varchar(10)
)
insert into #t
(lessonid,lesson_name,[user])
select '001' , '语文' , 'a'
union all select '002' , '数学' , 'b'
union all select '002' , '数学' , 'a'
union all select '003' , '化学' , 'c'
union all select '004' , '物理' , 'd'
union all select '003' , '化学' , 'b'--方法一:
select distinct [user]
from #t
where lessonid='002'
and [user] in (select [user] from #t where lessonid='003')
--方法二:
select distinct a.[user]
from #t a,#t b
where a.[user]=b.[user]
and a.lessonid = '002' and b.lessonid = '003'
drop table #t
from 表
where lesson_name='化学' and [user] in (select [user]
from 表
where lesson_name='数学')