在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/

10-10 18:45
查看更多