SQLite中主表多字段关联Lookup表的正确实现及问题修复

你的SQL存在三个关键问题，导致cashout和driver的姓名显示异常：

• 订单表未定义别名o，但查询中多处使用o.xxx调用字段，数据库无法识别该别名，会导致关联逻辑混乱。
• 员工表关联时字段不匹配：你提到员工表的主键是empl_id，但JOIN条件里写的是employee_id，字段名错误导致关联失效。
• Cashier、Cashout字段的CASE语句中，直接引用未指定别名的employees_tbl.employeeID，数据库会默认取第一个关联的员工表（e2）数据，所以会重复显示收银员姓名。

SELECT
    "orders_tbl"."order_number" AS "Order",
    DATE("orders_tbl"."order_entry_date") AS "Date",
    "orders_tbl"."order_entry_time" AS "Time",
 
    o.order_number,
    o.cashier_id, e2.empl_name as cashier_name,
    o.cashout_id, e3.empl_name as cashout_name,
    o.driver_id, e4.empl_name as driver_name,

    CASE 
     WHEN SUBSTR(o."customer_print_name",1,3) = "WEB" THEN "on-line"
     ELSE e2.empl_name
    END "Cashier",

    CASE
     WHEN SUBSTR(o."customer_print_name",1,3) = "WEB" AND o."cashout_id" = 0 THEN "pd online"
     WHEN SUBSTR(o."customer_print_name",1,3) = "WEB" AND o."cashout_id" <> 0 THEN e3.empl_name
     WHEN SUBSTR(o."customer_print_name",1,3) = "WEB" AND o."cashier_id" = 0 THEN e3.empl_name
     WHEN SUBSTR(o."customer_print_name",1,3) <> "WEB" THEN e2.empl_name
     ELSE "CASHOUT"
    END "Cashout",

    o."customer_print_name" AS "Customer",

    CASE o."order_type"
     WHEN 1 THEN "Take-out"
     WHEN 2 THEN "Dine-in"
     WHEN 3 THEN "Delivery"
     ELSE ""
   END "Type",

    o."ord_total" AS "Order $",

    CASE o."discount"
     WHEN 0 then ""
     ELSE "$"||o.discount
    END "Disc. $"

FROM "orders_tbl" o  -- 给订单表添加别名o

left join employees_tbl e2
        on o.cashier_id = e2.empl_id  -- 修正为员工表实际主键empl_id
left join employees_tbl e3
        on o.cashout_id = e3.empl_id
left join employees_tbl e4
        on o.driver_id = e4.empl_id

WHERE (o."record_type" = 1 OR o."record_type" = 5 OR o."record_type" = 6) 
    AND o."voided_order" = 0
    AND date(o."order_date") BETWEEN date('now','-3 days','localtime') AND date('now','localtime')

ORDER BY o."order_date" DESC

1. 给orders_tbl添加别名o，确保所有o.xxx的字段调用都能被数据库识别。
2. 将JOIN条件中的employee_id改为员工表实际的主键empl_id，保证三次关联分别对应收银员、结账员、配送员的正确数据。
3. 修改CASE语句中的员工姓名引用：Cashier字段使用e2.empl_name（收银员姓名），Cashout字段根据逻辑对应e3.empl_name（结账员姓名），避免引用未指定别名的表导致数据混乱。

内容的提问来源于stack exchange，提问作者Regis