博文
数据库之LEFT JOIN
|||
今天在公司做业务开发的时候，恰巧利用Left join ... on ...从海量的数据中筛选数据，现对Left join和Right jion的用法总结如下：
例如：
(1) SELECT *
FROM VIEW_XTGLDX2025 SB
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000001' AND SB.HTZT = BT.ENUMID
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000002' AND SB.HTZL = BT.ENUMID
WHERE 1=1 AND SB.HTZT IN('00000001', '00000002') AND SB.HTZL IN('0000001');
(2) 考虑到查询效率问题，我们一般采用下面的方式查询
SELECT * FROM
(SELECT *
FROM VIEW_XTGLDX2025 SB
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000001' AND SB.HTZT = BT.ENUMID
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000002' AND SB.HTZL = BT.ENUMID
) ZT
WHERE 1=1 AND ZT.HTZT IN('00000001', '00000002') AND ZT.HTZL IN('0000001');
若希望将两张表合并，则考虑利用UNION。例如：
(3) SELECT * FROM
(SELECT
SB.HTZL,
BT.ENUMVALUE
FROM VIEW_XTGLDX2025 SB
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000001' AND SB.HTZT = BT.ENUMID
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000002' AND SB.HTZL = BT.ENUMID
UNION
SELECT
CG.HTZL,
BT.ENUMVALUE
FROM VIEW_XTGLDX2024 CG
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000001' AND CG.HTZT = BT.ENUMID
LEFT JOIN XTENUMBANK BT ON BT.ENUMLXID = '20000002' AND CG.HTZL = BT.ENUMID
) ZT
WHERE 1=1 AND ZT.HTZT IN('00000001', '00000002') AND ZT.HTZL IN('0000001');
还需要注意：一是尽量用BETWEEN ... AND ... 替代 “>=” “<=”；二是尽量避免使用NOT IN, EXISTS, GOTO关键字。
https://blog.sciencenet.cn/blog-448935-607610.html
上一篇：JavaScript异常处理
下一篇：跨浏览器的事件处理程序分析
