动态SQL应用小列子

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->SELECT Id,CONVERT(VARCHAR(10),Create_Time, 120), Last_Login_Time FROM user0
UNION ALL
SELECT Id,CONVERT(VARCHAR(10),Create_Time, 120), Last_Login_Time FROM user1
UNION ALL
SELECT Id,CONVERT(VARCHAR(10),Create_Time, 120), Last_Login_Time FROM user2
.........

 呵呵，下面是我用动态SQL 改写的，呵呵，简洁多了

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->DECLARE @cmdText VARCHAR(8000);
DECLARE @userIndex INT;
SET @cmdText = '';
SET @userIndex = 0;
WHILE @userIndex <30
BEGIN
    IF (@userIndex != 29)
            SELECT @cmdText = @cmdText + 'SELECT Id,Create_Time, Last_Login_Time FROM ' 
                         + '  dbo.user' + CONVERT(VARCHAR,@userIndex) 
                         + '  UNION ALL' + CHAR(10); --换行
    ELSE
            SELECT @cmdText = @cmdText + 'SELECT Id,Create_Time, Last_Login_Time FROM '
                         +  'dbo.user' + CONVERT(VARCHAR,@userIndex) ;
    
    SET @userIndex = @userIndex + 1;
END;

SELECT @cmdText = 'SELECT CONVERT(VARCHAR(10),T.Create_Time, 120) AS Create_Time ,COUNT(0) AS RecordNum FROM (' 
            + @cmdText + 
            ') T WHERE DATEDIFF(d,Last_Login_Time,GETDATE()) < 7 GROUP BY CONVERT(VARCHAR(10),Create_Time, 120) ';
--PRINT     @cmdText    
EXEC (@cmdText);