代码之家 › 专栏 › 技术社区 › Elias

SQL-每个条件的行-访问

vba group-by ms-access sql

Elias · 技术社区 · 12 年前

我正在尝试创建一个查询,它将获取一个数据列表,并在每次满足条件时给我N行。

假设我有以下数据:

 ID  |  Type
  1  |  Vegetables
  2  |  Vegetables
  3  |  Vegetables
  4  |  Fruits
  5  |  Fruits
  6  |  Meats
  7  |  Dairy
  8  |  Dairy
  9  |  Dairy
  10 |  Dairy

我想要的是:

Type           
Dairy       
Dairy
Dairy
Fruits
Fruits
Meats
Meats
Vegetables
Vegetables

我的标准是,对于每种类型的每2个,我都将其作为一个“整体”值。如果有大于整数值的值,请四舍五入到最接近的整数。因此,蔬菜类型从1.5行增加到2行,奶制品类型保持在2行。

然后我想给每个不是集合中最后一个类型的类型添加一行(这就是为什么Vegetables只有两行),也许还有另一个列名称显示它是添加的行。

2 回复 | 直到 12 年前

fthiella 12 年前

此查询将返回每个类型以及必须重复的次数:

SELECT Type, tot+IIf(Type=(SELECT MAX(Type) FROM tablename),0,1) AS Rep
FROM (SELECT tablename.Type, -Int(-Count([tablename].[ID])/2) AS tot
  FROM tablename
  GROUP BY tablename.Type
)  AS s;

那么我的想法是使用一个名为[times]的表,其中包含重复n次的每个数字:

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

然后您的查询可能是这样的:

SELECT s.*
FROM (
  SELECT Type, tot+IIf(Type=(SELECT MAX(Type) FROM tablename),0,1) AS rep
  FROM (SELECT tablename.Type, -Int(-Count([tablename].[ID])/2) AS tot
    FROM tablename
    GROUP BY tablename.Type
  )  AS s1) s INNER JOIN times ON s.rep=times.n

Johnny Bones 12 年前

所以你要计算记录,除以2,四舍五入,然后加1。

--Create a temporary table with all numbers from 1 to 1024.
declare @Numbers table
( 
MaxQty INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024 
BEGIN 
INSERT @Numbers DEFAULT VALUES 
END

--First get the count of records
SELECT [Type], Sum(1) as CNT
INTO #TMP1
FROM MyTable
Group By [Type]

--Now get the number of times the record should be repeated, based on this formula :
--   count the records, divide by 2, round up and then add 1
SELECT [Type], CNT, CEILING((CNT/2)+1) as TimesToRepeat
INTO #TMP2
FROM #TMP1

--Join the #TMP2 table with the @Numbers table so you can repeat your records the
--   required number of times
SELECT A.*
from #TMP2 as A
join @Numbers as B 
on B.MaxQty <= A.TimesToRepeat

不漂亮,但应该有用。这仍然不能解释最后一种类型,我有点被这部分难住了。

推荐文章