在MS Access查询中,如何将一列分组,然后使用最大ID从表中获取特定值。我知道我可以分组并使用max来获取最大ID,但是不确定如何获取多个最大ID。
如果我有这样的表:
+----+------+-------+------+
| ID | Col1 | Col2 | Col3 |
+----+------+-------+------+
| 1 | a | one | 100 |
| 2 | a | two | 200 |
| 3 | a | three | 300 |
| 4 | a | four | 400 |
| 5 | a | five | 500 |
| 6 | b | one | 600 |
| 7 | b | two | 700 |
| 8 | b | three | 800 |
| 9 | b | four | 900 |
| 10 | b | one | 1000 |
| 11 | b | three | 1100 |
| 12 | b | six | 1200 |
| 13 | c | one | 1300 |
| 14 | c | two | 1400 |
| 15 | c | three | 1500 |
| 16 | c | four | 1600 |
| 17 | c | six | 1700 |
| 18 | c | three | 1800 |
| 19 | c | two | 1900 |
| 20 | c | one | 2000 |
+----+------+-------+------+
我想得到这个:
+------+------+------+-------+
| Col1 | one | two | three |
+------+------+------+-------+
| a | 100 | 200 | 300 |
| b | 1000 | 700 | 1100 |
| c | 2000 | 1900 | 1800 |
+------+------+------+-------+
当Col1 = b,而Col2 = 1时,ID = 6和10。由于10较大,因此该行的值是1000。表其余部分的值相同...
我一生无法弄清楚该如何做。
最佳答案
这将返回您要求的结果以及Access 2007中的示例数据。
SELECT
sub.Col1,
DLookup('Col3', 'YourTable', 'ID=' & sub.MaxOfID1) AS [one],
DLookup('Col3', 'YourTable', 'ID=' & sub.MaxOfID2) AS [two],
DLookup('Col3', 'YourTable', 'ID=' & sub.MaxOfID3) AS [three]
FROM
(
SELECT
y.Col1,
Max(IIf(y.Col2='one', y.ID, 0)) AS MaxOfID1,
Max(IIf(y.Col2='two', y.ID, 0)) AS MaxOfID2,
Max(IIf(y.Col2='three', y.ID, 0)) AS MaxOfID3
FROM YourTable AS y
GROUP BY y.Col1
) AS sub;
关于sql - sql按一列分组,然后过滤一列并为不同的值创建新列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15839348/