导轨 3、不同计数倍数有条件

Question

一些背景。 我正在使用 Rails 3 作为拼车应用程序。 我正在使用完整日历作为jquery日历库。 当某一天有很多事件时，它的效果不太好。 所以，我想做的，是有两个"事件源"。 首先，我想返回所有按日期和类别分组的事件，这些事件的数量小于某个值。对于其他，我想返回计数大于值的所有记录。

抱歉，如果不清楚，一些示例数据可能会使其更加明显

基本记录结构：

|    id    |  date    |  category_id |
|    1     | 1-Aug    |       1      |
|    2     | 1-Aug    |       1      |
|    3     | 1-Aug    |       1      |
|    4     | 1-Aug    |       2      |
|    5     | 1-Aug    |       2      |
|    6     | 1-Aug    |       3      |

假设 3 是魔术计数数字（3 或更高应该分组），我希望一个选择返回

|    id    |  date    |  category_id |   count  |
|    1     | 1-Aug    |       1      |     3    |

（我实际上并不在乎 ID 是否可以使用 group_concat 组合（与 MYSQL），在我的用例中这并不重要 - 我想知道的是，在 8 月 1 日，类别 1 有 3 个条目

第二个选择应返回其他所有内容

|    id    |  date    |  category_id |   count  |
|    4     | 1-Aug    |       2      |     2    |
|    5     | 1-Aug    |       2      |     2    |
|    6     | 1-Aug    |       3      |     1    |

我真的不需要返回的计数，但关键是每个结果都有单独的行，没有计数>= 3。

目前我的SQL看起来像这样：

SELECT `pools`.*
FROM       `pools`
INNER JOIN `fields`    ON `fields`.`id`    = `pools`.`field_id` 
INNER JOIN `regions`   ON `regions`.`id`   = `fields`.`region_id` 
INNER JOIN `countries` ON `countries`.`id` = `regions`.`country_id`
WHERE `regions`.`country_id` = 1
  AND `pools`.`confirmed`    = 1
  AND (leaving_date >= '2012-04-30 00:00:00')
  AND (leaving_date <= '2012-06-04 00:00:00')
GROUP BY field_id, leaving_date
HAVING   count(*) >= 3
ORDER BY leaving_date

我可以遍历返回的数组 - 但如果可能的话，我宁愿在 SQL 端做。 还想在最少的数据库行程中做到这一点。一般的指针将不胜感激！

Answer 1

也许这对开始很有用：

CREATE TEMPORARY TABLE grouped_rows AS
  SELECT `pools`.*
  FROM       `pools`
  INNER JOIN `fields`     ON `fields`.`id`    = `pools`.`field_id` 
  INNER JOIN `regions`    ON `regions`.`id`   = `fields`.`region_id` 
  INNER JOIN `countries`  ON `countries`.`id` = `regions`.`country_id`
  WHERE `regions`.`country_id` = 1
    AND `pools`.`confirmed`    = 1
    AND (leaving_date >= '2012-04-30 00:00:00')
    AND (leaving_date <= '2012-06-04 00:00:00')
  GROUP BY field_id, leaving_date;
  HAVING   count(*) >= 3;

SELECT `pools`.*
FROM       `pools`
INNER JOIN `fields`     ON `fields`.`id`    = `pools`.`field_id` 
INNER JOIN `regions`    ON `regions`.`id`   = `fields`.`region_id` 
INNER JOIN `countries`  ON `countries`.`id` = `regions`.`country_id`
LEFT  JOIN grouped_rows ON grouped_rows.field_id     = pools.field_id
                       AND grouped_rows.leaving_date = pools.leaving_date
WHERE `regions`.`country_id` = 1
  AND `pools`.`confirmed`    = 1
  AND (leaving_date >= '2012-04-30 00:00:00')
  AND (leaving_date <= '2012-06-04 00:00:00')
  AND grouped_rows.some_not_null_field IS NULL
UNION ALL
SELECT * FROM grouped_rows
ORDER BY leaving_date;

DROP TEMPORARY TABLE grouped_rows;

我不知道你是否可以发出所有三个语句并在一次往返中获得UNION的结果。也许您可以将 temptable 的选择嵌入到UNION的两半中，但它可能会给 MySQL 服务器带来更多的工作（或不）。

更新：

我找到了一个您可以使用的单查询解决方案。这只是基本的骨架结构，但我认为您可以根据它转换您的查询：

http://sqlfiddle.com/#!2/f67e3/18

SELECT
  IF( grouped.gcat, NULL, item.id ) AS out_id
 ,item.cat AS out_cat
 ,COALESCE( grouped.gcount, 1 ) out_count
FROM item
LEFT JOIN
(
  SELECT   count(*) AS gcount, cat AS gcat
  FROM     item
  GROUP BY gcat
  HAVING   count(*) >= 3
) AS grouped
  ON item.cat = grouped.gcat
GROUP BY out_id