用于工作日/周末计数的MySQL/PHP算法(每月)

Question

在开始编码之前，我想先想出一个好的设计。基本上，我有一个充满日期的数据库表，以及与这些日期相关联的用户(日期是sql格式的)。

使用PHP，我希望为每个用户计算(总计计算)他们与多少工作日相关联，以及他们与多少个周末相关联。我需要每个月的总数，以及一个总计。这需要从8月到5月“运行”。

我知道我可以确定一天是周末还是工作日：

 $date = '2007/08/30';
 $weekday = date('l', strtotime($date));

为了确定月份，我可以使用SQL，并使用case语句，例如，从“10”获取“十月”：

 SELECT MONTH(DATE_SPECIFIED);

不过，我最不确定的是，要经历什么过程。我可以很容易地进行大量的查询，但是效率很低。理想情况下，我正在考虑在html表中打印结果。

有人能给你提供一些建议或建议吗？

谢谢。

编辑：

我有一个users表和一个eventcal表。

以下是users表：

 CREATE TABLE `users` (
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `role` varchar(75) NOT NULL,
  `region` tinyint(4) unsigned default NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(75) NOT NULL,
  `new_pass` varchar(5) default NULL,
  PRIMARY KEY  (`username`),
  KEY `role` (`role`),
  KEY `region` (`region`),
  CONSTRAINT `users_ibfk_2` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON UPDATE CASCADE,
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`role`) ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面是事件表：

 CREATE TABLE `eventcal` (
  `id` int(11) NOT NULL auto_increment,
  `region` tinyint(3) unsigned NOT NULL,
  `primary` varchar(25) NOT NULL,
  `secondary` tinyint(1) NOT NULL,
  `eventDate` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `primary_2` (`primary`),
  CONSTRAINT `eventcal_ibfk_1` FOREIGN KEY (`primary`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

注意，evencal.primary有一个对users.username的外键引用.

Answer 1

你应该能.(假设您有一个日期表、一个用户表和一个链接表)

SELECT MONTH(eventDate), DAYOFWEEK(eventDate), 
COUNT(*) 
FROM eventcal as e 
LEFT JOIN users as u ON e.primary = u.username 
GROUP BY MONTH(eventDate), DAYOFWEEK(eventDate);

这应该会返回一堆行，每个列最多每月返回7行。一个是日期索引(1 =星期日，7=星期六)，一个是数字月份，另一个是附加到该天的用户数。

如果你需要限制在某些月份，你可以添加一个where子句，就像.

WHERE eventDate BETWEEN '20090501' AND '20091001'

您还可以在组后使用WITH ROLLUPS关键字，让mysql返回每周或每月的总数。但是这通常比它节省的麻烦更多，因为您必须编写逻辑来确定当前行是否为总计。

编辑：

如果您想直接获得周末/工作日的值：

SELECT MONTH(eventDate), IF(WEEKDAY(eventDate)<5, 'weekday', 'weekend') AS DAY,
COUNT(*) 
FROM eventcal as e 
LEFT JOIN users as u ON e.primary = u.username 
GROUP BY MONTH(eventDate), IF(WEEKDAY(eventDate)<5, 'weekday', 'weekend');

这将按上述方式返回，但每月只有2行，其中一行用于平日，一行用于周末。

(虽然我不能完全肯定你能按这样的计算值进行分组，但我认为你可以--如果你尝试了，请告诉我！)

Answer 2

MySQL有一个工作日()函数：

工作日(日期)

返回工作日的日期索引(0 =星期一，1=星期二，…6=星期日)。

您应该能够在按月使用组的MySQL查询中使用此方法。

我的SQL知识非常生疏，我确信这不是有效的SQL，但您应该从中获得要点。

SELECT user, weekdaysPerMonth, month
FROM
(SELECT COUNT(*) AS weekdaysPerMonth, user, date, month
 FROM table
 WHERE WEEKDAY(date) > 0 AND WEEKDAY(date) < 5)
GROUP BY month