代码之家 › 专栏 › 技术社区 › amr osama

SQL联合问题

sql-server-2005

0

amr osama · 技术社区 · 16 年前

在这个SQL代码中

DECLARE @n tinyint
   WHILE (@n > 0)
   BEGIN
    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 1
          THEN 'EVEN'
        ELSE 'ODD'
       END AS 'Type'
    SET @n = @n - 1
   END

我怎么能把union子句放在这个can中,使结果显示在一个结果集中?

4 回复 | 直到 16 年前

1

0

Himadri 16 年前

尝试以下操作:

DECLARE @n TINYINT
DECLARE @sql VARCHAR(max)
SET @sql=''
SET @n=10
   WHILE (@n > 0)
   BEGIN
  SET @sql=@sql+' SELECT  '+CONVERT(VARCHAR(50) ,@n)+' AS ''Number''
      ,CASE
        WHEN ('+CONVERT(VARCHAR(50) ,@n)+' % 2) = 1
          THEN ''ODD''
        ELSE ''EVEN''
       END AS TYPE '
       IF @n>1 SET @sql=@sql+' Union '
    SET @n = @n - 1
   END

   EXEC( @sql)

2

1

priyanka.sarkar 16 年前

你甚至可以试试这个

DECLARE @n tinyint
declare @tbl table(number tinyint,NumberType varchar(10))

set @n  = 10
   WHILE (@n > 0)
   BEGIN
   insert into @tbl

    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 0
          THEN 'EVEN'      
       END AS 'Type'

       UNION

       SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) <> 0
          THEN 'ODD'
       END AS 'Type' 

    SET @n = @n - 1

   END
   select * from @tbl where NumberType is not null

输出是

**number    NumberType**
10  EVEN
9   ODD
8   EVEN
7   ODD
6   EVEN
5   ODD
4   EVEN
3   ODD
2   EVEN
1   ODD

但你真正想要实现什么?

3

0

Mladen Prajdic 16 年前

你可以这样做:

DECLARE @n TINYINT
SET @n = 100
SELECT  number,
        CASE WHEN (number % 2) = 1 THEN 'EVEN' ELSE 'ODD' END AS 'Type'
FROM    (
            SELECT ROW_NUMBER() OVER(ORDER BY number) AS number
            FROM master..spt_values
        ) t
WHERE   number < @n

4

0

Andrew 16 年前

重新考虑你的问题比较容易。在集合中思考!

首先生成一组数字。然后将一组数字分类为偶数或奇数。

declare @numberCount int 
set @numberCount = 10;

with numbers as
(
  select 1 as number
  union all
  select number + 1 from numbers where number < @numberCount
)

select number,
       case        
          when (number % 2) = 1     
             then 'EVEN'      
             else 'ODD'      
       end AS 'Type' 
 from numbers option(maxrecursion 10000)