Mysql多列分组查询 示列详解
来源:互联网 发布:淘宝五分钟内拍下 编辑:程序博客网 时间:2024/05/16 17:58
一张表 按照时间,按照时间,id分组
源表:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
flowData.STORE_ID,
flowData.CREATE_HOUR AS createHour,
SUM(IN_COUNT) hourInCount,
SUM(OUT_COUNT) AS hourOutCount
FROM
T_IVA_PROJECT_PASSENGER_FLOW_DATA flowData
LEFT JOIN T_IVA_PROJECT_PASSENGER_FLOW_DIMENSION dimension ON flowData.STORE_ID = dimension.STORE_ID
AND flowData.IP_ADDRESS = dimension.IP_ADDRESS
WHERE
flowData.STORE_ID = '871l'AND dimension. STATUS = 1
GROUP BY
flowData.STORE_ID,
flowData.CREATE_DATE,
flowData.CREATE_HOUR
ORDER BY
hourInCount DESC
flowData.STORE_ID,
flowData.CREATE_HOUR AS createHour,
SUM(IN_COUNT) hourInCount,
SUM(OUT_COUNT) AS hourOutCount
FROM
T_IVA_PROJECT_PASSENGER_FLOW_DATA flowData
LEFT JOIN T_IVA_PROJECT_PASSENGER_FLOW_DIMENSION dimension ON flowData.STORE_ID = dimension.STORE_ID
AND flowData.IP_ADDRESS = dimension.IP_ADDRESS
WHERE
flowData.STORE_ID = '871l'AND dimension. STATUS = 1
GROUP BY
flowData.STORE_ID,
flowData.CREATE_DATE,
flowData.CREATE_HOUR
ORDER BY
hourInCount DESC
按照谁分组就是重复的放在一起,作为一列
然而当分组有多个层次的时候,按照范围显示 比如按照日期和时间分组
我们先把不同的日期分组,然后在按照不同的时间分组 然后相同合并,此图(可以看出按照日期和时间) 时间有相同的显示 因为他们的日期不同
假如仅仅按照时间的话 就不会有相同的时间了
NormalText Code
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
flowData.STORE_ID,
flowData.CREATE_HOUR AS createHour,
flowData.CREATE_DATE as date,
SUM(IN_COUNT) hourInCount,
SUM(OUT_COUNT) AS hourOutCount
FROM
T_IVA_PROJECT_PASSENGER_FLOW_DATA flowData
LEFT JOIN T_IVA_PROJECT_PASSENGER_FLOW_DIMENSION dimension ON flowData.STORE_ID = dimension.STORE_ID
AND flowData.IP_ADDRESS = dimension.IP_ADDRESS
WHERE
flowData.STORE_ID = '871l'AND dimension. STATUS = 1
GROUP BY
flowData.STORE_ID,
flowData.CREATE_DATE,
flowData.CREATE_HOUR
ORDER BY
hourInCount DESC
flowData.STORE_ID,
flowData.CREATE_HOUR AS createHour,
flowData.CREATE_DATE as date,
SUM(IN_COUNT) hourInCount,
SUM(OUT_COUNT) AS hourOutCount
FROM
T_IVA_PROJECT_PASSENGER_FLOW_DATA flowData
LEFT JOIN T_IVA_PROJECT_PASSENGER_FLOW_DIMENSION dimension ON flowData.STORE_ID = dimension.STORE_ID
AND flowData.IP_ADDRESS = dimension.IP_ADDRESS
WHERE
flowData.STORE_ID = '871l'AND dimension. STATUS = 1
GROUP BY
flowData.STORE_ID,
flowData.CREATE_DATE,
flowData.CREATE_HOUR
ORDER BY
hourInCount DESC
仅仅按照日期的话:
阅读全文
0 0
- Mysql多列分组查询 示列详解
- mysql 分组查询每组前几行的某列数据
- MySQL 多列分区详解
- group by 分组查询 返回多列问题
- Mysql多对多查询、列合并
- Mysql多列索引,查询优化
- Mysql使用多列索引查询
- MYSQL 子查询返回多列显示
- MySQL实现子查询过滤多列
- MySql按列分组然后求和
- MySQL 列转行 且分组进行统计
- MySQL对列排序,分组排序
- mysql 分组查询某一列最大的前10条记录
- MySQL 列转行统计查询
- Mysql查询表&列名
- extjs_06_grid(列锁定&列分组)
- linq 多列分组取某列最大值
- mysql查询表和列的注释、添加列
- RecyclerView的使用和与ListView的一些不同之处
- Swift基础语法2
- python练习(十三)
- mysql ZIP Archive与mysql MSI Installer区别 5 唐致红 | 浏览 13274 次 我有更好的答案 推荐于2016-03-22 03:47:12 最佳答案 mysql
- 从Android/Java基础之上学习C/C++语言(8)--C语言基础--预编译
- Mysql多列分组查询 示列详解
- 带权并查集--uva12232 Exclusive-OR
- KMP算法详细讲解
- hdu 1874畅通工程续 floyd
- 鸟叔Linux学习笔记:第二章 主机规划与磁盘分区
- Redis安装
- C++智能指针
- IELTS11 Test4 besides economy, other types of progress are equally important
- 《Java并发编程的艺术》笔记三——锁的升级与对比.md