Question

i have data like this.
Table customer

| customerId | fullName     | sex
| 1          | Budi Arsana  | Male
| 2          | Peter Parker | Male

缩略语

| Id | bookingDate | customer
| 1  | 2011-09-01  | 1
| 2  | 2011-09-30  | 1
| 3  | 2011-09-01  | 2

而我只想得出以下结果:选择有交易客户的2张表格,到最近只有1份交易。和

| customerId | fullName     | sex  | transId | bookingDate
| 1          | Budi Arsana  | Male | 2       | 2011-09-30
| 2          | Peter Parker | Male | 3       | 2011-09-01

Answer 1

SELECT c.customerId, c.fullName, c.sex, t.Id AS transId, t.bookingDate
    FROM customer c
        INNER JOIN (SELECT customer, MAX(bookingDate) AS MaxDate
                        FROM transaction
                        GROUP BY customer) q
            ON c.customerId = q.customer
        INNER JOIN transaction t
            ON q.customer = t.customer
                 AND q.MaxDate = t.bookingDate;

Answer 2

加入:

SELECT c.customerId, c.fullName, c.sex, t.Id AS transId, t.bookingDate
  FROM customer c
       LEFT JOIN transaction t ON t.customer = c.customerId
       LEFT JOIN transaction t2 ON t2.customer = t.customer
   AND t2.bookingDate > t.bookingDate 
 WHERE t2.Id IS NULL;

原则是,你创建另一个加入<代码>transaction table (t 2)和用于从最后结果中除最新记录外全部删除(t)。

洞穴: 如果每个客户每天有多个书记,这不会奏效。在此情况下,你可能希望从日期到时间。

友情链接