sql group by 无法显示的字段的替代办法

他不是万能的,很可能就差一步完美收工,却因为语法设计理念,导致功亏于溃.

有这么一个表

有这么一个表,现在想取每个小时南屋北屋两者的最大值, 同时还要保留position
问一个group by 能否解决

 

现在 group by 出来了

 

 没法加select 后边没法带position,group by 加position 等于又回到了原来.

于是出了这个表也不知道position 是哪个,等于无用了..........

换一种思路,把行转列配合group ,尽管显示的不太符合要求,但在程序中做个判断也基本能满足要求了

select day, hour, 
sum(CASE Position WHEN N'南屋' THEN datavalue ELSE 0 END) AS '南屋' ,
sum(CASE Position WHEN N'北屋' THEN datavalue ELSE 0 END) AS '北屋' 
,max (datavalue) maxvalue
from test group by day, hour order by day , hour

 在换一种思路

select * from
(select *,row_number() over(partition by year,month,day,hour order by datavalue desc) as Sequence from test )T where T.Sequence=1

 

 

 

 

 

很好的弥补了group by 的不足, row_number() over(partition by..) 可以看做是显示版的groupby 了

因此，二者的区别在于，如果要select出不在group by语句中的列，则应该使用row_number() over(partition by)，如果需要select的列全在group by中，则使用group by语句。

当然现实中 也存在一个问题, 南屋北屋两个值相同时,默认排序中谁在前边,就留下谁的值,这样也不能满足实际需求, 但在此满足了题目要求,如果真到现实中,求max 还得在程序里去把值相同的给过滤开来.

 

扩展: 配合group by 的使用,把group by 分组下的其他字符串的列给合并起来

 string_agg，array_agg 这两个函数的功能大同小异，只不过合并数据的类型不同。

create table jinbo.employee(empno smallint, ename varchar(20), job varchar(20), mgr smallint, hiredate date, sal bigint, comm bigint, deptno smallint);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALEMAN', 7698, '2014-11-12', 16000, 300, 30);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7566, 'JONES', 'MANAGER', 7839, '2015-12-12', 32000, 0, 20);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALEMAN', 7698, '2016-09-12', 12000, 1400, 30);

select * from jinbo.employee;
 empno | ename  |   job   | mgr  |  hiredate  |  sal  | comm | deptno 
-------+--------+---------+------+------------+-------+------+--------
  7499 | ALLEN  | SALEMAN | 7698 | 2014-11-12 | 16000 |  300 |     30
  7566 | JONES  | MANAGER | 7839 | 2015-12-12 | 32000 |    0 |     20
  7654 | MARTIN | SALEMAN | 7698 | 2016-09-12 | 12000 | 1400 |     30
(3 rows)

1.查询同一个部门下的员工且合并起来

方法1：
select deptno, string_agg(ename, ',') from jinbo.employee group by deptno;

 deptno |  string_agg  
--------+--------------
     20 | JONES
     30 | ALLEN,MARTIN

方法2：
select deptno, array_to_string(array_agg(ename),',') from jinbo.employee group by deptno;
 deptno | array_to_string 
--------+-----------------
     20 | JONES
     30 | ALLEN,MARTIN

select deptno, string_agg(ename, ',' order by ename desc) from jinbo.employee group by deptno;
 deptno |  string_agg  
--------+--------------
     20 | JONES
     30 | MARTIN,ALLEN

concat 字符连接 

非group by 

一、concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,...)

返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

3、举例：

例1:select concat (id, name, score) as info from tt2;

 

group_concat()函数  MySQL 中的 group_concat

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

3、举例：