如何找到与不同的多条记录,其中来自同一个表
条件我有一个表像这样如何找到与不同的多条记录,其中来自同一个表
ID | cid |lightness | darkness | color
------|-------|-------------|--------------|---------
1 | 5 |10 | 20 | green
2 | 5 |10 | 08 | green
3 | 5 |10 | 10 | green
4 | 5 |20 | 05 | green
5 | 8 |10 | 20 | red
6 | 8 |10 | 16 | red
7 | 8 |33 | 20 | red
8 | 5 |10 | 10 | green
我想找出以下计数:的记录,其中颜色
- 计数有记录亮度10
- 计数,其中颜色有暗20
所以输出应该是
Color | lightness | darkness | Total
---------|-------------|------------|---------
green | 4 | 1 | 5
red | 2 | 2 | 4
Total | 6 | 3 | 9
我试过下面的查询,但它没有带来正确的结果。
Select color, sum(lightness), sum(darkness)
from colortable
where cid in (5,8)
and (lightness = 10 or darkness = 20)
Group by color;
保存下列SQL作为一个新的查询,qryBaseCounts:
SELECT
sub.color,
sub.light_10,
sub.dark_20,
light_10+dark_20 AS light_plus_dark
FROM [
SELECT
color,
Sum(IIf(lightness=10,1,0)) AS light_10,
Sum(IIf(darkness=20,1,0)) AS dark_20
FROM colortable
WHERE
cid In (5,8)
AND (lightness=10
OR darkness=20)
GROUP BY color
]. AS sub;
然后你可以使用qryBaseCounts在UNION查询:
SELECT
q1.color,
q1.light_10 AS lightness,
q1.dark_20 AS darkness,
q1.light_plus_dark AS [Total]
FROM qryBaseCounts AS q1
UNION ALL
SELECT
"Total",
Sum(q2.light_10)
Sum(q2.dark_20)
Sum(q2.light_plus_dark)
FROM qryBaseCounts AS q2;
这是来自第二个查询使用您的示例数据的Access 2007输出colortable:
color lightness darkness Total
green 4 1 5
red 2 2 4
Total 6 3 9
试试这个:
Select color,
sum(iif(lightness = 10, 1, 0)),
sum(iif(darkness = 20, 1, 0)),
count(*)
from colortable
where cid in (5,8)
Group by color;
这不会给你汇总行。某些SQL变体提供了“WITH ROLLUP”子句或类似的内容,但不包括Access AFAIK。你可以使用一个联盟:
Select color,
sum(iif(lightness = 10, 1, 0)),
sum(iif(darkness = 20, 1, 0)),
count(*)
from colortable
where cid in (5,8)
Group by color
union
Select 'Totals',
sum(iif(lightness = 10, 1, 0)),
sum(iif(darkness = 20, 1, 0)),
count(*)
from colortable
where cid in (5,8)
总共他可以使用count(*) – 2012-01-17 15:29:35
对不起,指的是总计行,但我从您的评论中看到我错过了一列(并未能删除不必要的过滤器)。现在整顿了。谢谢。 – 2012-01-17 15:32:09
好的。对于总计行,他可以不使用“按颜色分组”部分重新运行查询。但他会运行两个查询 – 2012-01-17 15:43:01
感谢您的回答。你能告诉我如何修改查询以应对颜色列是否为多值字段 – Anthony 2012-01-17 21:29:25
我避开多值字段。我建议你创建一个包含该要求的新问题,并添加一个修改后的输入数据样本。 – HansUp 2012-01-17 21:40:36
我也讨厌他们,但我正在处理这个我继承的神糟糕的数据库。会发布一个问题。谢谢 – Anthony 2012-01-17 21:42:56