改正存储过程

wangxiaofeiwuqiao 2010-06-01 11:57:59

以下存储过程当Balance 为Int类型时，是完全正常的，但是实际需要我要把Int 修改为doubel类型。就出错了：列 "Balance" 的类型与 UNPIVOT 列表中指定的其他列的类型冲突。



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[Search_TongJiTime] 



    @date DateTime, --开始时间

    @dateend DateTime --结束时间



AS

BEGIN

SET NOCOUNT ON    

DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)

SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,

    @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))

FROM VIEW_XuQiuGongYingTongji 

where Monday>=@date and Monday<=@dateend  --这里加时间限制

GROUP BY [Monday]

DECLARE @SQL VARCHAR(MAX)

SET @SQL='

    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,

        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        

        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,

        '+@S1+',Total

    FROM (

        SELECT [ProductName],[SupplyName],

            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,

            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total

        FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji

            UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber,Balance)) B

        ) A

        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 

    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 

'

EXEC(@SQL)    

SET NOCOUNT OFF

END

Balance＝字段AlreadyNumber－字段XuQiuNumber；

...全文

153 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

S314324153 2010-06-02

打赏
举报

实在不行就用case when，2000下的

wangxiaofeiwuqiao 2010-06-02

打赏
举报

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[Search_TongJiTime] 



    @date DateTime, --开始时间

    @dateend DateTime --结束时间



AS

BEGIN

SET NOCOUNT ON    

DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)

SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,

    @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))

FROM VIEW_XuQiuGongYingTongji 

where Monday>=@date and Monday<=@dateend  --这里加时间限制

GROUP BY [Monday]

DECLARE @SQL VARCHAR(MAX)

SET @SQL='

    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,

        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        

        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,

        '+@S1+',Total

    FROM (

        SELECT [ProductName],[SupplyName],

            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,

            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total

        FROM (SELECT CAST(XuQiuNumber AS dec(18,2)) AS XuQiuNumber,CAST(AlreadyNumber AS dec(18,2)) AS AlreadyNumber,CAST(Balance AS dec(18,2)) AS Balance,Monday,ProductName,SupplyName FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji

            UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber,Balance)) B

        ) A

        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 

    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 

'

EXEC(@SQL)    

SET NOCOUNT OFF

END

wangxiaofeiwuqiao 2010-06-02

打赏
举报

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[Search_TongJiTime] 



    @date DateTime, --开始时间

    @dateend DateTime --结束时间



AS

BEGIN

SET NOCOUNT ON    

DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)

SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,

    @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))

FROM VIEW_XuQiuGongYingTongji 

where Monday>=@date and Monday<=@dateend  --这里加时间限制

GROUP BY [Monday]

DECLARE @SQL VARCHAR(MAX)

SET @SQL='

    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,

        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        

        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,

        '+@S1+',Total

    FROM (

        SELECT [ProductName],[SupplyName],

            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,

            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total

        FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji

            UNPIVOT(VALUE FOR TYPE IN(CAST(XuQiuNumber AS dec(18,2)) AS XuQiuNumber,CAST(AlreadyNumber AS dec(18,2)) AS AlreadyNumber,CAST(Balance AS dec(18,2)) AS Balance)) B

        ) A

        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 

    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 

'

EXEC(@SQL)    

SET NOCOUNT OFF

END

我按照你的方法改了，但是调用绑定时出现了：'(' 附近有语法错误。怎么办？

wuyq11 2010-06-01

打赏
举报

提示很明白了。类型冲突

hunterpo 2010-06-01

打赏
举报

"UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber,Balance)"

>> XuQiuNumber, AlreadyNumber, Balance 三个字段类型不同，想办法转成一样

丰云 2010-06-01

打赏
举报

不明白什么意思....

htl258_Tony 2010-06-01

打赏
举报

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb] ([ProductName] [nvarchar](10),[SupplyName] [nvarchar](10),[xuqiutime] [datetime],[xuqiu] [int],[gongying] [int],[balance] [decimal](18,2))

INSERT INTO [tb]

SELECT 'No1','深圳公司','2010-05-24','200','200','0' UNION ALL

SELECT 'No1','深圳公司','2010-05-31','300','200','-100' UNION ALL

SELECT 'No1','深圳公司','2010-06-07','400','1000','600' UNION ALL

SELECT 'No1','深圳公司','2010-06-14','800','1000','200' UNION ALL

SELECT 'No1','深圳公司','2010-06-21','800','0','-800' UNION ALL

SELECT 'No1','深圳公司','2010-06-28','500','1000','500' UNION ALL

SELECT 'No2','上海公司','2010-05-24','500','1000','500' UNION ALL

SELECT 'No2','上海公司','2010-05-31','500','1500','1000' UNION ALL

SELECT 'No2','上海公司','2010-06-07','800','1000','200' UNION ALL

SELECT 'No2','上海公司','2010-06-28','1600','1000','-600'



--SELECT * FROM [tb]



-->SQL查询如下:

DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)

SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,xuqiutime,23)) FROM tb GROUP BY [xuqiutime]

SELECT @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,xuqiutime,23))+',0)'+quotename(convert(VARCHAR,xuqiutime,23)) 

FROM tb GROUP BY [xuqiutime]

EXEC('

    SELECT CASE TYPE WHEN ''xuqiu'' THEN ProductName ELSE '''' END ProductName,

        CASE TYPE WHEN ''xuqiu'' THEN SupplyName ELSE '''' END SupplyName,        

        Data = CASE TYPE WHEN ''xuqiu'' THEN ''需求'' WHEN ''gongying'' THEN ''供应'' ELSE ''差额'' END,

        '+@S1+',Total

    FROM (

        SELECT [ProductName],[SupplyName],

            CONVERT(VARCHAR,xuqiutime,23) xuqiutime,VALUE,TYPE,

            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total

        FROM (

                 SELECT ProductName, SupplyName, xuqiutime, CAST(xuqiu AS dec(18, 2)) xuqiu, CAST(gongying AS dec(18, 2)) 

                        AS gongying, balance 

                 FROM tb

             ) tb

            UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying,balance)) B

        ) A

        PIVOT(MAX(VALUE) FOR xuqiutime IN('+@S+')) B

    ORDER BY B.ProductName,CASE TYPE WHEN ''xuqiu'' THEN 1 WHEN ''gongying'' THEN 2 ELSE 3 END

')

/*

ProductName SupplyName Data 2010-05-24                              2010-05-31                              2010-06-07                              2010-06-14                              2010-06-21                              2010-06-28                              Total

----------- ---------- ---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------

No1         深圳公司       需求   200.00                                  300.00                                  400.00                                  800.00                                  800.00                                  500.00                                  3000.00

                       供应   200.00                                  200.00                                  1000.00                                 1000.00                                 0.00                                    1000.00                                 3400.00

                       差额   0.00                                    -100.00                                 600.00                                  200.00                                  -800.00                                 500.00                                  400.00

No2         上海公司       需求   500.00                                  500.00                                  800.00                                  0.00                                    0.00                                    1600.00                                 3400.00

                       供应   1000.00                                 1500.00                                 1000.00                                 0.00                                    0.00                                    1000.00                                 4500.00

                       差额   500.00                                  1000.00                                 200.00                                  0.00                                    0.00                                    -600.00                                 1100.00



(6 行受影响)



*/

你看一下这个范例，把另外两列（xuqiu，gongying）也转为与Blance类型一样就可以了

hunterpo 2010-06-01

打赏
举报

改写 MSDN Sample 当例子：

USE tempdb;

GO



--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 float(53))

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4)

INSERT INTO pvt VALUES (2,4,1,5,5,5)

INSERT INTO pvt VALUES (3,4,3,5,4,4)

INSERT INTO pvt VALUES (4,4,2,5,5,4)

INSERT INTO pvt VALUES (5,5,1,5,5,5)

GO



;WITH #pvt AS (

	SELECT VendorID, 

	CAST(Emp1 AS float(53)) Emp1,

	CAST(Emp2 AS float(53)) Emp2,

	CAST(Emp3 AS float(53)) Emp3,

	CAST(Emp4 AS float(53)) Emp4, 

	Emp5 

	FROM pvt

)

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM 

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM #pvt) p

UNPIVOT

   (Orders FOR Employee IN 

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

GO



DROP TABLE pvt;

GO