Access SQL - 为每 10 行和组中发现的剩余行创建行 [英] Access SQL - Create Row For Every 10 Rows And Remainder Found In Group

问题描述

我无法概念化如何专门使用 SQL 来执行此操作.

I can't conceptualize how to do this exclusively with SQL.

假设我有一个查询，它可能是这个问题的解决方案中产生的子查询:

Say I have a query that would probably be the subquery in the solution to this problem that produced:

Color     |  Count
Brown     |  25
Yellow    |  5
Blue      |  35

使用上述结果集，我想查询如下:

Using the above result set, I would like a query with the following:

Color
Brown
Brown
Brown
Yellow
Blue
Blue
Blue
Blue

另一种解决方案是进行计数并执行以下操作:

An alternative solution would be to take the count and do something like:

SELECT -Int((-Count(*)/10)) AS Expr1, Color
FROM ColorTable
group by test.Source_City

使用上述数据会产生:

Color     |  Count
Brown     |  3
Yellow    |  1
Blue      |  4

对此的解决方案是获取 Count 并为每个 1 写入一行.

A solution for this would be to take the Count and write a row for each 1.

推荐答案

所以我们在名为 [InitialCounts] 的表中有我们的测试数据

So we have our test data in a table named [InitialCounts]

Color   Count
------  -----
Blue       35
Brown      25
Yellow      5

和一个名为 [Numbers] 的数字表"包含

and a "numbers table" named [Numbers] containing

   n
----
   1
   2
   3
...
9999

(或根据我们期望为每种颜色派生的最大行数，根据需要达到的高度，见下文).

(or as high as it needs to go, based on the largest number of rows we expect to derive for each color, below).

查询

SELECT 
    Color, 
    Int(CDbl(Count)/10 + 0.5) AS NewCount
FROM InitialCounts

返回

Color   NewCount
------  --------
Blue           4
Brown          3
Yellow         1

如果我们想为每种颜色生成重复的行，我们可以这样做

and if we want to produce the repeating rows for each color we can just do

SELECT NewCounts.Color
FROM
    Numbers,
    (
        SELECT 
            Color, 
            Int(CDbl(Count)/10 + 0.5) AS NewCount
        FROM InitialCounts
    ) AS NewCounts
WHERE Numbers.n <= NewCounts.NewCount
ORDER BY NewCounts.Color

回来

Color 
------
Blue  
Blue  
Blue  
Blue  
Brown 
Brown 
Brown 
Yellow

这篇关于Access SQL - 为每 10 行和组中发现的剩余行创建行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！