完善一下存储过程，添加表 #唯一序列号 获取当前年月日+3位流水号 #用orser_seq判断插入最大值 DROP PROCEDURE IF EXISTS usp_seqnum; CREATE PROCEDURE usp_seqnum() BEGIN #定义变量并获取相关值 DECLARE v_cnt INT; DECLARE v_timestr INT; SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d'); SELECT ROUND(RAND()*100,0)+1 INTO v_cnt; #新建表 DROP TABLE IF EXISTS im_orderseq; CREATE TABLE im_orderseq( timestr NVARCHAR(8) NOT NULL , ordersn INT(3) ); START TRANSACTION; #更新表的最值 UPDATE im_orderseq SET ordersn = ordersn + v_cnt WHERE timestr = v_timestr; IF ROW_COUNT() = 0 THEN #插入数据 INSERT INTO im_orderseq(timestr,ordersn) VALUES(v_timestr,v_cnt); END IF; SELECT CONCAT(v_timestr,LPAD(ordersn,7,0))AS ordersn FROM im_orderseq WHERE timestr = v_timestr; COMMIT; END; CALL usp_seqnum(); SELECT * FROM im_orderseq;

@MySQL---用SQL生成特殊的序列号

CREATE TABLE order_seq(
timestr INT UNSIGNED,
order_sn INT UNSIGNED,
);DELIMITER //CREATE PROCEDURE seq_no()BEGINDECLARE v_cnt INT UNSIGNED;DECLARE v_timestr INT UNSIGNED;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 THENINSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt);END IF;SELECT CONCAT(v_timestr,LPAD(order_sn,7,0))AS order_snFROM order_seq WHERE timestr = v_timestr;COMMIT;END//DELIMITER ;CALL seq_no();

create table arms( id mediumint primary key auto_increment, username varchar(64), arms varchar(64), clothing varchar(64), shoe varchar(64) ); insert into arms(username,arms,clothing,shoe) values('唐僧','九环锡杖','袈裟','僧鞋'),('孙悟空','金箍棒','黄金甲','步云鞋'); select username,'arms',arms from arms union all select username,'clothing',clothing from arms union all select username,'shoe',shoe from arms; 

列转行2 select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_id union all select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id; union all select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id; ========================================== 列转行3 select user_name, case when c.id=1 then 'arms' when c.id=2 then 'clothing' when c.id=3 then 'shoe' end as equipment, coalesce(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_sequence c where c.id<=3 order by user_name;

SELECT user_name , 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,CONCAT(mobile,','))+1 size FROM user1 b) b ON a.id <= b.size

老师讲的真好 学习了

@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;

select sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒',thwn kills end) as '猪八戒'） from user_name a join user_kills b on a.id = b.id

关于学生成绩的行转列代码参考： //表格定义及数据 create table if not exists score( id smallint unsigned not null primary key auto_increment, uname varchar(100) not null, subject varchar(100) not null, score smallint not null )engine=innodb default charset=utf8; insert score values (default, 'a', 'math', 10), (default, 'a', 'english', 20), (default, 'a', 'chinese', 30), (default, 'b', 'math', 50), (default, 'b', 'english', 60), (default, 'b', 'chinese', 70), (default, 'c', 'math', 110), (default, 'c', 'english', 120), (default, 'c', 'chinese', 130); //转换代码： select math.uname,math.math,english.english,chinese.chinese from (select id, uname, score as math from score where subject='math') as math inner join (select id, uname, score as english from score where subject='english') as english on math.uname=english.uname inner join (select id, uname, score as chinese from score where subject='chinese') as chinese on chinese.uname=english.uname;

存储过程，订单号： 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; 知识点： 1、在sql语句中添加变量。 declare @local_variable data_type 声明时需要指定变量的类型，可以使用SET、SELECT、SELECT...INTO对变量进行赋值，在sql语句中就可以使用@local_variable来调用变量。 2、RAND()返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值。 3、事务 4、ROW_COUNT()函数返回查询语句执行后，被影响的列数目 5、IF...THEN...END IF;

列转行3 select user_name, case when c.id=1 then 'arms' when c.id=2 then 'clothing' when c.id=3 then 'shoe' end as equipment, coalesce(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_sequence c where c.id<=3 order by user_name;

序列化方式进行行转列： 1.首先我们先建立一个序列表 2.思路： 首先我们通过cross join 将user1_equipment 和 序列表 进行笛卡尔积，变成最终有多少条记录 c.id 必须小于等于user1_equipment的列数 再通过case语句，分别取c.id=1,2,3...的对应的行的值，加上coalesce取不为空的数据 最后加上对应的列名， 示例如下： SELECT user_name, CASE WHEN c.id = 1 THEN 'arms' WHEN c.id = 2 THEN 'clothing' WHEN c.id = 3 THEN 'shoe' END AS equipment, COALESCE( CASE WHEN c.id = 1 THEN arms END CASE WHEN c.id = 2 THEN clothing END CASE WHEN c.id = 3 WHEN shoe END ) AS eq_name FROM user1 a JOIN user1_equipment b ON a.id=b.user_id CROSS JOIN tb_sequence c WHERE c.id <= 3 ORDER BY user_name;

select user_name ,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,concat(mobile,',') as mobile,length(mobile)-LENGTH(replace(mobile,',',''))+1 size from user1 b) b on a.id <=b.size

行转列技巧

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;