SQL：任何直接的方式来订购结果FIRST，THEN group by另一列？ [英] SQL: Any straightforward way to order results FIRST, THEN group by another column?
问题描述
我看到在SQL中，GROUP BY必须先于ORDER BY表达式。这是否意味着排序在分组后丢弃相同的行/列？
因为我似乎需要通过时间戳列A排序行，不确定如何完成此操作...
我正在使用MySQL 5.1.41
创建表
（
A int，
B时间戳
）
数据可以是：
+ ----- + ----------------------- +
| A | B |
+ ----- + ----------------------- +
| 1 |今天|
| 1 |昨天|
| 2 |昨天|
| 2 |明天|
+ ----- + ----------------------- +
我的目标是：
--- + ----------------------- +
| A | B |
+ ----- + ----------------------- +
| 1 |今天|
| 2 |明天|
+ ----- + ----------------------- +
基本上，我想要在列B（认为ORDER BY）中具有最新时间戳记的行，并且对于列A中的每个值只有一行（认为DISTINCT或GROUP BY） 。
我的实际项目详细信息，如果您需要这些：
我有两张表 - 用户和 payment_receipts 。
create table users
（
phone_nr int（10）unsigned not null，
主键（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键（phone_nr，payed_ts，payed_until_ts）
）
表可以包括其他列省略了所有IMO在这里不相关。作为移动支付计划的一部分，我必须定期向移动蜂窝网络中的用户发送SMS，这取决于支付是否到期。付款是在短信发送时实现的，这是付费税。我保留所有用 payment_receipts 表进行的所有支付的记录，用于记账，模拟一个真实的商店，买方和卖方都得到购买收据的副本，以供参考。此表存储每张收据的我（卖家）副本。客户收据是接收的SMS本身。每次发送SMS（并且因此完成支付）时，在表格中插入收据记录，指明谁支付，何时以及直到何时。为了解释后者，假设一个订阅服务，但是一个无限期，直到用户选择退出显式，在这一点用户记录被删除。付款是提前一个月支付的，所以一般来说， payed_ts 和 payed_until_ts 之间的差额为30天
自然，我有一个批处理作业，每天执行，并需要选择一个用户列表，作为自动订阅续订的一部分，每月付款。要将此链接到之前的虚拟示例，电话号码列 phone_nr 是 a 和 payed_until_ts 是 b ，但在实际代码中有两个表，它们引出了以下行为及其影响：当用户记录被删除时，收据保存，用于记账。因此，我不仅需要按日期分组付款，并丢弃除最新付款收据日期之外的所有付款，我还需要注意不要选择不再有匹配用户记录的收据。
我正在解决通过查找具有最新 payed_until_ts 值的收据来选择应付款记录的问题因为在大多数情况下，对于每个电话号码将有几个收据）对于每个 phone_nr 和这些行我还需要留下那些phone_numbers其中 payed_until_ts 早于批处理作业的执行时间。我循环列出这些数字并发送付款，为每个发送的短信存储一个新收据， payed_ts 是 now（）和 payed_until_ts 是 now（）+间隔30天。
<选择a，b（从b的表顺序中选择a，b）作为c组，选择a，b，然后选择a，b。
I see that in SQL, the GROUP BY has to precede ORDER BY expression. Does this imply that ordering is done after grouping discards identical rows/columns?
Because I seem to need to order rows by a timestamp column A first, THEN discarding rows with identical value in column A. Not sure how to accomplish this...
I am using MySQL 5.1.41
create table
(
A int,
B timestamp
)
The data could be:
+-----+-----------------------+
| A | B |
+-----+-----------------------+
| 1 | today |
| 1 | yesterday |
| 2 | yesterday |
| 2 | tomorrow |
+-----+-----------------------+
The results I am aiming for would be:
+-----+-----------------------+
| A | B |
+-----+-----------------------+
| 1 | today |
| 2 | tomorrow |
+-----+-----------------------+
Basically, I want the rows with the latest timestamp in column B (think ORDER BY), and only one row for each value in column A (think DISTINCT or GROUP BY).
My actual project details, if you need these:
In real life, I have two tables - users and 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)
)
The tables may include other columns, I omitted all that IMO is irrelevant here. As part of a mobile-payment scheme, I have to send SMS to users across the mobile cell network in periodic intervals, depending of course on whether the payment is due or not. The payment is actualized when the SMS is sent, which is premium-taxed. I keep records of all payments done with the payment_receipts table, for book-keeping, which simulates a real shop where both a buyer and seller get a copy of the receipt of purchase, for reference. This table stores my (sellers) copy of each receipt. The customers receipt is the received SMS itself. Each time an SMS is sent (and thus a payment is accomplished), the table is inserted a receipt record, stating who payed, when and "until when". To explain the latter, imagine a subscription service, but one which spans indefinitely until a user opt-out explicitly, at which point the user record is removed. A payment is made a month in advance, so as a rule, the difference between the payed_ts and payed_until_ts is 30 days worth of time.
Naturally I have a batch job that executes every day and needs to select a list of users that are due monthly payment as part of automatic subscription renewal. To link this to the dummy example earlier, the phone number column phone_nr is a and payed_until_ts is b, but in actual code there are two tables, which bring me to the following behavior and its implications: when a user record is removed, the receipt remains, for bookkeeping. So, not only do I need to group payments by date and discard all but the latest payment receipt date, I also need to watch out not to select receipts where there no longer is a matching user record.
I am solving the problem of selecting records that are due payment by finding the receipts with the latest payed_until_ts value (as in most cases there will be several receipts for each phone number) for each phone_nr and out of those rows I further need to leave only those phone_numbers where the payed_until_ts is earlier than the time the batch job executes. I loop over the list of these numbers and send out payments, storing a new receipt for each sent SMS, where payed_ts is now() and payed_until_ts is now() + interval 30 days.
Select a,b from (select a,b from table order by b) as c group by a;
这篇关于SQL：任何直接的方式来订购结果FIRST，THEN group by另一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
