select * from table_a where
field1 = ‘a’
and field2 = ‘b’
group by field3;
报错: this is incompatible with sql_mode=only_full_group_byuery
支持的方式有两种:
关闭 TIDB 的 SQL 模式 ONLY_FULL_GROUP_BY
sql 改造 ,在 group by 前 嵌套一层 select
select * from (
select * from table_a where
field1 = ‘a’
and field2 = ‘b’
)tmp
group by field3;
发现这种 方式可行的原因:
项目内对于group by 的应用场景一般是用于排序后 取 group by 下的分类的最新一条数据。
类似:
select * from (
select * from table_a where
field1 = ‘a’
and field2 = ‘b’
order by create_time desc
)tmp
group by field3;
使用非 full group by 语法会导致结果集不稳定。
对于取分组内最新一条数据的需求,可以使用窗口函数,可参考下面语句(查询各部门最高工资):
SELECT * FROM
(SELECT depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) as row_index )
WHERE(row_index = 1) FROM empsalary
附带下使用 窗口函数的改写方式:
SELECT * FROM (
select t.id as id ,t.manager_name as managerName,ttt.merchant_id as merchantId
,ROW_NUMBER() OVER w AS ‘row_number’
from a t
left join b tt on concat(’,’,tt.manager_ids,’,’) like concat(’%,’,t.id,’,%’)
left join c ttt on tt.id=ttt.id
where 1=1
and ttt.field_2=‘01’
and ttt.field_1= 1
and (ttt.field_3<= DATE_FORMAT(now(),’%Y%m%d%H%i%s’) or ttt.FUND_PUBLISH_DATE is null)
WINDOW w AS (PARTITION BY t.id )
) tmp WHERE tmp.row_number = 1
ORDER BY tmp.id asc