mysql分组排名，行列转换、存储过程、分组拼接等

突然发现好久没有输出了，由于前段时间换了新工作，这段时间也没有接触到新的内容所以遇到的问题比较少，最近这段时间呢，主要是用mysql+excel做数据分析，接下来呢就将这段时间遇到的问题做个记录与君共勉

1.mysql排名的实现，由于mysql没有开窗函数，不能像hive那样用row_number很方便的实现

分组排名待结算金额前20站址
SELECT 单位1,站址编码,已核查待结算金额,rank
 FROM
(
 SELECT t3.*,IF(@p=单位1,@r:=@r+1,@r:=1) AS rank,
 @p:=单位1
 FROM table t3 ,(SELECT @p:=NULL,@r:=0)r
 ORDER BY 单位1,已核查待结算金额 desc
 )s
where rank <21

以上代码实现了按单位1分组，按金额逆序排序，各单位前20的站址

@p后面的 单位1 是用来做分组的，使用的时候按照需要替换=号后面的单位1即可，其他地方按照上面就可以。

2.需求：一个合同号有多个凭证，一个凭证对应一个状态，当和同对应的凭证都为正常时该合同号为正常，有一个异常时为异常，其余为待确认

select t1.ht,a
,case when a like '%异常%' then '异常'
when a ='正常' then '正常' else '待确认' end as b
from (
        select ht,group_concat(distinct a) as a from tb
        group by ht
)t1

图片左边为结果演示，右边为按照自己的思路实现的代码虽然有点绕但是还是可以解决问题的，后来发现有group_concat函数就改进了代码 

3.前两天跟同事聊天，说他遇到了一个很申请的bug,分开统计和放一起统计差两个人

COUNT(distinct case when 是否外包人员='0' then 处理人账号 else '' end )-1 本部总人数, 
COUNT(distinct case when 是否外包人员='1' then 处理人账号 else '' end )-1 外包总人数,
COUNT(distinct case when 是否外包人员='1' then 处理人账号 else '' end )-1 + 
COUNT(distinct case when 是否外包人员='0' then 处理人账号 else '' end )-1 总人数 

 为什么后面有 -1 呢，按照他的理解，不在范围内的他给的是空 ' ' distinct后会是一条

实际上呢，在这里的空是不计数的，所以为了避免灵异事件还是建议各位，用其他值代替

4.mysql的存储过程

很多客户端上都有创建存储过程的核按钮，所以不用担心，只用把sql 丢里面做一下变量替换啥的，判断之类的就可以了

CREATE DEFINER=`root`@`localhost` PROCEDURE `sf_wkl`.`befor`(in BBBS varchar(16))
leave_cal:
BEGIN
DECLARE cnt INT DEFAULT 0;
declare errorString,returnString VARCHAR(64) default null;
select count(*) into cnt
	from gdpro.hwzzht
	where ly=BBBS and dcms not in (select dcms from gdfi.hwdcms );
if cnt>0  then
  set errorString='代持模式不在范围';  
  select errorString;
  leave leave_cal;
end if;
END

如图，跟oracle的很像，如果有那种定期需要刷的数据，还是很方便的，目前就用到了简单的这些

5.mysql 字符串分割，列转行

select a.TEMPLATEID,a.TEMPLATETYPE,a.VALID,substring_index(substring_index(a.CONCODE3CLASS,',',b.help_topic_id),';',-1) 
from 
sf_wkl.tmp2022_0112 a
join
mysql.help_topic b ### help_topic_id 共有504个
on b.help_topic_id <= (length(a.CONCODE3CLASS) - length(replace(a.CONCODE3CLASS,';',''))+1)
order by a.TEMPLATEID,a.TEMPLATETYPE,a.VALID;

参考博客 

MYSQL 逗号分割行转列 方法 - jack_zc - 博客园