例如:求人员年龄结构的报表
CREATE TABLE TAB1(ID BIGINT IDENTITY(1,1),Beg Int,En Int,P varchar(100))
CREATE TABLE TAB2(ID BIGINT IDENTITY(1,1),Name varchar(100),Job_Date Date)
GO
INSERT INTO TAB1
SELECT 0,3,'一级员工'
Union ALL
SELECT 4,4,'二级员工'
Union ALL
SELECT 5,5,'三级员工'
Union ALL
SELECT 6,7,'四级员工'
Union ALL
SELECT 8,100,'五级员工'
GO
INSERT INTO TAB2
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2001-01-01'
Union All
SELECT '一','2005-01-01'
Union All
SELECT '一','2008-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
Union All
SELECT '一','2008-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
GO
DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'Select ';
SELECT @SQL = @SQL + ' (Select Count(1) From TAB2 Where DATEDIFF(YEAR,Job_Date,GETDATE()) Between '
+ Cast(Beg AS VARCHAR) + ' And ' + CAST(En AS VARCHAR)+ ' ) [' + P + '],'
From TAB1
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-1)
EXEC(@SQL)
DROP TABLE TAB1
DROP TABLE TAB2