27,580
社区成员
发帖
与我相关
我的任务
分享
DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @Sql =
@Sql + 'SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM '
+ NAME + ' A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
' + NAME + ' WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex(''_'', [Station]))) B
WHERE A.ID = B.ID' +
' AND A.[Upload_DateTime] >= ' + '''' + @StartTime + '''' +
' AND A.[Upload_DateTime] < ' + '''' + @EndTime + '''' +
' UNION ALL '
FROM
SYS.TABLES
WHERE
LEFT(NAME, '9') = 'LOG_INFO_'
SET @SQL = LEFT(@SQL, LEN(@SQL)-10)
SELECT @Sql = 'SELECT
[Project_Name] as ''product'',
[Station] as ''station_id''
FROM '
+ '(' + @Sql + ') t'
+' GROUP BY
[Project_Name],
[Station]'
EXEC(@SQL)
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_N90 A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_N90 WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_N31CG_WM A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_N31CG_WM WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_N31CG_BM A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_N31CG_BM WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_Kingkong_WM A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_Kingkong_WM WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_K93_white A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_K93_white WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_Kingkong_BM A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_Kingkong_BM WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100' UNION ALL
SELECT
[Project_Name],
[Station],
[Barcode_SN]
FROM LOG_INFO_PN_White A WITH(NOLOCK),
(SELECT
MAX(ID) AS ID
FROM
LOG_INFO_PN_White WITH(NOLOCK)
GROUP BY [Project_Name], [Barcode_SN], SUBSTRING([Station] , 0 , charindex('_', [Station]))) B
WHERE A.ID = B.ID AND A.[Upload_DateTime] >= '20140727070000' AND A.[Upload_DateTime] < '20140727070100'
太多了,没办法完全显示,共30几张表
2、直接执行print出来的语句,看看会不会很慢
执行19张表时,耗时4秒左右