mysql – GROUP BY两个不同的列
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – GROUP BY两个不同的列，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5849字，纯文字阅读大概需要9分钟。
内容图文
我存储命中.我的表看起来像这样：
ID | time | Country
--------------------------------
1 | 01:00:00 | France
2 | 01:00:00 | France
3 | 01:00:00 | Brazil
4 | 01:00:00 | USA
5 | 02:00:00 | USA
这是我的查询：
SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
它确实计算了我得到的点击次数并按小时分组：
time | total_hits
---------------------------
01:00:00 | 4
02:00:00 | 1
我想要一个按时间列分组的结果,另一个按国家/地区列分组.
这就是我需要的：
1)像这样按小时分组(同上)
time | total_hits
---------------------------
01:00:00 | 4
02:00:00 | 1
2)AND按这样的国家分组
country | total_hits
---------------------------
France | 2
USA | 2
Brazil | 1
我可以：
SELECT
COUNT(*)
, HOUR(time)
, COUNT(IF( Country = 'France', Country, null)) AS France
, COUNT(IF( Country = 'USA', Country, null)) AS USA
, COUNT(IF( Country = 'Brazil', Country, null)) AS Brazil
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
或者使用CASE或SUM(Country =’France’)AS France.
但在国家专栏中,不仅仅有3个国家.如果我对每个国家都这样做,我的查询会很长.
我能做到这一点：
SELECT COUNT(*), Country, HOUR(time)
FROM hits
WHERE time >= CURDATE()
GROUP BY Country, HOUR(time)
但输出将是这样的：
time | country | total_hits
---------------------------------------
01:00:00 | France | 2
01:00:00 | USA | 1
01:00:00 | Brazil | 1
02:00:00 | USA | 1
但是我需要一个如上所述的输出.
基本上我在一个查询中需要这些查询：
1)按小时分组(如上所述)
SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
2)按国家分组
SELECT COUNT(*) as total_hits
FROM hits
WHERE time >= CURDATE()
GROUP BY Country
表现很重要.该数据库有数百万条目.
也许MySQL不是解决这个问题的最佳方式？
解决方法:
我不认为我可以帮助您格式化,但您需要的查询是这样的：
建议查询
SELECT
IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
COUNT(1) TotalHits
FROM
(
SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;
你的样本数据
USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
id int not null auto_increment,
time datetime,
country varchar(20),
PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');
您的示例数据已加载
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE hits
-> (
-> id int not null auto_increment,
-> time datetime,
-> country varchar(20),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO hits (time,Country) VALUES
-> ('2014-06-19 01:00:00','France'),
-> ('2014-06-19 01:00:00','Brazil'),
-> ('2014-06-19 01:00:00','USA'),
-> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time | country |
+----+---------------------+---------+
| 1 | 2014-06-19 01:00:00 | France |
| 2 | 2014-06-19 01:00:00 | Brazil |
| 3 | 2014-06-19 01:00:00 | USA |
| 4 | 2014-06-19 02:00:00 | USA |
+----+---------------------+---------+
4 rows in set (0.00 sec)
mysql>
建议的查询执行
mysql> SELECT
-> IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
-> COUNT(1) TotalHits
-> FROM
-> (
-> SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
-> FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
-> ) AA
-> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00 | 1 |
| Total for Brazil | 1 |
| 2014-06-19 01:00:00 | 1 |
| Total for France | 1 |
| 2014-06-19 01:00:00 | 1 |
| 2014-06-19 02:00:00 | 1 |
| Total for USA | 2 |
| Total for All Countries | 4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql>
我会把格式留给你:-)
CAVEAT：请确保您在表格上有时间索引
ALTER TABLE hits ADD INDEX time_index (time);
另外一个查询可能是
SELECT
IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
COUNT(1) TotalHits
FROM
(
SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;
谁的输出
+-------------------------------+-----------+
| Statistic | TotalHits |
+-------------------------------+-----------+
| Brazil | 1 |
| France | 1 |
| USA | 1 |
| Total for 2014-06-19 01:00:00 | 3 |
| USA | 1 |
| Total for 2014-06-19 02:00:00 | 1 |
| Total for 2014-06-19 | 4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)
更新2014-06-21 19:07美国东部时间
也许两个查询结合UNION将适合您
SELECT
IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
COUNT(1) TotalHits
FROM
(
SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
COUNT(1) TotalHits
FROM
(
SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;
我从第一个中移除了WITH ROLLUP,以便总计出现一次
内容总结
以上是互联网集市为您收集整理的mysql – GROUP BY两个不同的列全部内容，希望文章能够帮你解决mysql – GROUP BY两个不同的列所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】