mysql left join 仅来自基表的第一行

Question

我一直在思考如何将一个表中的数据 LEFT JOIN 到 BASE 表中......但仅针对基表中的第一行......这甚至可能吗？

例如:

表 1

item_id  |  insert_datetime  |  value  
---------------------------------------
   2     | 1/1/2014 12:54:05 |   65
   2     | 1/1/2014 12:57:05 |   65
   3     | 8/7/2014 10:01:47 |   87
   3     | 9/8/2014 09:37:21 |   33
   3     | 9/8/2014 09:42:21 |   33
   4     | 2/9/2014 15:22:01 |   58

TABLE2(按 ID、日期 [非日期时间]、值左连接)

item_id  |   drop_datetime   |  value  
---------------------------------------
   2     | 1/1/2014 00:00:00 |   65
   3     | 9/8/2014 00:00:00 |   33

我需要做这样的事情:

SELECT t1.item_id
, t1.insert_datetime as inserted
, t1.value as ins_value
##
, t2.drop_datetime as cancelled
, t2.value as cld_value
##
FROM table1 t1
##
LEFT JOIN table2 t2
ON t2.item_id = t1.item_id
AND date(t2.drop_datetime) = date(t1.insert_datetime)
AND t1.value = t2.value
##
WHERE 1=1

所以这意味着我需要获取哪些值被取消了......但是在 item_id = 2 的情况下有以下情况:

项目于 2014 年 1 月 1 日 12:54:05 发货(值为 65)...由于参数错误而被取消(T2 中的第一行)并于 2014 年 1 月 1 日 12:57 重新发货:05 具有相同的值

item_id = 2 我得到的是:

item_id  |     inserted      |  ins_value |  cancelled        |   cld_value
-----------------------------------------------------------------------------
   2     | 1/1/2014 12:54:05 |     65     | 1/1/2014 00:00:00 |      65
   2     | 1/1/2014 12:57:05 |     65     | 1/1/2014 00:00:00 |      65

但我需要得到这个:

item_id  |     inserted      |  ins_value |  cancelled        |   cld_value
-----------------------------------------------------------------------------
   2     | 1/1/2014 12:54:05 |     65     | 1/1/2014 00:00:00 |      65
   2     | 1/1/2014 12:57:05 |     65     |                   |       

因为在现实中，只是第一个被取消了.. 有可能在 MySQL 中得到这个结果吗？

注意:不幸的是，一个项目也可能在一小时内被取消两三次

                                   **EDIT 1**

如果有帮助...所有数据都在一张表中，带有 SHIP(1) 或 CANCLED(2) 的症状

源表

id  |  item_id  |  insert_datetime  |  value  |  symptom  
-----------------------------------------------------------
1   |     2     | 1/1/2014 12:54:05 |   65    |     1
2   |     2     | 1/1/2014 00:00:00 |   65    |     2
3   |     2     | 1/1/2014 12:57:05 |   65    |     1
4   |     3     | 8/7/2014 10:01:47 |   87    |     1
5   |     3     | 9/8/2014 09:37:21 |   33    |     1
6   |     3     | 9/8/2014 00:00:00 |   33    |     2
7   |     3     | 9/8/2014 09:42:21 |   33    |     1
8   |     4     | 2/9/2014 15:22:01 |   58    |     1

这会有帮助吗？

                                   **POSSIBLE SOLUTION**

Ít 似乎这工作正常:

SELECT t1.item_id
, t1.insert_datetime as inserted
, t1.value as ins_value
##
, t2.drop_datetime as cancelled
, t2.value as cld_value
##
FROM table1 t1
##
LEFT JOIN table2 t2
ON t2.item_id = t1.item_id
AND date(t2.drop_datetime) = date(t1.insert_datetime)
AND t1.value = t2.value
AND t1.id < t2.id   ## <- this should do the trick
##
WHERE 1=1

Answer 1

你可以试试这个:

SQL Fiddle

MySQL 5.5.32 架构设置:

CREATE TABLE Table1
    (`id` int, `item_id` int, `insert_datetime` datetime, `value` int, `symptom` int)
;

INSERT INTO Table1
    (`id`, `item_id`, `insert_datetime`, `value`, `symptom`)
VALUES
    (1, 2, '2014-01-01 12:54:05', 65, 1),
    (2, 2, '2014-01-01 00:00:00', 65, 2),
    (3, 2, '2014-01-01 12:57:05', 65, 1),
    (4, 3, '2014-07-08 10:01:47', 87, 1),
    (5, 3, '2014-08-09 09:37:21', 33, 1),
    (6, 3, '2014-08-09 00:00:00', 33, 2),
    (7, 3, '2014-08-09 09:42:21', 33, 1),
    (8, 3, '2014-08-09 00:00:00', 33, 2),
    (9, 3, '2014-08-09 09:48:29', 33, 1),
    (10, 3, '2014-09-02 15:22:01', 58, 1)
;

查询 1:

SELECT DISTINCT t1.item_id
, t1.insert_datetime as inserted
, t1.value as ins_value
, ifnull(t2.insert_datetime,'') as cancelled
, ifnull(t2.value,'') as cld_value
FROM table1 t1
LEFT JOIN table1 t2
ON t2.item_id = t1.item_id
AND date(t2.insert_datetime) = date(t1.insert_datetime)
AND t1.value = t2.value
AND t1.id < t2.id 
AND t2.symptom = 2
WHERE t1.symptom = 1 

Results :

| ITEM_ID |            INSERTED | INS_VALUE |           CANCELLED | CLD_VALUE |
|---------|---------------------|-----------|---------------------|-----------|
|       2 | 2014-01-01 12:54:05 |        65 | 2014-01-01 00:00:00 |        65 |
|       2 | 2014-01-01 12:57:05 |        65 |                     |           |
|       3 | 2014-07-08 10:01:47 |        87 |                     |           |
|       3 | 2014-08-09 09:37:21 |        33 | 2014-08-09 00:00:00 |        33 |
|       3 | 2014-08-09 09:42:21 |        33 | 2014-08-09 00:00:00 |        33 |
|       3 | 2014-08-09 09:48:29 |        33 |                     |           |
|       3 | 2014-09-02 15:22:01 |        58 |                     |           |