oracle多表联查分页sql优化

最新推荐文章于 2024-08-19 08:45:16 发布

weixin_44310277

最新推荐文章于 2024-08-19 08:45:16 发布

阅读量1k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/weixin_44310277/article/details/87973343

版权

本文介绍了一种使用ROWID辅助查询来提高多表联查速度的方法，结合BJHM_ROOMS_TABLE、BJHM_BASIC_HOTEL_TABLE、BJHM_SUBCODE_TABLE进行左连接操作，实现数据汇总并进行分页。查询涉及房间总数、使用房间数、空闲房间数、备注房间数等指标，并根据酒店类型和来源进行过滤。

摘要由CSDN通过智能技术生成

利用rowid辅助查询提高查询速度
SELECT
ROWNUM + ‘499990’ AS lineNum,
(
NVL (ROOMS_SINGLE_NUMBER, 0) + NVL (ROOMS_TWIN_NUMBER, 0) + NVL (ROOMS_DOUBLE_NUMER, 0) + NVL (ROOMS_THREE_NUMBER, 0) + NVL (ROOMS_SUITE_NUMBER, 0)
) AS roomsSum,
(
NVL (ROOMS_SINGLE_USE_NUMBER, 0) + NVL (ROOMS_TWIN_USE_NUMBER, 0) + NVL (ROOMS_DOUBLE_USE_NUMBER, 0) + NVL (ROOMS_THREE_USE_NUMBER, 0) + NVL (ROOMS_SUITE_USE_NUMBER, 0)
) AS useRoomsSum,
(
NVL (ROOMS_SINGLE_FREE_NUMBER, 0) + NVL (ROOMS_TWIN_FREE_NUMBER, 0) + NVL (ROOMS_DOUBLE_FREE_NUMBER, 0) + NVL (ROOMS_THREE_FREE_NUMBER, 0) + NVL (ROOMS_SUITE_FREE_NUMBER, 0)
) AS freeRoomsSum,
(
NVL (ROOMS_SINGLE_SUB_NUM, 0) + NVL (ROOMS_TWIN_SUB_NUM, 0) + NVL (ROOMS_DOUBLE_SUB_NUM, 0) + NVL (ROOMS_THREE_SUB_NUM, 0) + NVL (ROOMS_SUITE_SUB_NUM, 0)
) AS remarksRoomsSum,
r .ROOMS_HOTEL_NAME,
r .ROOMS_HOTEL_NO,
r .RO