mysql 存储过程

最新推荐文章于 2024-07-25 16:06:56 发布

超级无敌暴龙战士塔塔开

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量28

点赞数

分类专栏： sqlserver或者mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_45800365/article/details/133819231

版权

sqlserver或者mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

CREATE DEFINER=`root`@`localhost` PROCEDURE `studentsushe`()
BEGIN
declare username varchar(30);
declare name varchar(30);
DECLARE dormid int;
declare roomid int;
declare bedwei VARCHAR(50);
declare status VARCHAR(30);
DECLARE ruzhutime datetime;
	#Routine body goes here...

select stu.username as username,stu.name as name,adr.dormbuild_id as dormid,adr.dormroom_id as roomid,age,gender,phone_num as phoneNum,email
(case when first_bed = stu.username then '1号床' WHEN second_bed = stu.username THEN '2号床' WHEN 
third_bed = stu.username THEN '3号床' WHEN 
fourth_bed = stu.username THEN '4号床' END) as 'bedwei',stu.status as status,stu.ruzhutime as ruzhutime from student stu inner join dorm_room adr 
where stu.username = adr.first_bed or stu.username = adr.second_bed or stu.username = adr.third_bed or stu.username = adr.fourth_bed;



select @username = stu.username,name = stu.name, dormid = adr.dormbuild_id,roomid = adr.dormroom_id,
bedwei = (case when first_bed = stu.username then '1号床' WHEN second_bed = stu.username THEN '2号床' WHEN 
third_bed = stu.username THEN '3号床' WHEN 
fourth_bed = stu.username THEN '4号床' END),status = stu.status,ruzhutime = stu.ruzhutime from student stu inner join dorm_room adr 
where stu.username = adr.first_bed or stu.username = adr.second_bed or stu.username = adr.third_bed or stu.username = adr.fourth_bed;

create TEMPORARY TABLE stusuhe (username varchar(30),name varchar(30),dormid int,roomid int,bedwei varchar(50),status VARCHAR(30),ruzhutime datetime);


insert into stusuhe(username,name,dormid,roomid,bedwei,status,ruzhutime) values(@username,@name,@dormid,@roomid,@bedwei,@status,@ruzhutime);

select * from stusushe;
END