mysql cross join 实现行转列

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;

select user_name ,count（*） from  user1_test group_by user_name having count（*）>1

alter table user1 add column mobile varchar（100）

select sum(case when user_name='孙悟空' then kills end) as ‘孙悟空’，sum(case when user_name=‘猪八戒’ then kills end) as ‘猪八戒’，sum(case when user_name=‘沙僧’) as '沙僧' from user1 a join user_kills b on a。id=b.user_id

select a.'user_name'，sum（kills） from user1 a join user_kills b on a.id=b.userId groub by a.user_name

select a.'user_name'，kills from user1 a join user_kills b on a.id=b.userId

show tables 展示表

行转列的场景

需要使用唯一序列号的场景：

1. 作为数据库主键。

2. 业务序列号。

生成序列号的方法：

- MySQL：AUTO_INCREMENT

- SQLServer：IDENTITY/SEQUENCE

- Oracle：SEQUENCE

- PgSQL：SEQUENCE

如何选择生成序列号的方式：

【原则】：优先选择系统提供的序列号生成方式。

【优点】：

1. 控制并发；

2. 不重复，保证序列号的唯一性。

【缺点】：序列号不连续（数据空洞），例如 1、2、4。

【原因】：对已有的数据的删除，以及事务回滚等方式不会影响自增长的序号，例如已有数据 1、2、3，删除 3 号数据。之后再插入一条数据，此时数据表的数据为 1、2、4。

需要使用唯一序列号的场景：

1. 作为数据库主键。

2. 业务序列号：如发票号、车票号、订单号等。

生成序列号的方法：

- MySQL：AUTO_INCREMENT

- SQLServer：IDENTITY/SEQUENCE

- Oracle：SEQUENCE

- PgSQL：SEQUENCE

如何选择生成序列号的方式：

【原则】：优先选择系统提供的序列号生成方式。

【优点】：

1. 控制并发；

2. 不重复，保证序列号的唯一性。

【缺点】：

1. 序列号不连续（数据空洞），例如 1、2、4。

【原因】：对已有的数据的删除，以及事务回滚等方式不会影响自增长的序号，例如已有数据 1、2、3，删除 3 号数据。之后再插入一条数据，此时数据表的数据为 1、2、4。

需要使用唯一序列号的场景：

1. 作为数据库主键。

2. 业务序列号：如发票号、车票号、订单号等。

生成序列号的方法：

- MySQL：AUTO_INCREMENT

- SQLServer：IDENTITY/SEQUENCE

- Oracle：SEQUENCE

- PgSQL：SEQUENCE

如何选择生成序列号的方式：

【原则】：优先选择系统提供的序列号生成方式。

【优点】：

1. 控制并发；

2. 不重复，保证序列号的唯一性。

【缺点】：

1. 序列号不连续（数据空洞），例如 1、2、4。

【原因】：对已有的数据的删除，以及事务回滚等方式不会影响自增长的序号，例如已有数据 1、2、3，删除 3 号数据。之后再插入一条数据，此时数据表的数据为 1、2、4。

CREATE TABLE order_seq(

timestr INT UNSIGNED,

order_sn INT UNSIGNED,

);

DELIMITER //

CREATE PROCEDURE seq_no() 

BEGIN

DECLARE v_cnt INT;

DECLARE v_timestr INT;

DECLARE rowcount BIGINT;

SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');

SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;

START TRANSACTION;

UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;

IF ROW_COUNT() = 0 THEN

INSERT INTO order_seq(timestr,order_sn) VALUES(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;

END//

DELIMITER ;

CALL seq_no();·

@MySQL---删除复杂的重复数据

#准备好要用的表
create table user1_practice(
id int not null auto_increment primary key,
user_name varchar(3),
over varchar(5),
mobile varchar(100));
insert into user1_practice(user_name,over,mobile) 
values ('唐僧','旃檀功德佛','12112345678,14112345678,12112345678');
insert into user1_practice(user_name,over,mobile) 
values ('猪八戒','净坛使者','12144643321,14144643321');
insert into user1_practice(user_name,over,mobile) 
values ('孙悟空','斗战胜佛','12166666666,14166666666,18166666666,18166666666');
insert into user1_practice(user_name,over,mobile) 
values ('沙僧','金身罗汉','12198343214,14198343214');

#建一个序列表
create tb_sequence(id int not null auto_increment primary key);
insert into tb_sequence values(),(),(),(),(),(),(),(),();

#列转行后的表user1_trans1
create table user1_trans1 as 
select a.id,user_name,over,replace(substring(
substring_index(mobile,',',a.id),
char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile 
from tb_sequence a 
cross join(select user_name,over,concat(mobile,',') as mobile,
length(mobile)-length(replace(mobile,',',''))+1 as size 
from user1_practice b) b on a.id <= b.size;

#删除user1_trans1表中的重复记录
delete a from user1_trans1 a 
join (select user_name,over,mobile,count(*),max(id) as id 
from user1_trans1  
group by user_name,over,mobile having count(*) > 1  ) b
on a.user_name = b.user_name and a.over = b.over and a.mobile = b.mobile 
where a.id < b.id;

#用group_concat函数将mobile转化成以逗号分隔的字符串
create table user1_trans2 as 
select user_name,over,group_concat(mobile) as mobile 
from user1_trans1 group by user_name,over;

#对原表user1_practice进行关联更新
update user1_practice a 
inner join user1_trans2 b on a.user_name = b.user_name
set a.mobile = b.mobile;

@MySQL---利用主键处理重复数据

1.查询数据是否重复

SELECT user_name,COUNT(*)
FROM user1_test
GROUP BY user_name HAVING COUNT(*)>1;

2.删除重复数据，对于相同数据保留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;