mysql - 我无法让查询从我的产品表中提取所有记录

Question

我需要它来提取产品表中的所有产品，如果快速库存调查行中有相应的数据，它似乎只会提取产品的记录。

SELECT from_unixtime(j.job_datetime/1000+7200, '%d/%m/%Y %H:%i') AS tme,
  s.store_name,
  s.store_chain,
  dev.device_user_name,
  qss.quick_stock_survey_job_id,
  qss.quick_stock_survey_id,
  qss_lines.quick_stock_survey_lines_quantity,
  qss_lines.quick_stock_survey_lines_is_outofstock,
  prd.product_description,
  prd.product_order,
  cat.category_name,
  product_status
FROM product AS prd
LEFT JOIN quick_stock_survey_lines AS qss_lines
  ON qss_lines.quick_stock_survey_lines_product_id = prd.product_id
LEFT JOIN quick_stock_survey AS qss
  ON qss.quick_stock_survey_id = qss_lines.quick_stock_survey_lines_quick_stock_survey_id
LEFT JOIN job AS j
  ON j.job_id = qss.quick_stock_survey_job_id
LEFT JOIN store AS s 
  ON  j.job_store_id = s.store_id 
LEFT JOIN device_user AS dev
  ON dev.device_user_id = j.job_device_user_id
LEFT JOIN category AS cat
  ON cat.category_id = prd.product_default_category_id
WHERE dev.device_user_client_id = 173
  AND j.job_type = 6
  AND ((j.job_datetime/1000) BETWEEN (UNIX_TIMESTAMP()-604800) AND UNIX_TIMESTAMP()) 
ORDER BY prd.product_order, dev.device_user_name;

Answer 1

我建议将 WHERE 子句的逻辑移至相应的 ON 子句。原因是您当前的 WHERE 子句存在从结果集中过早过滤掉整个记录的风险，原因很简单，因为许多连接条件中的一些可能不匹配。

SELECT
    FROM_UNIXTIME(j.job_datetime/1000+7200, '%d/%m/%Y %H:%i') AS tme,
    s.store_name,
    s.store_chain,
    dev.device_user_name,
    qss.quick_stock_survey_job_id,
    qss.quick_stock_survey_id,
    qss_lines.quick_stock_survey_lines_quantity,
    qss_lines.quick_stock_survey_lines_is_outofstock,
    prd.product_description,
    prd.product_order,
    cat.category_name,
    product_status
FROM product as prd
LEFT JOIN quick_stock_survey_lines AS qss_lines
    ON qss_lines.quick_stock_survey_lines_product_id = prd.product_id
LEFT JOIN quick_stock_survey AS qss
    ON qss.quick_stock_survey_id = qss_lines.quick_stock_survey_lines_quick_stock_survey_id
LEFT JOIN job AS j
    ON j.job_id = qss.quick_stock_survey_job_id AND
       j.job_type = 6 AND
       ((j.job_datetime/1000) BETWEEN (UNIX_TIMESTAMP()-604800) AND
           UNIX_TIMESTAMP())
LEFT JOIN store AS s 
    ON j.job_store_id = s.store_id
LEFT JOIN device_user AS dev
    ON dev.device_user_id = j.job_device_user_id AND
       dev.device_user_client_id = 173
LEFT JOIN category AS cat
    ON cat.category_id = prd.product_default_category_id
ORDER BY
    prd.product_order,
    dev.device_user_name;