mysql - 计数枚举值？

如果我的表看起来像这样:

CREATE TABLE `daily_individual_tracking` (
  `daily_individual_tracking_id` int(10) unsigned NOT NULL auto_increment,
  `daily_individual_tracking_date` date NOT NULL default ''0000-00-00'',
  `sales` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  `repairs` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  `shipping` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  PRIMARY KEY  (`daily_individual_tracking_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

基本上这些字段可以是是或否。

如何计算日期范围内每列的数量是多少？

谢谢!!

最佳答案

您可以像这样运行三个查询:

SELECT COUNT(*)
FROM daily_individual_tracking
WHERE sales = 'YES'
AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'

或者，如果您愿意，您可以像这样一次获得所有三个:

SELECT (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE sales = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS sales_count, (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE repairs = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS repairs_count, (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE shipping = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS shipping_count

另一种方法是使用 SUM 而不是 COUNT。你也可以试试这个，看看它如何影响性能:

SELECT
    SUM(sales = 'YES') AS sales_count,
    SUM(repairs = 'YES') AS repairs_count,
    SUM(shipping = 'YES') AS shipping_count
FROM daily_individual_tracking
WHERE daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'

关于mysql - 计数枚举值？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2595763/