SELECT p.*
FROM product p
LEFT JOIN product_time pt ON p.id = pt.product_id
WHERE pt.time BETWEEN ? AND ?
AND pt.type = 'PRODUCTION_DATE';
我需要更改我的查詢，而不是僅按 'PRODUCTION_DATE' 過濾，而是首先按 'RELEASE_DATE' 過濾。如果“RELEASE_DATE”型別的 production_time 不存在，請按“PRODUCTION_DATE”過濾。我該怎么做呢？
uj5u.com熱心網友回復：
在檢查實際值之前，您似乎希望確保發布日期存在 - 或 - 或者不存在并且生產日期存在。為此，每種型別都有一個左連接：
SELECT product.*
FROM product
LEFT JOIN product_time AS rel_time ON product.id = rel_time.product_id AND rel_time.type = 'RELEASE_DATE'
LEFT JOIN product_time AS prd_time ON product.id = prd_time.product_id AND prd_time.type = 'PRODUCTION_DATE'
WHERE rel_time.pk IS NOT NULL AND rel_time.time BETWEEN ? AND ?
OR rel_time.pk IS NULL AND prd_time.time BETWEEN ? AND ?
uj5u.com熱心網友回復：
使用LEFT JOIN兩次如下：
SELECT p.*
FROM product p
LEFT JOIN product_time pt1 ON p.id = pt1.product_id
AND pt1.time BETWEEN ? AND ?
AND pt1.type = 'RELEASE_DATE'
LEFT JOIN product_time pt ON p.id = pt.product_id
AND pt.time BETWEEN ? AND ?
AND pt.type = 'PRODUCTION_DATE'
WHERE ISNULL(pt1.time, pt.time) IS NOT NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/370205.html
標籤：sql