计算枚举值? [英] Count enumerated values?

问题描述

如果我的桌子是这样的:

If my table looks like this:

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

基本上，这些字段可以是是"或否".

basically the fields can be either yes or no.

在一个日期范围内，我如何计算每列的是多少?

How can I count how many yes's their are for each column over a date range?

谢谢！

推荐答案

您可以运行三个查询，如下所示:

You can either run three queries like this:

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

或者，如果需要，您可以一次获得所有三个，如下所示:

Or if you want you can get all three at once like this:

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.您也可以尝试这样做，看看它如何影响性能:

Another way to do it is to use SUM instead of COUNT. You could try this too to see how it affects the performance:

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'

这篇关于计算枚举值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！