create view view2 as
select CEOCount=( select count(1) from view1 where Position='ceo') ,
ExecuteCount= (select count(1) from view1 where Position='Execute') ......
select CEOCount=( select count(1) from view1 where Position='ceo') ,
ExecuteCount= (select count(1) from view1 where Position='Execute') ......
解决方案 »
- 判断字符的问题,如下
- 收集SQL2000安装问题
- 请问高手们一个问题,先谢谢了 !
- 这样的数据库功能应该如何实现?求救!!
- [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server 不存在或访问被拒绝
- 请指导一个关联查询,谢谢!
- 弱弱的问一句,能用exist实现而不是not exist 么?
- 数据库被还原,能否再回复到以前的数据
- 怎么复制表的结构?????
- mina 卡死在mina卡死在cf.getSession().getCloseFuture().awaitUninterruptibly()
- 关于distinct的问题
- 挺无聊的一个问题
sum(case when position='Execute' then 1 end) as ExecuteCount,
sum(case when position='Support' then 1 end) as SupportCount,
sum(case when position='Support' then 1 end) as ManagerCount
from view
create view view_name
as
select
CEOCount=sum(case Position when 'CEO' then 1 else 0 end),
ExecuteCount=sum(case Position when 'xecute' then 1 else 0 end),
SupportCount=sum(case Position when 'Support' then 1 else 0 end),
ManagerCount=sum(case Position when 'Manager' then 1 else 0 end)
from [view]
--否则的话要用动态SQL语句,即写成存储过程--如果 Position 固定,则可以写出view
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Position+'count]=sum(case Position when '''+Position+''' then 1 else 0 end)'
from [view]
group by Position
set @s=stuff(@s,1,1,'')
exec('select'+@s+' from [view]')
set @s=''
select @s=@s+',['+职位名称+'count]=sum(case 职位编号 when '''+cast(职位编号 as varchar)+''' then 1 else 0 end)'
from 职位名称表set @s=stuff(@s,1,1,'')
exec('select'+@s+' from 视图')
ID PositionName PositionCount
1 CEO 1
2 Manager 16
3 Support 5
4 Execute 20
问题是现在怎样能实现这个View呢?