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)