sql_cmd.md

常用的sql

作用	命令	备注
分组排序后取第一个	code	8.0兼容，部分老版本的5.7不可用
多行合成一行用逗号分割	SELECT GROUP_CONCAT(id SEPARATOR ',') FROM redis_cluster where env = 'test' and deleted_at is null and status = 4;
分组排序5.7版本	code	5.7版本
按照倒数第二个-分隔符切割	code

code1

select *
from (select row_number()
                 over (partition by colum_partiion order by colum_order desc) as rownum, *
      from (SELECT *
            FROM talbe) as G1) as G
where G.rownum = 1

code2

select pod_ip
from (select pod_ip,
             pod_name,
             @row_number := IF (@subject = tmp1.cluster_name, @row_number+1, 1) as row_number,
    @subject := cluster_name as '分组'
      from (
          select (select trim (Trailing concat('-', SUBSTRING_INDEX(pod_name, '-', -2)) from
          pod_name)) as cluster_name, pod_ip, pod_name
          from aegis_pods_topology
          where
          namespace = 'default'
          and pod_name like 'k8redis-%') as tmp1, (select @row_number := 0) b) as c
WHERE row_number = 1

code3

select trim(TRAILING (select substring_index("hello-test-1", '-', -2)) from "hello-test-1") as name;