MySQL FULLTEXT 来自多个表的分数

Question

我正在尝试搜索卡车维护记录的 MySQL 数据库。我正在搜索的表是 Maintenance、Truck 和 Maintenance_parts。我需要做什么才能从卡车和 Maintenance_Parts 表中获取全文分数以添加到维护表中的分数？

SELECT maintenance_id, some_id, type_code, service_date, mileage, mg_id, mg_type, comments, work_done,
    MATCH(comments, work_done) AGAINST( 'valve tire' ) AS score
    FROM maintenance
    WHERE  type_code = 'truck'
    AND some_id IN (
        SELECT truck_id FROM truck 
        WHERE MATCH( truck_number, make, model, engine, vin_number, transmission_number, comments) AGAINST( 'P' )
        OR truck_number LIKE '%P%'
    )
    AND maintenance_id IN( 
        SELECT maintenance_id FROM maintenance_parts WHERE MATCH( part_num, part_desc, part_ref) AGAINST( 'valve tire' )
    )
    OR MATCH(comments, work_done) AGAINST( 'valve tire' )
    AND status = 'A' ORDER BY score DESC LIMIT 0, 30

Answer 1

您必须重写查询以消除子查询，并使用连接来代替。一旦子查询的全文搜索移动到父查询，您应该能够在那里加入分数。