以下SQL 语句如何使用 TP5.1 查询构造器查询

最新推荐文章于 2022-07-04 08:00:00 发布

原创最新推荐文章于 2022-07-04 08:00:00 发布 · 748 阅读

0 ·

CC 4.0 BY-SA版权

thinkphp5.1 专栏收录该内容

9 篇文章

订阅专栏

本文展示了一个复杂的原生SQL查询语句，该查询用于从houses表中统计未被有效租赁合同关联的房子数量，排除了已被删除的数据及部分特定状态的记录。

原生SQL

SELECT
    count( `id` ) AS num 
FROM
    `houses` h 
WHERE
    (
    `id` NOT IN (
SELECT
    `house_id` 
FROM
    `contract_lord` 
WHERE
    `deleted_at` IS NULL 
    AND ( end_type IS NULL OR end_type = '' ) 
    AND ( end_at > curdate( ) ) 
    AND `house_id` IS NOT NULL 
GROUP BY
    `house_id` 
    ) 
    OR `id` NOT IN ( SELECT `house_id` FROM `contract_lord` WHERE `house_id` IS NOT NULL AND `deleted_at` IS NULL GROUP BY `house_id` ) 
    ) 
    AND h.`is_nrcy` = 0 
    AND `h`.`deleted_at` IS NULL 
    AND 1 = 1