我该如何解决这个问题?
问题描述:
我有一个表格,包括1列ID和12个月的月份(Month01到Month12)。如果我有一个参数@月= 9,我怎么可以比较获得列Month01到Month09没有Month10,Month11,Month12。请帮帮我!我该如何解决这个问题?
答
,你也可以尝试了这一点:)
DECLARE @month int = 9
DECLARE @Sql nvarchar(100) =''
DECLARE @pos int = 1
WHILE(@pos <= @month)
BEGIN
SET @Sql = @Sql + (',month' + right('00'+ rtrim(@pos), 2))
SET @pos = @pos + 1
END
SET @Sql= 'SELECT ID ' [email protected] + ' FROM table'
EXEC sp_executesql @Sql
答
DECLARE @month int
DECLARE @tableName nvarchar(128)
SET @month = 9;
SET @tableName = 'months' -- YOUR TABLE's NAME
DECLARE @query nvarchar(2048)
SET @query =
'SELECT id, ' + STUFF((SELECT DISTINCT ', ' + name
FROM sys.columns WHERE name LIKE'Month%'
AND object_id = OBJECT_ID(@tableName)
AND CONVERT(INT, RIGHT(name, 2)) <= @month
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
+ ' FROM ' + @tableName;
EXEC(@query)
答
厮磨的回答是更优雅,但下面是一个另类的“基本”的方式来做到这一点:
DECLARE @month INT
SET @month = 9
IF @Month = 1
BEGIN
SELECT Id, Month01
FROM MonthlyData
END
IF @Month = 2
BEGIN
SELECT Id, Month01, Month02
FROM MonthlyData
END
-- Repeat for months 3-9
IF @Month = 9
BEGIN
SELECT Id, Month01, Month02, Month03, Month04,
Month05, Month06, Month07, Month08, Month09
FROM MonthlyData
END
-- Repeat for months 10-12
见我SQL Fiddle。
答
declare @str varchar(1000)
SELECT @str= coalesce(@str + ', ', '') + a.name
FROM (
Select name from sys.columns
where object_id = OBJECT_ID('table_months')
and isnumeric(right(name,2))= 1 and name like 'month%'
and cast(right(name,2) as int) < '03') a
- 而不是 '03' 的你使用一个变量并分配所需的飞蛾数量
declare @sql nvarchar(100)
set @sql = 'select ID, '+ @str+' from table_months'
exec sp_executesql @sql
你不应该过滤的''object_id' sys.columns'?如果另一个表有一个名为'MonthValue'的列呢? – 2014-10-07 07:14:32
@AndriyM,你说得对。 Fix'd。 – 2014-10-07 15:51:40