sql server 2000中这样的视图如何修改?
CREATEVIEWdbo.lis_jkmzASSELECTA.CFPHASMZLSH,A.CSFDASJHPTSQDH,A.CZXKSBMASJCKSBH,A.IKSA...
CREATE VIEW dbo.lis_jkmz
AS
SELECT A.CFPH AS MZLSH, A.CSFD AS JHPTSQDH, A.CZXKSBM AS JCKSBH,
A.IKS AS SJKS, A.DJZRQ AS SQSJ, A.CXM AS XM, B.IBM AS XB, A.CNL AS NL,
A.IYS AS SJYS, A.CXMBM AS JCXM, A.CMZH AS BLH, A.CYLH AS BZ,
A.CYBH AS LCZD, A.BSH AS dcbz
FROM yxyydb2011.dbo.TBMZFYMX201111 A INNER JOIN
dbo.TBZDXB B ON A.CXB = B.CMC
WHERE (A.CXMBM IN
(SELECT cbm
FROM tbzdsfxmmz
WHERE ifybm = '10' OR
ifybm = '9' OR
ifybm = '5' OR
ifybm = '11' OR
ifybm = '12' OR
ifybm = '37' OR
ifybm = '41')) AND (A.BTF = '0')
这是创建视图的语句,其中的表TBMZFYMX201111的名字是由字符:TBMZFYMX+年(2011)+月(11)组成。这样的语句如何写? 展开
AS
SELECT A.CFPH AS MZLSH, A.CSFD AS JHPTSQDH, A.CZXKSBM AS JCKSBH,
A.IKS AS SJKS, A.DJZRQ AS SQSJ, A.CXM AS XM, B.IBM AS XB, A.CNL AS NL,
A.IYS AS SJYS, A.CXMBM AS JCXM, A.CMZH AS BLH, A.CYLH AS BZ,
A.CYBH AS LCZD, A.BSH AS dcbz
FROM yxyydb2011.dbo.TBMZFYMX201111 A INNER JOIN
dbo.TBZDXB B ON A.CXB = B.CMC
WHERE (A.CXMBM IN
(SELECT cbm
FROM tbzdsfxmmz
WHERE ifybm = '10' OR
ifybm = '9' OR
ifybm = '5' OR
ifybm = '11' OR
ifybm = '12' OR
ifybm = '37' OR
ifybm = '41')) AND (A.BTF = '0')
这是创建视图的语句,其中的表TBMZFYMX201111的名字是由字符:TBMZFYMX+年(2011)+月(11)组成。这样的语句如何写? 展开
2个回答
2011-11-12
展开全部
是当前的年,月吗?
更多追问追答
追问
年可以手工修改,只是月要自动生成
追答
给你个例子,自己改吧,你的我这无法测试
declare @tableName as varchar(50)
set @tableName = 'TBMZFYMX2011'+ cast(month(getDate()) as varchar(10))
declare @sql as nvarchar(2000)
set @sql = N'CREATE VIEW dbo.lis_jkmz as select * from ' + @tableName
EXEC sp_executesql @sql
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
你想改成什么样啊?再就是,join最好用on 条件,在里边处理效率高。
追问
就是只要执行语句就可以生成视图并且表的名字是由字符:TBMZFYMX+年(2011)+当月(小于10要加0)组成
追答
你要在procedure里动态生成view吗?脚本跟sql的什么版本关系不大。如果是的话,可以这样:
create procedure xxx
as
begin
decare @s varChar(50), @s2 varChar(50), @sql varChar(4000)
select @s='TBMZFYMX'+convert(varChar(20),year(getDate())), @s2=convert(varChar(50),month(getDate()))
if len(@s2)
'
--执行创建出视图
exec(@sql)
end
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询
广告 您可能关注的内容 |