在MySQL中对多个数据运行相同SELECT的最简单方法

我有一个非常简单的选择,让我们说：

SELECT COUNT(added) FROM users WHERE added < "2015-07-30"

我是否可以以某种简单的方式运行此选择,不仅适用于给定的日期,而且还可以说,7天,每行显示计算到特定日期？

编辑：
这是我构建表的SQL：

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,added DATE
);

INSERT INTO users (added) VALUES ("2015-07-30");
INSERT INTO users (added) VALUES ("2015-07-29");
INSERT INTO users (added) VALUES ("2015-07-28");
INSERT INTO users (added) VALUES ("2015-07-21");
INSERT INTO users (added) VALUES ("2015-07-26");
INSERT INTO users (added) VALUES ("2015-07-25");
INSERT INTO users (added) VALUES ("2015-07-24");
INSERT INTO users (added) VALUES ("2015-07-23");
INSERT INTO users (added) VALUES ("2015-07-29");
INSERT INTO users (added) VALUES ("2015-07-22");
INSERT INTO users (added) VALUES ("2015-07-20");
INSERT INTO users (added) VALUES ("2014-02-10");

我期待这样的结果：

    DATE   | Count |
--------------------                   
2015-07-30 |  12   |
2015-07-29 |  11   |   
2015-07-28 |  10   |
2015-07-27 |   9   |
2015-07-26 |   9   |
2015-07-25 |   8   |
2015-07-24 |   7   |

SELECT
  SUM(added >= CURRENT_DATE() - INTERVAL 14 DAY) AS last_14_days,SUM(added >= CURRENT_DATE() - INTERVAL 7 DAY) AS last_7_days,SUM(added = CURRENT_DATE()) AS today
FROM users
WHERE
  added >= CURRENT_DATE() - INTERVAL 14 DAY

您还可以使用它来计算所有记录之前< “2015-07-30”和过去7天,但会有不好的表现,我建议你改用内联查询：

SELECT
  (SELECT COUNT(added) FROM users WHERE added < "2015-07-30") AS before_30,(SELECT COUNT(added) FROM users WHERE added >= "2015-09-08") AS last_7

或UNION查询：

SELECT "Before 30" as Interval,COUNT(*) AS total
FROM users WHERE added < "2015-07-30"
UNION ALL
SELECT "Last 7" as Interval,COUNT(*) AS total
FROM users WHERE added >= "2015-09-08"

编辑

根据您的评论,您需要使用GROUP BY查询.如果添加的是日期字段(没有时间信息),则可以使用此查询：

SELECT added,COUNT(*)
FROM users
WHERE added >= CURRENT_DATE() - INTERVAL 7 DAY
GROUP BY added

编辑

这应该是你正在寻找的：

SELECT d.added,COUNT(*)
FROM
  (SELECT DISTINCT added
   FROM users
   WHERE added BETWEEN "2015-07-30" - INTERVAL 30 DAY AND "2015-07-30") AS d
  INNER JOIN users
  ON users.added <= d.added
GROUP BY
  d.added
ORDER BY `d`.`added` DESC

SQLFiddle here

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!