如果只是要查出结果:if exists(select * from sysobjects where id = object_id('proc_GetGazetteInfo'))
drop proc proc_GetGazetteInfo
gocreate proc proc_GetGazetteInfo
as
select top 5 gongbao_type,gongbao_name,magazine_btime,a.magazine_id ,a.cnt
from (select T1.magazine_id,(select count(zhuanli_id) from t_zhuanli where magazine_id=T1.magazine_id) Cnt
from (select top 5 magazine_id from t_magazine) T1
) a,t_gongbao b where a.gongbao_id=b.gongbao_id
gongbao_type gongbao_name magazine_btime magazie_id -----注:应该还是可以优化go
如果要问存储过成立建视图:if exists(select * from sysobjects where id = object_id('proc_GetGazetteInfo'))
drop proc proc_GetGazetteInfo
gocreate proc proc_GetGazetteInfo
as
exec('create view zhuanli_count as
select t1.magazine_id,(select count(zhuanli_id) from t_zhuanli where magazine_id=T1.magazine_id) Cnt from (select top 5 magazine_id from t_magazine) T1 ')go
drop proc proc_GetGazetteInfo
gocreate proc proc_GetGazetteInfo
as
select top 5 gongbao_type,gongbao_name,magazine_btime,a.magazine_id ,a.cnt
from (select T1.magazine_id,(select count(zhuanli_id) from t_zhuanli where magazine_id=T1.magazine_id) Cnt
from (select top 5 magazine_id from t_magazine) T1
) a,t_gongbao b where a.gongbao_id=b.gongbao_id
gongbao_type gongbao_name magazine_btime magazie_id -----注:应该还是可以优化go
如果要问存储过成立建视图:if exists(select * from sysobjects where id = object_id('proc_GetGazetteInfo'))
drop proc proc_GetGazetteInfo
gocreate proc proc_GetGazetteInfo
as
exec('create view zhuanli_count as
select t1.magazine_id,(select count(zhuanli_id) from t_zhuanli where magazine_id=T1.magazine_id) Cnt from (select top 5 magazine_id from t_magazine) T1 ')go
还有用你写的在查询分析其中有错误提示:
create proc proc_GetGazetteInfo
as
select top 5 gongbao_type,gongbao_name,magazine_btime,a.magazine_id ,a.cnt
from (select T1.magazine_id,(select count(zhuanli_id) from t_zhuanli where magazine_id=T1.magazine_id) Cnt
from (select top 5 magazine_id from t_magazine) T1
) a,t_gongbao b where a.gongbao_id=b.gongbao_id
-----注:应该还是可以优化go
错误提示为:Server: Msg 207, Level 16, State 3, Procedure proc_GetGazetteInfo, Line 4
列名 'magazine_btime' 无效。
Server: Msg 207, Level 16, State 1, Procedure proc_GetGazetteInfo, Line 4
列名 'gongbao_id' 无效。