mysql - 学习SQL，获取最新配置

Question

我有 table :

-Parts-
ID
name

-ReportA-
ID
PART_ID
LOCATION_ID
GPS_ID
USER_ID
VALVE_ID
DATE

-ReportB-
ID
PART_ID
LOCATION_ID
TYPE_ID
NOTES
GPS_ID
DATE

(1) 我想获取所有具有报告 A 或 B 的部分:

SELECT
    *
FROM
    parts
WHERE
    id IN (SELECT id FROM reports_a UNION SELECT id FROM reports_b

完美 (1) 但现在我需要从最新报告中获取 LOCATION_ID 和 GPS_ID(A 或 B)。

示例:

-Parts-
1
Valve

-ReportA-
1
1
4
9
2
12
2013-02-01

-ReportB-
1
1
113
3
"Changed part"
90
2013-03-27

所以我需要一个 SQL 脚本来获取所有零件及其最新位置和 GPS id，上面的结果将产生:

PART_ID = 1
LOCATION_ID = 113
GPS_ID = 90

所以我将之前的 SQL 脚本更改为:

SELECT
    id AS PART_ID
    (
        (
           SELECT location_id FROM reports_a ORDER BY date DESC
           UNION
           SELECT location_id FROM reports_b ORDER BY date DESC
        )
        ORDER BY
           date DESC
        LIMIT 1  
    ) AS LOCATION_ID
FROM
    parts
WHERE
    id IN (SELECT id FROM reports_a UNION SELECT id FROM reports_b

但是，我无法按日期 DESC 排序，因为它不是 UNION 返回的字段？

有什么想法吗？

Answer 1

更新:我之前似乎误读了这个问题，下面的查询获取特定product_id的最新数据

SELECT 
    id,
    name,
    (
      SELECT 
        CONCAT( 'Location_id: ',location_id, ' gps_id: ', gps_id, ' date: ', `date` )
      FROM 
        ( SELECT `date`,part_id, location_id, gps_id FROM reportA
        UNION 
        SELECT `date`,part_id, location_id, gps_id FROM reportB
        ) t
      WHERE part_id = p.id  
      ORDER BY `date` desc 
      LIMIT 1) 
    as details
FROM 
    parts p

<强> SQLFiddle

您使用的结构不正确，理想情况下您应该将所有report_data放在一个表中，其中一列为report_type，可以是A或 B