读书人

帮忙看段sql解决方案

发布时间: 2012-05-24 11:55:41 作者: rapoo

帮忙看段sql

SQL code
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER  PROCEDURE CROSSTABASBEGIN    DECLARE @AREANAME VARCHAR(50)    DECLARE @F_CLASS2 VARCHAR(50)    DECLARE @AR_MONEY DECIMAL(18,2)    DECLARE @CREATESQL VARCHAR(200)    DECLARE @INSERTSQL VARCHAR(200)    CREATE TABLE #TMP(项目 VARCHAR(50))    DECLARE CREATECURSOR CURSOR FOR SELECT DISTINCT AREANAME FROM DINING_AREA    DECLARE INSERTCURSOR CURSOR FOR SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,AREANAME FROM (SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,(SELECT AREANAME FROM DINING_AREA WHERE ID=(SELECT AREAID FROM DISHTABLE WHERE ID=(SELECT TABLEID FROM TABLEORDERS WHERE ID=(SELECT TABLEORDERSID FROM ORDERDISH WHERE ID=O.ID)))) AS AREANAME FROM ORDERDISH O GROUP BY F_CLASS2,ID) A GROUP BY F_CLASS2,AREANAME ORDER BY F_CLASS2,AREANAME    OPEN CREATECURSOR    FETCH NEXT FROM CREATECURSOR INTO @AREANAME    WHILE @@FETCH_STATUS=0    BEGIN        IF NOT @AREANAME IS NULL        BEGIN            SET @CREATESQL='ALTER TABLE #TMP ADD '+@AREANAME+' DECIMAL(18,2)'            EXEC(@CREATESQL)                    END        FETCH NEXT FROM CREATECURSOR INTO @AREANAME    END    OPEN INSERTCURSOR        FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME            WHILE @@FETCH_STATUS=0            BEGIN                SET @INSERTSQL='INSERT INTO #TMP (项目,'+ @AREANAME +') VALUES('''+ @F_CLASS2 +''','+ @AR_MONEY +')'                EXEC(@INSERTSQL)                FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME            END    CLOSE CREATECURSOR    DEALLOCATE CREATECURSOR    CLOSE INSERTCURSOR    DEALLOCATE INSERTCURSORENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO


[解决办法]
SET @INSERTSQL='INSERT INTO #TMP (项目,'+ ltrim(@AREANAME) +') VALUES('''+ ltrim(@F_CLASS2 )+''','+ltrim( @AR_MONEY )+')'

读书人网 >SQL Server

热点推荐