选择数字到多行 [英] Select Number Into Multiple Rows

问题描述

我在选择一个数字的表上有一个选择

I have a select on a table which selects a number

SELECT * FROM table

我生成的东西看起来像

| column |
|   2    |
|   5    |

我想知道是否有办法将 2 和 5 变成多行 2 和 5.所以基本上

I am wondering if there is a way to turn that 2 and 5 into multiple rows of 2 and 5. So basically

| column |
|   2    |
|   2    |
|   5    |
|   5    |
|   5    |
|   5    |
|   5    |

正如一些人问我的最终目标是什么，我真正想要完成的是一种选票选择，基本上我将一个人获得的选票数量存储为#，我想把它转为#分成多行，以便我可以将其转移到另一个程序以供选择获胜者.

As asked by a few what my ultimate goal is, is I am really trying to accomplish is a sort of ballot select, basically I store the number of ballots a person gets as a # and I want to turn that # into multiple rows so I can transfer it over to another program for the winner selection.

推荐答案

您可以使用数字(又名计数)表来完成:

You can do it using a numbers (aka tally) table:

SELECT col
FROM mytable AS t1
INNER JOIN (
  SELECT @rn := @rn + 1 AS num
  FROM (
     SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) AS x
  CROSS JOIN (
     SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) AS y
  CROSS JOIN (SELECT @rn := 0) AS var          
) AS t2 ON t1.col >= t2.num
ORDER BY col

上述查询使用了一个数值范围为 [1-100] 的数字表.如果您的列包含更大的值，那么您必须使用额外的 CROSS JOIN 操作来扩展数字表.

The above query uses a numbers table with a range of values [1-100]. If your column contains bigger values then you have to grow the numbers table using additional CROSS JOIN operations.

此处演示

这篇关于选择数字到多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！