create table A
(
id int primary key identity(1,1),
sNo varchar(20)
)create table B
(
id int primary key identity(1,1),
sBNo varchar(20),
course varchar(50)
)insert into A values('A1')
insert into A values('A2')insert into B values('A1','english')
insert into B values('A1','chinese')
insert into B values('A1','jap')
insert into B values('A1','france')insert into B values('A2','english')
insert into B values('A2','chinese')
insert into B values('A2','jap')
select * from A
select * from B就是把select count(*) from B where sbNo = '?' 的数据结果联合到
A中
我要得到如下数据形式:
id sNo 课程数
1 A1 4
2 A2 3
select id,sNo,课程数 from A
left join
(select count(*) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
from a,b
where a.sno=b.sbno
group by a.id,a.sno
?
left join
(select count(1) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
(
id int primary key identity(1,1),
sNo varchar(20)
)create table B
(
id int primary key identity(1,1),
sBNo varchar(20),
course varchar(50)
)insert into A values('A1')
insert into A values('A2')insert into B values('A1','english')
insert into B values('A1','chinese')
insert into B values('A1','jap')
insert into B values('A1','france')insert into B values('A2','english')
insert into B values('A2','chinese')
insert into B values('A2','jap')
select id,sNo,课程数 from A
left join
(select SBNo,count(1) 课程数 from B group by sbNo) as T
on a.sNo=T.sbNo
/*
id SNo 课程数
--------------------
1 A1 4
2 A2 3
*/drop table A,B
left join
(select count(1) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
left join
(select count(*) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
left join
(select count(*) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
left join
(select count(*) 课程数 from B group by sbNo) as tt
on a.sNo=b.sbNo
from(
SELECT A.ID,A.SNO,B.COURSE
FROM A,B
WHERE A.SNO=B.SBNO
) as U
GROUP BY ID,SNO
这种方法先把表合并,再从中选择
[color=#99CC00]select *,(select count(1) from b where sbno = a.sno)as 课程数 from 这种方法是先选择A中的前两个字段再加上B中的后一个合计字段,但是必须有外键关系
-- 应该是分组查询
select a.*,t.counts from a
left join (select sbno,count(1) as counts from b group by sbno) t
on a.sno = t.sbno