SELECT
id,
idpersona,
data,
settimana,
idlegenda,
creatoda,
creatoil
FROM
arp.turni
WHERE
idpersona = 10042
AND YEAR(data) = 2024
AND MONTH(data) = 1
AND id != (SELECT
turni.id
FROM
turni
JOIN
turni_layer_contabilita ON turni.idpersona = turni_layer_contabilita.idpersona
AND turni.data = turni_layer_contabilita.data
WHERE
MONTH(turni.data) = 1)
UNION SELECT
*
FROM
arp.turni_layer_contabilita
WHERE
idpersona = 10042
AND YEAR(data) = 2024
AND MONTH(data) = 1;
SELECT CASE WHEN t2.data IS NULL THEN t1.id ELSE t2.id END id,
idpersona,
data,
CASE WHEN t2.data IS NULL THEN t1.settimana ELSE t2.settimana END settimana,
CASE WHEN t2.data IS NULL THEN t1.idlegenda ELSE t2.idlegenda END idlegenda,
CASE WHEN t2.data IS NULL THEN t1.creatoda ELSE t2.creatoda END creatoda,
CASE WHEN t2.data IS NULL THEN t1.creatoil ELSE t2.creatoil END creatoil
FROM maintable t1
LEFT JOIN sidetable t2 USING (idpersona, data)
也许我已经找到了解决方案。
这个想法是构建一个没有主表中某些行的 UNION。
这些行通过INNER JOIN匹配日期和人员 ID进行标识。
看起来它有效,但我愿意接受改进(如果有的话)。
提前致谢
sidetable
如果表中存在匹配的行,则t2.data
(并且t2.idpersona
- 您可以自由地使用它)不为 NULL,并且根据输出列返回来自 的值sidetable
,否则t2.column
为 NULL 并且根据输出列返回来自 的值maintable
。