2 imcarlyoung imcarlyoung 于 2014.07.11 16:57 提问

超级长得SQL语句,看晕你的头,求大神帮忙看一下这段SQL错在哪里。
                        DECLARE @SQL VARCHAR(8000)
                        SET @SQL ='SELECT A.POINT,
                           A.WZ,
                           A.LC,
                           A.PL1,
                           A.PL2,
                           A.XS1,
                           CASE 
                               WHEN ISNULL(A.ZDZS,'''') <> '''' THEN A.ZDZS
                               ELSE 0
                           END AS ZDZS,
                           CASE 
                               WHEN ISNULL(A.PJZ,'''') <> '''' THEN A.PJZ
                               ELSE 0
                           END AS PJZ,
                           CASE 
                               WHEN ISNULL(A.SSZ,'''') <> '''' THEN A.SSZ
                               ELSE 0
                           END AS SSZ,
                           CASE 
                               WHEN ISNULL(A.ZXZ,'''') <> '''' THEN A.ZXZ
                               ELSE 0
                           END AS ZXZ,
                           CASE 
                               WHEN ISNULL(A.ZXZS,'''') <> '''' THEN A.ZXZS
                               ELSE 0
                           END AS ZXZS,
                           CASE 
                               WHEN ISNULL(A.ZDZ,'''') <> '''' THEN A.ZDZ
                               ELSE 0
                           END AS ZDZ,
                           CASE 
                               WHEN ISNULL(A.MaximumAndTime,'''') <> '''' THEN A.MaximumAndTime
                               ELSE CONVERT(VARCHAR(50),GETDATE(),20)
                           END AS MaximumAndTime,


                         A.SJ  双击后错误指向这里 为 消息 102,级别 15,状态 1,第 36 行

')' 附近有语法错误。

                    FROM('
                        SET @SQL= @SQL + 'SELECT * FROM ('
                        IF OBJECT_ID('FC_M20140608', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-08 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               0 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               0* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               0* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140608 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140609', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-09 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               1 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               1* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               1* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140609 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140610', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-10 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               2 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               2* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               2* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140610 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140611', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-11 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               3 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               3* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               3* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140611 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140612', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-12 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               4 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               4* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               4* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140612 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140613', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-13 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               5 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               5* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               5* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140613 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140614', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-14 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               6 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               6* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               6* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140614 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140615', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-15 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               7 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               7* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               7* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140615 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140616', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-16 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               8 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               8* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               8* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140616 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140617', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-17 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               9 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               9* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               9* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140617 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140618', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-18 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               10 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               10* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               10* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140618 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140619', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-19 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               11 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               11* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               11* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140619 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            IF OBJECT_ID('FC_M20140620', 'U') IS NOT NULL
                            BEGIN
                               SET @SQL = @SQL + 'SELECT ''001A07'' AS POINT,
                               wz.WZ,
                               dev.LC,
                               dev.PL1,
                               dev.PL2,
                               dev.XS1,
                               CONVERT(varchar(50),m.ZDZ) +''\2014-06-20 ''+ CONVERT(varchar(50),m.ZDZS / 3600)+'':''+ CONVERT(varchar(50),(m.ZDZS % 3600)/60)+'':''+CONVERT(varchar(50),(m.ZDZS % 3600) % 60) AS MaximumAndTime,
                               12 * 86400 + m.ZDZS AS ZDZS,
                               m.PJZ,
                               12* 1440 + m.SJ AS SJ,
                               SSZ,
                               ZXZ,
                               12* 86400 + m.ZXZS AS ZXZS,
                               m.ZDZ
                        FROM   FC_M20140620 m INNER
                        JOIN   FC_DEV dev
                          ON   m.DEVID = dev.DEVID
                          INNER
                        JOIN   FC_WZ wz
                          ON   m.WZID = wz.WZID
                        WHERE  m.FZH = 001
                          AND  m.KH = 07 UNION ALL '
                            END
                            SET @SQL = SUBSTRING(@SQL,1,(LEN(@SQL)- 9))
                        SET @SQL = @SQL + ') F) A  ORDER BY SJ '
                        EXEC(@SQL)
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!