查询具有 () 的计数值

发布于 2024-09-14 04:16:26 字数 918 浏览 10 评论 0原文

我在根据填充率显示记录的查询时遇到困难。

例如，空缺可以没有预订或有一些预订。如果空缺有预订，则可以采用“有效 [1]”、“待处理 [0]”的形式。如果空缺有预订记录，我到目前为止编写的查询就可以工作，但如果没有预订记录，我就无法让它工作。

我对预订空缺的查询（有效）如下：-

SELECT v.*, j.job_category_name, bu.business_unit_name 
FROM vacancy v 
INNER JOIN job_category j ON j.job_category_id = v.job_category_id 
INNER JOIN business_unit bu ON bu.business_unit_id = v.business_unit_id 
INNER JOIN booking b ON b.vacancy_id = v.vacancy_id 
INNER JOIN booking_status bs ON bs.id = b.booking_status_id 
WHERE 
    v.vacancy_status <> 'revoked' AND 
    v.vacancy_reference <> 'auto-generated booking' AND 
    v.business_unit_id IN (series of primary keys) AND 
(bs.booking_status_type_id = 1 OR bs.booking_status_type_id = 2) 
GROUP BY v.vacancy_id 
HAVING v.vacancy_limit > count(b.booking_id)
ORDER BY v.vacancy_id DESC

我认为通过将 b 和 bs 的连接更改为 LEFT JOIN 会有效，但事实并非如此。

有什么想法吗？

原文

I'm having difficulty with a query which displays records according to their fill rate.

For instance, a vacancy can have no bookings or some bookings. If a vacancy has bookings, they can be in the form of 'active [1]', 'pending [0]'. The query I have written so far works if the vacancy has booking records but I can't get it to work if it doesn't have booking records.

My query (which works) for vacancies with a booking is as follows:-

SELECT v.*, j.job_category_name, bu.business_unit_name 
FROM vacancy v 
INNER JOIN job_category j ON j.job_category_id = v.job_category_id 
INNER JOIN business_unit bu ON bu.business_unit_id = v.business_unit_id 
INNER JOIN booking b ON b.vacancy_id = v.vacancy_id 
INNER JOIN booking_status bs ON bs.id = b.booking_status_id 
WHERE 
    v.vacancy_status <> 'revoked' AND 
    v.vacancy_reference <> 'auto-generated booking' AND 
    v.business_unit_id IN (series of primary keys) AND 
(bs.booking_status_type_id = 1 OR bs.booking_status_type_id = 2) 
GROUP BY v.vacancy_id 
HAVING v.vacancy_limit > count(b.booking_id)
ORDER BY v.vacancy_id DESC

I thought by changing the join of b and bs to LEFT JOIN would have worked, but it hasn't.

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

许久 2024-09-21 04:16:26

如果没有可供使用的架构副本，则很难准确判断，但是当您将 booking 和 bookingstatus 更改为 LEFT JOIN 时，您还可以修改您的 WHERE 子句，使其内容类似于：

WHERE 
  v.vacancy_status <> 'revoked' AND 
  v.vacancy_reference <> 'auto-generated booking' AND 
  v.business_unit_id IN (series of primary keys) AND 
  (ISNULL(bs.booking_status_type_id, 1) = 1 OR ISNULL(bs.booking_status_type_id, 2) = 2)

即确保满足完整的 WHERE 子句，因此不会从 booking 和 bookingstatus 为 NULL？

Without a copy of your schema to work from, it's difficult to tell exactly, but when you changed booking and bookingstatus to LEFT JOINs, did you also modify your WHERE clause so that it read something like:

WHERE 
  v.vacancy_status <> 'revoked' AND 
  v.vacancy_reference <> 'auto-generated booking' AND 
  v.business_unit_id IN (series of primary keys) AND 
  (ISNULL(bs.booking_status_type_id, 1) = 1 OR ISNULL(bs.booking_status_type_id, 2) = 2)

i.e. Ensured that the full WHERE clause would be satisfied, thus not stripping out the records where all the values for columns from booking and bookingstatus were NULL?

回复收藏 0 原文