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

SQL联合问题

  •  0
  • amr osama  · 技术社区  · 15 年前

    在这个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 回复  |  直到 15 年前
        1
  •  0
  •   Himadri    15 年前

    尝试以下操作:

    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    15 年前

    你甚至可以试试这个

    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    15 年前

    你可以这样做:

    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    15 年前

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

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

    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)