table1
班别编号 人数上限、
1 50
2 30
3 40
table2 班别 人员
1 张三
1 李四
1 王五
2 李1
2 李2
显示结果
班别 人数上限 剩余名额
1 50 47
2 30 28其中的47是50-3得到的这样的语句应该怎样写?
班别编号 人数上限、
1 50
2 30
3 40
table2 班别 人员
1 张三
1 李四
1 王五
2 李1
2 李2
显示结果
班别 人数上限 剩余名额
1 50 47
2 30 28其中的47是50-3得到的这样的语句应该怎样写?
A.班别编号 AS 班级,
A.人数上限
A.人数上限-B.cnt AS 剩余名额
FROM table1 AS A
JOIN(
SELECT
班级,
COUNT(*) AS cnt
FROM table2
GROUP BY 班级
) AS B
ON A.班别编号=B.班别
from table1 a
left join
(select 班别,count(班别) total from table2 ) b on b.班别=a.班别编号
from table1 a left join (
select 班级号,count(*) as con from table2 group by 班级号
)b on a.班级号=b.班级号
create table #t1
(
bjbh int
,rs int
)
insert #t1 select 1,50 union select 2,60 union select 3,50
create table #t2
(
bjbh int
,ry char(20)
)
insert #t2
select 1,'cc' union all select 1,'d'union all select 1,'f'union all select 1,'e'union all select 1,'d'
union all select 2,'d'union all select 2,'f'union all select 2,'e'union all select 2,'d'select bjbh,rs,rs-(select isnull(count(1),0) from #t2 where #t2.bjbh=#t1.bjbh) from #t1
/*
bjbh rs
----------- ----------- -----------
1 50 45
2 60 56
3 50 50(3 row(s) affected)*/
drop table #t1,#t2
select m.* , 剩余名额 = 人数上限 - isnull((select count(*) from table2 n where n.班别 = m.班别),0) from table1 m
go
create table [table1]([班别编号] int,[人数上限] int)
insert [table1]
select 1,50 union all
select 2,30 union all
select 3,40
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([班别] int,[人员] varchar(4))
insert [table2]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'李1' union all
select 2,'李2'select * from [table1]
select * from [table2]select b.[班别],a.[人数上限],[剩余名额]=a.[人数上限]-count(*)
from table1 a join table2 b
on a.[班别编号]=b.[班别]
group by b.[班别],a.[人数上限]
--测试结果:
/*
班别 人数上限 剩余名额
----------- ----------- -----------
1 50 47
2 30 28(2 行受影响)
*/
go
create table [table1]([班别编号] int,[人数上限] int)
insert [table1]
select 1,50 union all
select 2,30 union all
select 3,40
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([班别] int,[人员] varchar(4))
insert [table2]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'李1' union all
select 2,'李2'select m.* , 剩余名额 = 人数上限 - isnull((select count(*) from table2 n where n.班别 = m.班别编号),0) from table1 mdrop table table1,table2/*
班别编号 人数上限 剩余名额
----------- ----------- -----------
1 50 47
2 30 28
3 40 40(所影响的行数为 3 行)
*/
SELECT T1.班别, T1.人数上限, T1.人数上限 - T2.已有人数 AS 剩余名额
FROM Table1 T1
INNER JOIN (
SELECT 班别,COUNT(*) AS 已有人数
FROM Table2
GROUP BY 班别
) T2 ON T1.班别 = T2.班别
from table1 a left join
(
select 班别,count(*) as con from table2 group by 班别
) b
on a.班别编号=b.班别
A.班别编号 AS 班级,
A.人数上限
A.人数上限-B.cnt AS 剩余名额
FROM table1 A,
(
SELECT
班级,
COUNT(*) AS cnt
FROM table2
GROUP BY 班级
) B
where A.班别编号=B.班级
班别编号 as 班别,
人数上限,
人数上限-(select count(人员) from table2 where 班别=a.班别编号) as 剩余名额
from table1 a
group by 班别编号,人数上限