计算枚举值？

发布于 2024-08-28 05:24:02 字数 576 浏览 3 评论 0原文

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

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

基本上字段可以是或否。

如何计算某个日期范围内每列有多少个“是”？

谢谢！！

原文

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?

Thanks!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

第七度阳光i 2024-09-04 05:24:02

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

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'

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

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'

回复收藏 0 原文

~没有更多了~