我在下面有一個查詢。我選擇具有某些條件的列。在when陳述句的情況下，如果when子句為真，那么我選擇在when子句中定義的列，所以我重復相同的查詢。
我如何在下面防止這種情況發生，您是否有關于防止在 sql.conf 中重復相同查詢的想法。
CASE WHEN EXISTS(
SELECT
TOP 1
wo.licence_plate
FROM
work wo
JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
JOIN parameter par ON ds.status = par.code
WHERE
par.id = 1246
AND
par.code = 5
AND
wo.customer_id = cus.pk_id
AND
wo.delivery_date > DATEADD( day, -7, GETDATE() )
)
THEN
(
SELECT
TOP 1 wo.licence_plate
FROM
work wo (NOLOCK)
JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
JOIN parameter par ON ds.status = par.code
WHERE
par.parameter_group_id = 1246
and
par.code = 5
and
wo.customer_id = cus.pk_id
and
wo.delivery_date > DATEADD(DAY, -7, GETDATE())
) ELSE ' ' END
as result
FROM
customer cus
uj5u.com熱心網友回復：
' '如果記錄不存在，這將回傳。
SELECT ISNULL(SELECT
1 wo.licence_plate
FROM
dbo.tbl_asx_work_order wo
JOIN tbl_asx_work_order_delivery_status ds ON wo.pk_id = ds.fk_work_order_id
JOIN tbl_par_parameter par ON ds.status = par.code
WHERE
par.parameter_group_id = 1246
and
par.code = 5
and
wo.customer_id = cus.pk_id
and
wo.delivery_date > DATEADD(DAY, -7, GETDATE()), ' ')
uj5u.com熱心網友回復：
您可以OUTER APPLY為此使用 an 。如果沒有匹配項，
則使用COALESCE或ISNULL默認為空字串。
SELECT cus.*
, COALESCE(oa.licence_plate, '') AS licence_plate
FROM customer cus
OUTER APPLY (
SELECT TOP 1 wo.licence_plate
FROM work wo
JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
JOIN [parameter] par ON ds.status = par.code
WHERE wo.customer_id = cus.pk_id
AND par.id = 1246 AND par.code = 5
AND wo.delivery_date > DATEADD(day, -7, GETDATE())
) oa;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/349845.html
標籤：sql sql-server 查询语句