mysql - 结合 MySQL 查询,其中一个是排除查询
问题描述
第一个问题在这里,所以如果我错过了之前提出的问题,或者没有很好地格式化,我深表歉意......我的公司有一个我正在尝试改进的自定义 CRM + 数据库。我们需要找到一份不会更新其年度服务的物业清单。目前,我们通过首先查找将更新其服务的属性来做到这一点,这是通过以下查询完成的:
SELECT DISTINCT
j.`property_id`
FROM
`jobs` AS j
LEFT JOIN `property` AS p
ON j.`property_id` = p.`property_id`
LEFT JOIN `agency` AS a
ON p.`agency_id` = a.`agency_id`
INNER JOIN `property_services` AS ps
ON (
j.`property_id` = ps.`property_id`
AND j.`service` = ps.`alarm_job_type_id`
)
WHERE ps.`service` = 1
AND a.`country_id` = 1
AND (
j.`status` = 'Pending'
OR j.`date` IS NULL
OR j.`date` = '0000-00-00'
OR j.`job_type` = 'Once-off'
OR j.`job_type` = '240v Rebook'
OR (
j.`date` >= '2019-04-22'
AND j.`job_type` = 'Yearly Maintenance'
)
)
然后我们找到我们想要显示给用户的详细信息,不包括过程中的其他项目:
SELECT DISTINCT
j.`property_id`,
p.`address_1` AS p_address1,
p.`address_2` AS p_address2,
p.`address_3` AS p_address3,
p.`state` AS p_state,
p.`postcode` AS p_postcode,
a.`agency_id`,
a.`agency_name`
FROM
`jobs` AS j
LEFT JOIN `property` AS p
ON j.`property_id` = p.`property_id`
LEFT JOIN `agency` AS a
ON p.`agency_id` = a.`agency_id`
INNER JOIN `property_services` AS ps
ON (
j.`property_id` = ps.`property_id`
AND j.`service` = ps.`alarm_job_type_id`
)
WHERE p.`property_id` NOT IN (INSERT HERE THE IDS YOU GOT FROM THE FIRST QUERY)
AND ps.`service` = 1
AND p.`deleted` = 0
AND p.`agency_deleted` = 0
AND a.`status` = 'active'
AND a.`country_id` = 1
ORDER BY j.`property_id` DESC
LIMIT 0, 50
理想情况下,我想组合这些查询,或者以某种方式优化它们,因为页面当前需要 2 分钟以上才能加载,即使有索引也是如此。再次道歉,我不是数据库或查询专家,我很确定学位只包括一两个主题!
解决方案
作为记录(以防有人发现它有帮助),组合版本是:
SELECT DISTINCT
j.`property_id`,
p.`address_1` AS p_address1,
p.`address_2` AS p_address2,
p.`address_3` AS p_address3,
p.`state` AS p_state,
p.`postcode` AS p_postcode,
a.`agency_id`,
a.`agency_name`
FROM
`jobs` AS j
LEFT JOIN `property` AS p
ON j.`property_id` = p.`property_id`
LEFT JOIN `agency` AS a
ON p.`agency_id` = a.`agency_id`
INNER JOIN `property_services` AS ps
ON (
j.`property_id` = ps.`property_id`
AND j.`service` = ps.`alarm_job_type_id`
)
WHERE p.`property_id` NOT IN
(SELECT DISTINCT
j.`property_id`
FROM
`jobs` AS j
LEFT JOIN `property` AS p
ON j.`property_id` = p.`property_id`
LEFT JOIN `agency` AS a
ON p.`agency_id` = a.`agency_id`
INNER JOIN `property_services` AS ps
ON (
j.`property_id` = ps.`property_id`
AND j.`service` = ps.`alarm_job_type_id`
)
WHERE ps.`service` = 1
AND a.`country_id` = 1
AND (
j.`status` = 'Pending'
OR j.`date` IS NULL
OR j.`date` = '0000-00-00'
OR j.`job_type` = 'Once-off'
OR j.`job_type` = '240v Rebook'
OR (
j.`date` >= '2019-05-08'
AND j.`job_type` = 'Yearly Maintenance'
)
))
AND ps.`service` = 1
AND p.`deleted` = 0
AND p.`agency_deleted` = 0
AND a.`status` = 'active'
AND a.`country_id` = 1
AND (
j.`status` != 'Booked'
AND j.`status` != 'To Be Booked'
AND j.`status` != 'Send Letters'
AND j.`status` != 'On Hold'
AND j.`status` != 'On Hold - COVID'
AND j.`status` != 'Pre Completion'
AND j.`status` != 'Merged Certificates'
)
AND j.`date` > DATE_ADD(NOW(), INTERVAL - 350 DAY)
ORDER BY j.`property_id` DESC
StackOverflow 之外的人帮助合并,但没有帮助......我们最终不得不添加一个标记并搜索它,因为这个查询在我们的数据库上运行了 193 秒。
此外,我完全得到了提供最小可重现示例的要求,而我没有这样做是我的错。
推荐阅读
- html - 使用 CSS Flexbox 的响应式字母块
- python - 需要帮助为我的链表类实现 remove() 方法
- azure - terraform azurerm_virtual_machine_extension 设置上的动态块
- python - Pandasql 有条件
- c# - asp.net mvc 5 基于角色的安全性
- python - 与networkx中边缘和节点颜色的条件过滤器的多重交集比较?
- airflow - Cloud Composer 将自定义插件导入到所有现有的 dag
- r - RStudio:创建并排箱线图以比较价格
- reactjs - 更改 url 并重新渲染反应路由器
- c++ - 搜索具有特定扩展名的文件 - 使用 recursive_directory_iterator -
图书馆