mysql - 如何计算同一查询中的所有结果和特定计数 - MySql

Question

我需要计算特定日期的所有 FirstExtracted，并且我需要计算同一日期的所有 LastExtracted。因此，对于今天，我需要所有等于 2012-10-24 的 FirstExtracted 和 LastExtracted。

这是我目前所拥有的，但它没有调出 LastExtracted。它输出 LastExtracted 与 FirstExtracted 相同的计数:

(SELECT LastExtracted,FirstExtracted,
COUNT(FirstExtracted) AS FirstCount,
COUNT(LastExtracted) AS LastCount,
DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate 
FROM results
WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d'))
UNION ALL
(SELECT LastExtracted,FirstExtracted,
COUNT(FirstExtracted) AS FirstCount,
COUNT(LastExtracted) AS LastCount,
DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate,
DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate 
FROM results
WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') GROUP BY Firstdate) 
ORDER BY Firstdate DESC
LIMIT 20

也许我应该使用内部联接？

更新:所以使用你的查询我做了一些改变让它现在为我做其他事情。如果您查看此页面，我会提出查询

semesterold.com/code2.html

我想计算所有标题和 GROUP BY 艺术家。这将是一个数组。然后我希望子查询按艺术家匹配的搜索类型进行计数。所以如果 db 有 akon、rihanna、chris brown。我想要它计算每个艺术家有多少标题，比如 akon 有 100 个。然后我想显示标题的数量，然后计算这 100 个标题中有多少是 google、bing、akon 等的特定网站。

Answer 1

我会使用联合来分别获取两个结果(FirstExtracted 和 LastExtracted)，然后使用子查询合并它们。

SELECT
    sub.date, sum(sub.FirstCount) AS FirstCount,
    sum(sub.LastCount) AS LastCount 
  FROM (
      SELECT
          DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS date,
          COUNT(FirstExtracted) AS FirstCount, 0 AS LastCount
        FROM results
        GROUP BY date
      UNION ALL
        SELECT
            DATE_FORMAT(`LastExtracted`,'%Y-%m-%d'),
            0, COUNT(LastExtracted)
          FROM results
          GROUP BY date
    ) AS sub 
    GROUP BY sub.date

假设您今天第一次提取了 220 个条目，今天最近提取了 292 个条目。在其他日期的条目中，这会给你:

+------------+------------+-----------+
| date       | FirstCount | LastCount |
+------------+------------+-----------+
| 2012-10-24 |        220 |       292 |
+------------+------------+-----------+

更新 UNION 单独会给您以下结果。注意零占位符。

+------------+------------+-----------+
| date       | FirstCount | LastCount |
+------------+------------+-----------+
| 2012-10-24 |        220 |         0 |
+------------+------------+-----------+
| 2012-10-24 |          0 |       292 |
+------------+------------+-----------+