Mysql 我不断获得总工作作为回报

Question

我有sql查询，我喜欢在那里获取总记录。

我有一个雇主和这个雇主的600个工作。

我需要得到所有的雇主，但由于某种原因，我的查询返回雇主发布的总工作。

请让我知道我在这个查询中出了什么问题。

SELECT count(c.id) as total 
FROM employer as c INNER JOIN  job as j ON j.employerIDFK = c.id 
WHERE c.isActive=1 AND c.status=1 
AND j.isActive=1 
AND j.beenActive=1 
AND j.status=1 
AND DATE_ADD( j.createdAt, INTERVAL 30 DAY ) > NOW()

Answer 1

SELECT  COUNT(DISTINCT c.id) AS total 
FROM    employer c
JOIN    job j
ON      j.employerIDFK = c.id
WHERE   c.isActive = 1
        AND c.status = 1
        AND j.isActive = 1 
        AND j.beenActive = 1 
        AND j.status = 1 
        AND j.createdAt >= NOW() - INTERVAL 30 DAY

创建以下索引：

employer (isActive, status)
job (employerFKID)
job (isActive, beenActive, status, createdAt, employerFKID)

使查询工作得更快。

如果由于某些晦涩的原因您不愿意使用 DISTINCT ，您可以使用这个：

SELECT  COUNT(c.id) AS total
FROM    employer c
WHERE   c.isActive = 1
        AND c.status = 1
        AND c.id IN
        (
        SELECT  employerIDFK
        FROM    job j
        WHERE   j.isActive = 1
                AND j.beenActive = 1
                AND j.status = 1
                AND j.createdAt >= NOW() - INTERVAL 30 DAY
        )

但是，这可能效率较低，因为MySQL无法在此类查询中job领先。

Answer 2

您的查询应返回所有雇主的所有工作的计数，但必须匹配一些条件才能显示。您正在使用的隐式连接是内部连接。这要求所有条件都匹配，以便包含行。这意味着它只返回"isActive"、"beenActive"、"status=1"和 createdAt 少于 30 天的作业，其中雇主是"isActive"和"status=1"。检查您的数据，看看这是否是您想要的。

SELECT c.id AS employerID, count(*) as total 
FROM employer as c, job as j 
WHERE c.isActive=1 AND c.status=1 
AND j.employerIDFK = c.id 
AND j.isActive=1 
AND j.beenActive=1 
AND j.status=1 
AND DATE_ADD( j.createdAt, INTERVAL 30 DAY ) > NOW()
GROUP BY c.id

至于其他人试图对createdAt上的过滤器说些什么，MySQL将首先评估NOW（）（只有一次）。然后，它为每个 createdAt 日期添加 30 天，以查看它是否大于 NOW（）。MySQL有时会自动优化这一点，这取决于您的版本和其他一些因素，但一般来说，对每一行的createdAt日期执行函数以将其与常量表达式进行比较是不好的，因为MySQL不能利用createdAt列上的索引。

因此，您应该转换：

AND DATE_ADD( j.createdAt, INTERVAL 30 DAY ) > NOW()

对此：

AND j.createdAt > DATE_ADD( NOW(), INTERVAL -30 DAY )

这使得j.createdAt列成为普通列，因此MySQL现在可以利用该列的任何索引来查找过去少于30天的日期。

这相当于站在一个有100人的房间里，要求他们在生日上增加30天，然后问谁计算的日期比今天大。你刚刚让100个人工作。相反，通过从今天的日期中减去 30 天来预先计算标准，并简单地询问是否有人的生日大于该日期。你只需要做一个计算，它就使这100个人不必做艰苦的工作。

Answer 3

我怀疑"选择计数（*）作为总数"是你的问题。您要求 SQL 计算返回的所有内容。注意你正在"选择"的东西，因为这是唯一会退还给你的东西。

你

没有完全解释你在这里想要实现的目标。"我需要得到所有的雇主"是模糊的，难以理解。您想获得有关雇主的哪些数据？他们的身份证？他们的总工作？