代码之家 › 专栏 › 技术社区 › Armen Michaeli

SQL:有没有简单的方法可以先对结果排序,然后再按另一列分组?

sql-order-by group-by database mysql sql

Armen Michaeli · 技术社区 · 15 年前

因为我似乎需要先按时间戳列a对行进行排序,然后丢弃列a中具有相同值的行。不知道如何做到这一点。。。

我正在使用MySQL5.1.41

create table
(
    A int,
    B timestamp
)

数据可以是:

+-----+-----------------------+
|  A  |  B                    |
+-----+-----------------------+
|  1  |  today                |
|  1  |  yesterday            |
|  2  |  yesterday            |
|  2  |  tomorrow             |
+-----+-----------------------+

我的目标是:

+-----+-----------------------+
|  A  |  B                    |
+-----+-----------------------+
|  1  |  today                |
|  2  |  tomorrow             |
+-----+-----------------------+

我的实际项目详情,如果您需要:

在现实生活中,我有两张桌子- users 和 payment_receipts .

create table users
(
    phone_nr int(10) unsigned not null,
    primary key (phone_nr)
)

create table payment_receipts
(
    phone_nr int(10) unsigned not null,
    payed_ts timestamp default current_timestamp not null,
    payed_until_ts timestamp not null,
    primary key (phone_nr, payed_ts, payed_until_ts)
)

这些表可能包括其他列,我省略了所有与IMO无关的内容。作为移动支付计划的一部分,我必须定期通过移动蜂窝网络向用户发送短信,当然这取决于支付是否到期。支付是在短信发送时实现的,这是保费税。我会记录所有的付款 付款收据 payed_ts 和 payed_until_ts 相当于30天的时间。

当然,我有一个每天都执行的批处理作业,需要选择一个用户列表,作为自动订阅续订的一部分,这些用户每月到期付款。要将此链接到前面的虚拟示例,请使用phone number列 phone_nr 是 a 付款截止日期 是 b ,但在实际代码中有两个表,这使我了解了以下行为及其含义:删除用户记录时,收据仍然保留,以便记账。因此,我不仅需要按日期对付款进行分组并放弃所有付款,还需要注意不要选择不再有匹配用户记录的收据。

我通过查找最新的收据来解决选择到期付款记录的问题值(在大多数情况下,每个电话号码都有多张收据) 电话号码 在那几行之外,我只需要留下那些电话号码 付款截止日期 早于批处理作业执行的时间。我在这些号码的列表上循环并发送付款,为每个发送的短信存储一个新的收据,其中 已付金额 now() 和是 now() + interval 30 days .

5 回复 | 直到 5 年前

Mike Sherov 15 年前

Select a,b from (select a,b from table order by b) as c group by a;

paxdiablo 15 年前

是的,分组是先完成的,它会影响单个 select 而排序会影响所有结果 union ,例如:

select a, 'max', max(b) from tbl group by a
union all select a, 'min', min(b) from tbl group by a
order by 1, 2

(使用中的字段号) order by 因为我懒得给我的专栏命名)。每个 group by 只影响其 ,的影响组合结果集。

似乎你所追求的可以通过以下方式实现:

select A, max(B) from tbl group by A

它使用 max 聚合函数,基本上可以进行预组排序(它没有在任何像样的DBMS中对它进行排序,而只需从合适的索引(如果可用)中选择最大值。

nvogel 15 年前

SELECT DISTINCT a,b
FROM tbl t
WHERE b = (SELECT MAX(b) FROM tbl WHERE tbl.a = t.a);

vol7ron 15 年前

根据你的新规则(用PostgreSQL测试)

您需要的查询:

SELECT    pr.phone_nr, pr.payed_ts, pr.payed_until_ts 
FROM      payment_receipts pr
JOIN      users
          ON (pr.phone_nr = users.phone_nr)
   JOIN      (select phone_nr, max(payed_until_ts) as payed_until_ts 
              from payment_receipts 
              group by phone_nr
             ) sub
             ON (    pr.phone_nr       = sub.phone_nr 
                 AND pr.payed_until_ts = sub.payed_until_ts)
ORDER BY  pr.phone_nr, pr.payed_ts, pr.payed_until_ts;

原始答案(有更新):

CREATE TABLE foo (a NUMERIC, b TEXT, DATE);

INSERT INTO foo VALUES 
   (1,'a','2010-07-30'),
   (1,'b','2010-07-30'),
   (1,'c','2010-07-31'),
   (1,'d','2010-07-31'),
   (1,'a','2010-07-29'),
   (1,'c','2010-07-29'),
   (2,'a','2010-07-29'),
   (2,'a','2010-08-01');

-- table contents
SELECT * FROM foo ORDER BY c,a,b;
 a | b |     c      
---+---+------------
 1 | a | 2010-07-29
 1 | c | 2010-07-29
 2 | a | 2010-07-29
 1 | a | 2010-07-30
 1 | b | 2010-07-30
 1 | c | 2010-07-31
 1 | d | 2010-07-31
 2 | a | 2010-08-01

-- The following solutions both retrieve records based on the latest date
--    they both return the same result set, solution 1 is faster, solution 2
--    is easier to read

-- Solution 1: 
SELECT    foo.a, foo.b, foo.c 
FROM      foo
JOIN      (select a, max(c) as c from foo group by a) bar
  ON      (foo.a=bar.a and foo.c=bar.c)
ORDER BY  foo.a, foo.b, foo.c;

-- Solution 2: 
SELECT    a, b, MAX(c) AS c 
FROM      foo main
GROUP BY  a, b
HAVING    MAX(c) = (select max(c) from foo sub where main.a=sub.a group by a)
ORDER BY  a, b;

 a | b |     c      
---+---+------------
 1 | c | 2010-07-31
 1 | d | 2010-07-31
 2 | a | 2010-08-01
(3 rows)

评论:
1 返回两次,因为它们是多个 b 价值观。这是可以接受的(建议的)。你的数据不应该有这个问题,因为 c 基于的值。

vartaks 9 年前

create table user_payments
(
    phone_nr int NOT NULL,
    payed_until_ts datetime NOT NULL
)

insert into user_payments
(phone_nr, payed_until_ts)
values
(1, '2016-01-28'), -- today
(1, '2016-01-27'), -- yesterday  
(2, '2016-01-27'), -- yesterday 
(2, '2016-01-29')  -- tomorrow

select phone_nr, MAX(payed_until_ts) as latest_payment
from user_payments
group by phone_nr

-- OUTPUT:
-- phone_nr latest_payment
-- 1        2016-01-28 00:00:00.000
-- 2        2016-01-29 00:00:00.000

在上面的示例中,我使用了datetime列,但是类似的查询应该适用于timestamp列。

MAX函数基本上会执行“ORDER BY”payed\u until\u ts列,并为每个电话选择最新的值。此外,由于“groupby”子句,每个电话号码只能得到一个值。