MySQL 表

拒绝数据

+-----------+-----------------+------------------+---------------+
| reject_id | reject_location | reject_equipment | reject_time   |
+-----------+-----------------+------------------+---------------+
| 1         | 7               | 6                | 1326795921000 |
+-----------+-----------------+------------------+---------------+
| 2         | 7               | 1                | 1326796641000 |
+-----------+-----------------+------------------+---------------+
| 3         | 7               | 6                | 1326799521000 |
+-----------+-----------------+------------------+---------------+
| 4         | 6               | 5                | 1326800781000 |
+-----------+-----------------+------------------+---------------+
| 5         | 7               | 3                | 1326802281000 |
+-----------+-----------------+------------------+---------------+
| 6         | 7               | 4                | 1326802941000 |
+-----------+-----------------+------------------+---------------+
| 7         | 7               | 1                | 1326814161000 |
+-----------+-----------------+------------------+---------------+
| 8         | 6               | 2                | 1328026700000 |
+-----------+-----------------+------------------+---------------+

设备

+--------------+------------------+
| equipment_id | equipment_string |
+--------------+------------------+
| 1            | Microdoser       |
+--------------+------------------+
| 2            | Monoblock        |
+--------------+------------------+
| 3            | Valve Magnet     |
+--------------+------------------+
| 4            | Checkweigher     |
+--------------+------------------+
| 5            | Microleak        |
+--------------+------------------+
| 6            | Capper           |
+--------------+------------------+

地点

+-------------+-----------------+
| location_id | location_string |
+-------------+-----------------+
| 1           | Fred Line 1     |
+-------------+-----------------+
| 2           | Fred Line 2     |
+-------------+-----------------+
| 3           | Fred Line 3     |
+-------------+-----------------+
| 4           | Bob Line 1      |
+-------------+-----------------+
| 5           | Bob Line 2      |
+-------------+-----------------+
| 6           | Bob Line 3      |
+-------------+-----------------+
| 7           | Jeff Line 1     |
+-------------+-----------------+
| 8           | Jeff Line 2     |
+-------------+-----------------+
| 9           | Jeff Line 3     |
+-------------+-----------------+

emrs_data

+---------+---------------+-----------+-------------+---------------+
| emrs_id | emrs_location | emrs_code | emrs_string | emrs_time     |
+---------+---------------+-----------+-------------+---------------+
| 1       | 8             | 8744751   | String Text | 1331051832000 |
+---------+---------------+-----------+-------------+---------------+
| 2       | 3             | 8660465   | String Text | 1331051832000 |
+---------+---------------+-----------+-------------+---------------+
| 3       | 6             | 8665447   | String Text | 1331055356000 |
+---------+---------------+-----------+-------------+---------------+
| 4       | 7             | 8762177   | String Text | 1331060531000 |
+---------+---------------+-----------+-------------+---------------+
| 5       | 4             | 8547253   | String Text | 1331061898000 |
+---------+---------------+-----------+-------------+---------------+
| 6       | 9             | 8744580   | String Text | 1331062654000 |
+---------+---------------+-----------+-------------+---------------+
| 7       | 2             | 8668716   | String Text | 1331064810000 |
+---------+---------------+-----------+-------------+---------------+
| 8       | 1             | 8665436   | String Text | 1331066757000 |
+---------+---------------+-----------+-------------+---------------+
| 9       | 5             | 8761458   | String Text | 1331066847000 |
+---------+---------------+-----------+-------------+---------------+
| 10      | 8             | 8743520   | String Text | 1331068372000 |
+---------+---------------+-----------+-------------+---------------+
| 11      | 3             | 8708691   | String Text | 1331070587000 |
+---------+---------------+-----------+-------------+---------------+
| 12      | 7             | 8811149   | String Text | 1331071045000 |
+---------+---------------+-----------+-------------+---------------+

当前查询

以下查询是我目前使用的查询，请注意我使用了“IN”，因为它们是动态的，与“BETWEEN”值相同。

SELECT location_string, equipment_string, reject_time 
FROM reject_data, equipment, locations
WHERE reject_equipment = equipment_id 
    AND reject_location = location_id 
    AND reject_location IN (7) 
    AND reject_equipment IN (1,2,3,4,5,6) 
    AND reject_time BETWEEN 0 AND 1331113803000
ORDER BY reject_id DESC 
LIMIT 100

问题

我想做的是在右侧添加两列，包含 emrs_code 和 emrs_string，请参见下表作为示例。我遇到的问题是试图为每个拒绝记录获取相关的 emrs_code 和 emrs_string，我只想在每次拒绝时返回一组 emrs 结果，并且 emrs 数据需要来自与 reject_time 相比最近的前一个时间，有点难解释一下，这里有一个查询可以让我获得预定位置和时间的正确信息:

SELECT emrs_code, emrs_string
FROM `rejectlogging`.`emrs_data`
WHERE emrs_time <= 1331113803000 AND emrs_location = 7
ORDER BY emrs_time DESC
LIMIT 1;

我基本上坚持将上述内容合并到原始查询中。任何帮助将非常感激。谢谢。

期望的结果

+-----------------+------------------+---------------+-----------+-------------+
| location_string | equipment_string | reject_time   | emrs_code | emrs_string |
+-----------------+------------------+---------------+-----------+-------------+
| A7              | Microleak        | 1331064910000 | 8762177   | String Text |
+-----------------+------------------+---------------+-----------+-------------+
| A3              | Checkweigher     | 1331107261000 | 8708691   | String Text |
+-----------------+------------------+---------------+-----------+-------------+
| A1              | Microdoser       | 1331107166000 | 8665436   | String Text |
+-----------------+------------------+---------------+-----------+-------------+
| A2              | Microdoser       | 1331107161000 | 8668716   | String Text |
+-----------------+------------------+---------------+-----------+-------------+
| A4              | Microleak        | 1331105836000 | 8547253   | String Text |
+-----------------+------------------+---------------+-----------+-------------+

最佳答案

试试这个:-(更新)

SELECT location_string, equipment_string, reject_time , (SELECT emrs_code
FROM `rejectlogging`.`emrs_data`
WHERE emrs_time <= reject_data.reject_time AND emrs_location = reject_data.reject_location
ORDER BY emrs_time DESC
LIMIT 1) as emrs_code,
(SELECT emrs_string
FROM `rejectlogging`.`emrs_data`
WHERE emrs_time <= reject_data.reject_time AND emrs_location = reject_data.reject_location 
ORDER BY emrs_time DESC
LIMIT 1) as emrs_string
FROM reject_data, equipment, locations
WHERE reject_equipment = equipment_id 
    AND reject_location = location_id 
    AND reject_location IN (7) 
    AND reject_equipment IN (1,2,3,4,5,6) 
    AND reject_time BETWEEN 0 AND 1331113803000
ORDER BY reject_id DESC 
LIMIT 100

关于MySQL 添加新列到查询结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9600092/