mysql开发技巧2

1、如何进行行列转换

2、如何生成唯一序列号

3、如何删除重复数据

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