视图如何改为临时表+sp?

am2000 2003-10-16 11:54:02
现有一视图代码如下:
CREATE VIEW dbo.v_stock_month
AS
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............

SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size)
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount

主要用来查询某月每天的出入库台帐。
考虑到需要带年度和月份做参数,想改用sp+临时表实现,以提高性能和灵活性。
请各位大虾给个例子。
...全文
46 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2003-10-16
  • 打赏
  • 举报
回复
CREATE proc dbo.p_stock_month
@year int,
@month int
AS
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............

SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size) AND
stock.tz_year=@year and stock.tz_month=@month --加入条件
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
aierong 2003-10-16
  • 打赏
  • 举报
回复
我想到有2种办法
1。笨一点的办法
每个月的天数无非就是28,29,30,31四种
你可以把语句写成相应的4种,对应28,29,30,31
让后你可以判断该月份的天数是多少天,就调用哪个语句

这个语句可以判断该月份有多少天
declare @n datetime
select @n='20030911'
select datediff(day,@n-day(@n)+1,dateadd(mm,1,@n-day(@n)+1))

2。灵活一点的办法,
就是用动态语句,先判断该月有多少天,就生成多少天的查询语句
在利用EXECUTE(@SQL)动态运行查询
am2000 2003-10-16
  • 打赏
  • 举报
回复
谢谢!我现在想把sql语句通过循环组合起来。不需要一个个输入SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
之类的语句。直接用For i=1 TO 31 NEXT ,

还有。如果这个月没有31号,则不生成31号的记录。有何良策!
txlicenhe 2003-10-16
  • 打赏
  • 举报
回复
create procedure test @year int,@month int
As
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............

SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size)
and stock.tz_year = @year and stock.tz_month = @month
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
am2000 2003-10-16
  • 打赏
  • 举报
回复
OK!谢谢了!
zjcxc 2003-10-16
  • 打赏
  • 举报
回复
--改为动态生成SQL语句的方法用:

CREATE proc dbo.p_stock_month
@year int,
@month int
AS
declare @sql varchar(8000),@day int,@dd int

--得到要查询年月的天数
select @dd=day(dateadd(month,@month,dateadd(year,@year-1900,'1999-01-01'))-1)
,@day=1
,@sql='
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount'
while @day<=@dd
select @sql=@sql+',SUM(CASE WHEN day(v_book.date)='+cast(@day as varchar)
+' AND (v_book.lb=''02'' OR v_book.lb=''01'') THEN Amount ELSE 0 END) AS rk_'
+cast(@day as varchar)+',SUM(CASE WHEN day(v_book.date)='+cast(@day as varchar)
+' AND (v_book.lb=''10'' OR v_book.lb=''11'') THEN Amount ELSE 0 END) AS ck_'
+cast(@day as varchar)
,@day=@day+1

set @sql=@sql+' FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size) AND
stock.tz_year='+cast(@year as varchar)
+' and stock.tz_month='+cast(@month as varchar)+'
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount'
exec(@sql)
am2000 2003-10-16
  • 打赏
  • 举报
回复
create procedure sp_StockMonthReport @year char(4),@month char(2)
As
Declare @ExecStr Varchar(3000)
@ExecStr=" SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,"+char(13)
@ExecStr+=" stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,"+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,"+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31"+char(13)
@ExecStr+=" FROM v_book, stock"+char(13)
@ExecStr+=" WHERE (v_book.fashion_code = stock.fashion_code) AND "+char(13)
@ExecStr+=" (v_book.size_code = stock.fashion_size)"+char(13)
@ExecStr+=" and stock.tz_year = @year and stock.tz_month = @month"+char(13)
@ExecStr+=" GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code, "+char(13)
@ExecStr+=" stock.qc_amount"+char(13)
Execute (@ExecStr)
提示信息:
服务器: 消息 170,级别 15,状态 1,过程 sp_StockMonthReport,行 4
第 4 行: '@ExecStr' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 sp_StockMonthReport,行 35
必须声明变量 '@ExecStr'。
am2000 2003-10-16
  • 打赏
  • 举报
回复
aierong(皑婀瑢-数据库XML.NET联盟会局长) :我要的就是你的第2种方法。
另外,这并没有用到临时表,在什么情况下需要使用临时表。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧