大佬教程收集整理的这篇文章主要介绍了在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
您还可以使用它来计算所有记录之前<>查询:
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
以上是大佬教程为你收集整理的在MySQL中对多个数据运行相同SELECT的最简单方法全部内容,希望文章能够帮你解决在MySQL中对多个数据运行相同SELECT的最简单方法所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。