仅显示每场比赛的第一行

发布于 2024-12-29 01:30:30 字数 539 浏览 1 评论 0原文

我有一个表（AreaPartners），我只想匹配每个组中的第一个“名称”记录，按“ID”排序，按“区域”分组。因此，对于下表：

    Area    Name            ID
    AB      ISmith          748
    AB      AWood           750
    AB      HArcher         751
    AB      DMunslow        753
    AB      DCornelius      754
    BH      MLee            301
    BH      NMcClean        307
    BH      DMiles          309
    BH      LPayze          325
    BH      MPinnock        329

我想返回 AB 的 ISmith 和 BH 的 MLee 结果。

我该怎么做呢？我相信这与 Group By 功能有关，但我一生都无法让它发挥作用。

原文

I have a table (AreaPartners), and I want to match only the first "Name" record in each group, order by "ID", grouped by "Area". So for the table below:

    Area    Name            ID
    AB      ISmith          748
    AB      AWood           750
    AB      HArcher         751
    AB      DMunslow        753
    AB      DCornelius      754
    BH      MLee            301
    BH      NMcClean        307
    BH      DMiles          309
    BH      LPayze          325
    BH      MPinnock        329

I'd want to return the results ISmith for AB and MLee for BH.

How do I go about doing this? I believe it's something to do with the Group By function, but I can't for the life of me get it to work.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好菇凉咱不稀罕他 2025-01-05 01:30:30

试试这个：

SELECT yourTable.Area, yourTable.Name
FROM yourTable INNER JOIN (
  SELECT MIN(Id) AS MinId
  FROM yourTable
  GROUP BY Area) M ON yourTable.Id = M.MinId

Try this:

SELECT yourTable.Area, yourTable.Name
FROM yourTable INNER JOIN (
  SELECT MIN(Id) AS MinId
  FROM yourTable
  GROUP BY Area) M ON yourTable.Id = M.MinId

回复收藏 0 原文

吐个泡泡 2025-01-05 01:30:30

由于注释而更新（没有表变量并且分区不是 MS access 语句）。您还可以使用 IN 语句来完成此操作：

SELECT 
    yourTable.Area, 
    yourTable.Name
FROM yourTable 
WHERE yourTable.Id IN
(
    SELECT 
        MIN(tbl.Id) AS MinId
    FROM 
        yourTable as tbl
    GROUP BY 
        tbl.Area
)

在 MSSQL 中，您可以这样写：

DECLARE @tbl TABLE
    (
        Area VARCHAR(100),
        Name VARCHAR(100),
        ID INT
    )
INSERT INTO @tbl
SELECT 'AB','ISmith',748
UNION ALL
SELECT 'AB','AWood',750
UNION ALL
SELECT 'AB','HArcher',751
UNION ALL
SELECT 'AB','DMunslow',753
UNION ALL
SELECT 'AB','DCornelius',754
UNION ALL
SELECT 'BH','MLee',301
UNION ALL
SELECT 'BH','NMcClean',307
UNION ALL
SELECT 'BH','DMiles',309
UNION ALL
SELECT 'BH','LPayze',325
UNION ALL
SELECT 'BH','MPinnock',325
;WITH CTE
AS
(
SELECT
    RANK() OVER(PARTITION BY tbl.Area ORDER BY ID) AS iRank,
    tbl.ID,
    tbl.Area,
    tbl.Name
FROM
    @tbl AS tbl
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.iRank=1

Update because of comment (There is no table variable and Partition over is not a MS access statement). You can also do it with an IN statement:

SELECT 
    yourTable.Area, 
    yourTable.Name
FROM yourTable 
WHERE yourTable.Id IN
(
    SELECT 
        MIN(tbl.Id) AS MinId
    FROM 
        yourTable as tbl
    GROUP BY 
        tbl.Area
)

In MSSQL you can write this:

DECLARE @tbl TABLE
    (
        Area VARCHAR(100),
        Name VARCHAR(100),
        ID INT
    )
INSERT INTO @tbl
SELECT 'AB','ISmith',748
UNION ALL
SELECT 'AB','AWood',750
UNION ALL
SELECT 'AB','HArcher',751
UNION ALL
SELECT 'AB','DMunslow',753
UNION ALL
SELECT 'AB','DCornelius',754
UNION ALL
SELECT 'BH','MLee',301
UNION ALL
SELECT 'BH','NMcClean',307
UNION ALL
SELECT 'BH','DMiles',309
UNION ALL
SELECT 'BH','LPayze',325
UNION ALL
SELECT 'BH','MPinnock',325
;WITH CTE
AS
(
SELECT
    RANK() OVER(PARTITION BY tbl.Area ORDER BY ID) AS iRank,
    tbl.ID,
    tbl.Area,
    tbl.Name
FROM
    @tbl AS tbl
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.iRank=1

回复收藏 0 原文

~没有更多了~