本文介绍如何在SQL查询中整合`reject_data`、`equipment`、`locations`表，并加入`emrs_data`的相关信息，以获取每个拒绝记录的最新EMRS代码和字符串。作者提到通过子查询获取与reject_time相关的最近EMRS数据，最终目标是得到包含设备、地点、拒绝时间以及EMRS代码和字符串的结果集。
reject_data
+-----------+-----------------+------------------+---------------+
| 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
扫一扫
1056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
