查询，在第二列（sql，oracle）中多次授予每一行

3 回答

• 1

  假设SQL Server，可以通过明智地使用计数表（在此示例中使用 sys.all_columns ，其大小是可以生成的行数的上限）和计算索引号范围的部分和来实现 . 像这样：

  ;with Ranges as (
      select
          col1,
          (select coalesce(sum(t2.col2), 0)+1
              from MyTable t2 where t2.col1<t1.col1) as indexStart,
          (select coalesce(sum(t2.col2), 0)
              from MyTable t2 where t2.col1<=t1.col1) as indexEnd
      from
          MyTable t1
  )
  ,TallyTable as (
      select
          row_number() over (order by c.object_id, c.column_id) as number
      from
          sys.all_columns c
  )
  select
      r.col1
  from
      Ranges R, TallyTable T
  where
      T.number between r.indexStart and r.indexEnd
  

  虽然正如OP上的评论所示，但不建议这样做，并且很可能有更好的解决方案来解决您的业务问题 .

  回复于 2024-04-29T19:22:58+08:00
• 0

  假设您正在使用SQL Server，使用递归 CTE 可以很好地完成工作

  • 从 YourTable 中选择每一行，在结果集中添加一个计数器 .

  • 递归给定列，直到计数器达到 col2 中的值

  SQL Statement

  ;WITH q AS (
    SELECT  col1, cnt = 1
    FROM    YourTable
    UNION ALL
    SELECT  q.col1, cnt + 1
    FROM    q
            INNER JOIN YourTable yt ON yt.col1 = q.col1
    WHERE   cnt < yt.col2          
  )  
  SELECT  col1
  FROM    q
  OPTION  (MAXRECURSION 0)
  

  Test script

  ;WITH YourTable(col1, col2) AS (
    SELECT 'apple', 4
    UNION ALL SELECT 'melon', 1
    UNION ALL SELECT 'banana', 2
  )
  , q AS (
    SELECT  col1, cnt = 1
    FROM    YourTable
    UNION ALL
    SELECT  q.col1, cnt + 1
    FROM    q
            INNER JOIN YourTable yt ON yt.col1 = q.col1
    WHERE   cnt < yt.col2          
  )  
  SELECT  col1
  FROM    q
  OPTION  (MAXRECURSION 0)
  

  回复于 2024-04-29T19:22:58+08:00
• 0

  由于你没有给出DBMS我假设PostgreSQL：

  with test_data (col1, col2) as (
    values ('apple', 4), ('melon', 1), ('banana', 2)
  ) 
  select col1 
  from (
    select col1, generate_series(1, col2)
    from test_data
  ) t
  

  回复于 2024-04-29T19:22:58+08:00