mysql开发中如何进行行列转换?使用序列化表的方法实现行转列
1、如何进行行列转换
需要进行行转列的场景
汇总显示
姓名 |
学科 |
成绩 |
张三 |
数学 |
78 |
张三 |
语文 |
85 |
张三 |
英语 |
90 |
李四 |
数学 |
90 |
李四 |
语文 |
75 |
李四 |
英语 |
80 |
姓名 |
数学 |
英语 |
语文 |
张三 |
78 |
85 |
90 |
李四 |
90 |
75 |
80 |
王五 |
75 |
69 |
95 |
赵六 |
89 |
70 |
79 |
如:
姓名 |
打怪数 |
猪八戒 |
10 |
猪八戒 |
2 |
猪八戒 |
12 |
沙增 |
3 |
沙增 |
5 |
沙增 |
1 |
孙悟空 |
20 |
孙悟空 |
10 |
孙悟空 |
17 |
孙悟空 |
猪八戒 |
沙增 |
47 |
24 |
9 |
此法存在缺陷,为什么存在缺陷呢
Select * from ( select sum(kills) as ‘沙增’ fromuser1 a join user_kills b on a_id = b.user_id and a.user_name= ‘沙增’)a cross join (
select sum(kills) as ‘猪八戒’ fromuser1 a join user_kills b on a.id = b.user1_id and a.user_name= ‘猪八戒’)
B. Cross join ( select sum(kills) as ‘孙悟空’ fromuser1 a join user_kills b on a.id = b.user_id and a.user_name= ‘孙悟空’)c
使用case语句,怎用的,好处又在哪里
如:
Select sum(case when user_name = ‘孙悟空’ thenkills end) as ‘孙悟空’,
Sum(case when user_name = ‘猪八戒’ thenkills end) as ‘猪八戒’,
Sum(case when user_name = ‘沙增’ thenkills end) as ‘’沙增
From user1 a join user_kills b on a.id = b.user_id;
2.使用序列化表的方法实现行转列
如:
Select user_name
,REPLACE(SUBSTRING(SUBSTRING_INDEX(moblie,’,’,a.id),CHAR_LENGTH(
SUBSTRING_INDEX(moblie,’,’,a.id-1)+1),’,’,”)AS moblie
FROM tb_sequence a CROSS JOIN(
SELECT user_name.COMCAT(moblie,’,’)AS moblie, LENGTH(moblie)
LENGTH(REPALCE(moblie,’,’,”))+1size)
FROM user1 b ) b ON a.id <= b.size
反转过来看:
Select user_name,’arms’ asequipment,arms from user1 a join user_equipment b on a.id =b.user_id,
Union all
Select user_name, ‘clothing’ asequipment,clothing from user1 a join user1_equipment b on a.id =b.user_id union all
Select user_name,’shoe’ asequipment,shoe from user1 a join user1_equipment b on a.id =b.user_id
使用union方式处理
如:
Select b.’user_name’,’arms’ ASequipment,arms
FROM user1_equipment a join user1 b ON a.’user_id’ =b.’id’
UNION
SELECT b.’user_name’,’clothing’,clothing
FROM user1_equipment a JOIN user1 b ONa.’user_id’ =b.’id’
UNION
SELECT b.user1_equipment a join user1 b ON a.’user_id’ =b.’id’
ORDER BY 1,2
如:
Select user_name,
Case when c.id = 1 then ‘arms’
When c.id = 2 then ‘clothing’
When c.id = 2 then ‘shoe’
End as equipment
Coalese(case when c.id = 1 then arms end
Case when c.id = 2 then clothing end
Case when c.id = 3.then shoe end ) as eq_name
From user1 a
Join user1_equipment b on a.id = b.user_id cross join tb_sequencec.where c.id<= 3 order by user_name;
2.如何生成唯一的序列号
需要使用唯一序列号的场景
数据库主键
业务序列号如发票号,车票好,订单号等
生成序列号的方法
序列号:MYSQL:AUTO_INCREMENT
SQLSERVER:IDENTITY/SEQUENCE
ORACLE:SEQUENCE
PGSQL:SEQENCE
如:用sql来生成特殊的序列号
生成订单序列号,并且订单号的格式如下:
YYYYMMDDNNNNNNN ,201505120000003
DECLARE v_cnt INT;
DECLARE v_timestr INT;
DECLARE rowcount BIGHT;
SET v_timestr= DATE_FROM(NOW(),’%Y%m%d);
SELECT ROUNT(RAND()*100,0)+1 INTO v_int;
START TRANSACTION;
UPDATE order_seq SET order_sn = order_sn+v_cnt WHERE timesrtr =v_timestr;
IF ROW_COUNT() = 0 THEN
INSERT INTOorder_seq(timestr,order_sn)VALLus(v_timestr,v_cnt);
END IF;
SELECT CONCAT(v_timestr,LPAD(order_sn,7,0)) AS order_sn
FROM order_seq WHERE timestr = v_timestr;
COMMIT;
3.如何处理重复的数据
产生数据重复的原因
人为原因,如重复录入数据,重复提交等
系统原因,由于系统升级或设计的原因使原来可以重复的数据变不能重复了
如何查询数据是否重复
利用group by 和having从句处理
如:
SELECT user_name,COUNT(*)
FROM user1——test
GROUP BY user_name HAVING COUNT(*) >1
删除重复数据,对于相同数据保留ID最大的
DELETE a
FROM user1_test a JOIN(
SELECT user_name,count(*),MAX(id) AS id)
FROM user1_test
GROUP BY user_name HAVING COUNT(*) >1)
b ON a,user_name = b.user_name
WHERE a.id < b.id
如何处理重复的数据
更复杂的情况
User_name |
over |
mobile |
张三 |
以 |
12222345678,32526645868,12222345678 |
李四 |
耳 |
12465357565,1434346586 |
王五 |
三 |
124645568453,142355523775,1224342311455,1224342311455 |
赵六 |
四 |
2342465868342,871436568899 |
上一篇: MySql和Oracle的区别
下一篇: 数据库sql给某列添加唯一约束的方法