mysql - 尝试在工作日和周末获取 COUNT()

Question

我有一个表，其中包含一个列(created)，该列存储创建该项目时的 unix 时间戳。

现在，我想要COUNT() 在工作日(周一至周五)创建的所有项目，与在周末(周六和周日)创建的所有项目进行比较。

我的查询是:

SELECT
  IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 0 AND WEEKDAY(FROM_UNIXTIME(`created`)) >= 4) THEN COUNT(*) AS `weekday`,
  IF (WEEKDAY(FROM_UNIXTIME(`created`)) <= 5) THEN COUNT(*) AS `weekend`
FROM `mytable`

但是我得到的错误是

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN COUNT(*), IF (WEEKDAY(FROM_UNIXTIME(created)) <= 5) THEN COUNT(*)' at line 3

非常感谢任何帮助。

Answer 1

您似乎没有使用正确的语法。 http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if

引自上述网址，

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.".

我相信这将是您查询的正确形式，

SELECT
DATE(FROM_UNIXTIME(`created`)) AS `date`,
IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 0 AND WEEKDAY(FROM_UNIXTIME(`created`)) <= 4, COUNT(*), 0), --WeekDay Count
IF (WEEKDAY(FROM_UNIXTIME(`created`)) >= 5 AND WEEKDAY(FROM_UNIXTIME(`created`)) <= 6, COUNT(*), 0) --Weekend Count

来自 mytable

我也找到了此链接以及工作日功能。这让我相信你的范围一开始就不正确。

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_weekday