求一条SQL语句,谢谢大家!
class表
id count(detail表中,属于此类的记录数)
1 2
2 1
3 0
detail表
id classid(与class表关联)
1 1
2 1
3 2
我想通过一条SQL语句或存储过程,
根据detail表中指定类的记录数,更新class表的count字段
[解决办法]
create trigger t
on detail
for insert,update,delete
as
update class set count = b.c
from class a
inner join (select classid,count(*) as c from detail group by classid)b
on a.id = b.classid
[解决办法]
根据detail表中指定类的记录数
是什么意思啊？
[解决办法]
create table class(id int, [count] int)
insert class select 1, null
insert class select 2, null
insert class select 3, null
create table detail(id int, classid int)
insert detail select 1, 1
union all select 2, 1
union all select 3, 2
update class set [count]=isnull(B.[count], 0)
from class A
left join
(
select classid, [count]=count(*) from detail group by classid
)B on A.id=B.classid
select * from class
--result
id count
----------- -----------
1 2
2 1
3 0
(3 row(s) affected)
[解决办法]
where id in ( '1 ', '2 ', '3 ', '4 ')
