sql 按一列分组，然后过滤一列并为不同的值创建新列

Question

在 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 = one，ID = 6 和 10。由于 10 较大，因此它获得该行的值 1000。表的其余部分相同...

我这辈子都想不出怎么做。

Answer 1

这将返回您在 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;