1. <legend id='XmWBx'><style id='XmWBx'><dir id='XmWBx'><q id='XmWBx'></q></dir></style></legend>

        <bdo id='XmWBx'></bdo><ul id='XmWBx'></ul>

    2. <i id='XmWBx'><tr id='XmWBx'><dt id='XmWBx'><q id='XmWBx'><span id='XmWBx'><b id='XmWBx'><form id='XmWBx'><ins id='XmWBx'></ins><ul id='XmWBx'></ul><sub id='XmWBx'></sub></form><legend id='XmWBx'></legend><bdo id='XmWBx'><pre id='XmWBx'><center id='XmWBx'></center></pre></bdo></b><th id='XmWBx'></th></span></q></dt></tr></i><div id='XmWBx'><tfoot id='XmWBx'></tfoot><dl id='XmWBx'><fieldset id='XmWBx'></fieldset></dl></div>

        <tfoot id='XmWBx'></tfoot>

        <small id='XmWBx'></small><noframes id='XmWBx'>

        选择每个 GROUP BY 组中的第一行?

        时间:2023-07-18
          <bdo id='iz1OV'></bdo><ul id='iz1OV'></ul>
          <i id='iz1OV'><tr id='iz1OV'><dt id='iz1OV'><q id='iz1OV'><span id='iz1OV'><b id='iz1OV'><form id='iz1OV'><ins id='iz1OV'></ins><ul id='iz1OV'></ul><sub id='iz1OV'></sub></form><legend id='iz1OV'></legend><bdo id='iz1OV'><pre id='iz1OV'><center id='iz1OV'></center></pre></bdo></b><th id='iz1OV'></th></span></q></dt></tr></i><div id='iz1OV'><tfoot id='iz1OV'></tfoot><dl id='iz1OV'><fieldset id='iz1OV'></fieldset></dl></div>
          <legend id='iz1OV'><style id='iz1OV'><dir id='iz1OV'><q id='iz1OV'></q></dir></style></legend>
            <tbody id='iz1OV'></tbody>

            • <small id='iz1OV'></small><noframes id='iz1OV'>

                <tfoot id='iz1OV'></tfoot>

                  本文介绍了选择每个 GROUP BY 组中的第一行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  正如标题所暗示的,我想选择用 GROUP BY 分组的每组行的第一行.

                  As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

                  具体来说,如果我有一个看起来像这样的 purchases 表:

                  Specifically, if I've got a purchases table that looks like this:

                  SELECT * FROM purchases;
                  

                  我的输出:

                  <头>
                  id客户总计
                  15
                  2莎莉3
                  32
                  4莎莉1

                  我想查询每个客户的最大购买量(total)的id.像这样:

                  I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

                  SELECT FIRST(id), customer, FIRST(total)
                  FROM  purchases
                  GROUP BY customer
                  ORDER BY total DESC;
                  

                  预期输出:

                  <头>
                  FIRST(id)客户第一个(总计)
                  15
                  2莎莉3

                  推荐答案

                  关于支持 CTE 和窗口功能:

                  WITH summary AS (
                      SELECT p.id, 
                             p.customer, 
                             p.total, 
                             ROW_NUMBER() OVER(PARTITION BY p.customer 
                                                   ORDER BY p.total DESC) AS rank
                        FROM PURCHASES p)
                   SELECT *
                     FROM summary
                   WHERE rank = 1
                  

                  任何数据库都支持:

                  但是你需要添加逻辑来打破关系:

                  Supported by any database:

                  But you need to add logic to break ties:

                    SELECT MIN(x.id),  -- change to MAX if you want the highest
                           x.customer, 
                           x.total
                      FROM PURCHASES x
                      JOIN (SELECT p.customer,
                                   MAX(total) AS max_total
                              FROM PURCHASES p
                          GROUP BY p.customer) y ON y.customer = x.customer
                                                AND y.max_total = x.total
                  GROUP BY x.customer, x.total
                  

                  这篇关于选择每个 GROUP BY 组中的第一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:无法创建表;为什么这段代码会失败? 下一篇:在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?

                  相关文章

                    <small id='XCFEg'></small><noframes id='XCFEg'>

                    • <bdo id='XCFEg'></bdo><ul id='XCFEg'></ul>

                  1. <legend id='XCFEg'><style id='XCFEg'><dir id='XCFEg'><q id='XCFEg'></q></dir></style></legend>

                    <i id='XCFEg'><tr id='XCFEg'><dt id='XCFEg'><q id='XCFEg'><span id='XCFEg'><b id='XCFEg'><form id='XCFEg'><ins id='XCFEg'></ins><ul id='XCFEg'></ul><sub id='XCFEg'></sub></form><legend id='XCFEg'></legend><bdo id='XCFEg'><pre id='XCFEg'><center id='XCFEg'></center></pre></bdo></b><th id='XCFEg'></th></span></q></dt></tr></i><div id='XCFEg'><tfoot id='XCFEg'></tfoot><dl id='XCFEg'><fieldset id='XCFEg'></fieldset></dl></div>

                  2. <tfoot id='XCFEg'></tfoot>