create table a (name varchar(20))create table b (tbname varchar(10),con int)
insert into b select 'a',0create trigger up_b on a
for insert
as
begin
declare @con int
select @con=count(*) from a
update b set con=@con where tbname='a'
endinsert into a select '1'
insert into a select '1'
insert into a select '1'
select * from btbname con
a 3
insert into b select 'a',0create trigger up_b on a
for insert
as
begin
declare @con int
select @con=count(*) from a
update b set con=@con where tbname='a'
endinsert into a select '1'
insert into a select '1'
insert into a select '1'
select * from btbname con
a 3
表user 表内username,type两列,username是用户名,type是权限。现在有有表type 表内有type权限名,不能重复有约束表user有数据admin 超级管理员
abc 超级管理员
test 普通会员表type有数据超级管理员
普通会员现在要求查询出一张虚拟表显示信息如下
权限名 会员数
超级管理员 2
普通会员 1
create table [user](
username varchar(10),
[type] varchar(20))insert [user] select 'admin', '超级管理员'
union all select 'abc', '超级管理员'
union all select 'test', '普通会员'create table [type](
[name] varchar(20))insert [type] select '超级管理员'
union all select '普通会员'
select
t.[name] as '权限名',
sum(case u.[type] when t.[name] then 1 else 0 end) as '会员数'
from
[user] u inner join [type] t
on u.[type] = t.[name]
group by
t.[name]
drop table [user]
drop table [type]/*
权限名 会员数
-------------------- -----------
超级管理员 2
普通会员 1
*/
insert into @user select 'admin','超级管理员'
insert into @user select 'abc','超级管理员'
insert into @user select 'test','普通会员'
declare @type table([type] varchar(20))
insert into @type select '超级管理员'
insert into @type select '普通会员'select b.type as '权限名',count(*) as '会员数' from @type a left join @user b on a.type=b.type
group by b.type