BEGIN
declare num0 int;
declare num1 int;
declare num2 int;
declare num3 int;
declare num4 int;
declare num5 int;
declare num6 int;
declare num7 int;
declare num8 int;
select count(*) into num0 from (select * from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a0;select count(*) into num1 from (select * from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a1;select count(*) into num2 from (select * from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a2;select count(*) into num3 from (select * from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a3;select count(*) into num4 from (select * from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a4;select count(*) into num5 from (select * from notice_base where type = "文化科';" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a5;select count(*) into num6 from (select * from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a6;select count(*) into num7 from (select * from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a7;select count(*) into num8 from (select * from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a8;
set num0 = 10 - num0;
set num1 = 10 - num1;
set num2 = 10 - num2;
set num3 = 10 - num3;
set num4 = 10 - num4;
set num5 = 10 - num5;
set num6 = 10 - num6;
set num7 = 10 - num7;
set num8 = 10 - num8;
(select uuid, type ,0 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,0 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num0)
UNION
(select uuid, type ,1 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,1 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num1)
UNION
(select uuid, type ,2 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,2 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "办公室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num2)
UNION
(select uuid, type ,3 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,3 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num3)
UNION
(select uuid, type ,4 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,4 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文物科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num4)
UNION
(select uuid, type ,5 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,5 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num5)
UNION
(select uuid, type ,6 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,6 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num6)
UNION
(select uuid, type ,7 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,7 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num7)
UNION
(select uuid, type ,8 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,8 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num8) ;
END
这是一个正确的存储过程,现在我想把这个存储过程添加一个科室,新添加代码如下
declare num9 int;
select count(*) into num9 from (select * from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a9;
set num9 = 10 - num9;
UNION
(select uuid, type ,9 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,9 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "研究室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num9)
由于粘贴不了那么多代码,只把新添加的放上去了。
但就是修改不了,提示我语法错误,请问是什么原因?
declare num0 int;
declare num1 int;
declare num2 int;
declare num3 int;
declare num4 int;
declare num5 int;
declare num6 int;
declare num7 int;
declare num8 int;
select count(*) into num0 from (select * from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a0;select count(*) into num1 from (select * from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a1;select count(*) into num2 from (select * from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a2;select count(*) into num3 from (select * from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a3;select count(*) into num4 from (select * from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a4;select count(*) into num5 from (select * from notice_base where type = "文化科';" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a5;select count(*) into num6 from (select * from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a6;select count(*) into num7 from (select * from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a7;select count(*) into num8 from (select * from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a8;
set num0 = 10 - num0;
set num1 = 10 - num1;
set num2 = 10 - num2;
set num3 = 10 - num3;
set num4 = 10 - num4;
set num5 = 10 - num5;
set num6 = 10 - num6;
set num7 = 10 - num7;
set num8 = 10 - num8;
(select uuid, type ,0 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,0 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "通知公告" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num0)
UNION
(select uuid, type ,1 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,1 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "行政执法队" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num1)
UNION
(select uuid, type ,2 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,2 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "办公室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num2)
UNION
(select uuid, type ,3 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,3 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化大事件" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num3)
UNION
(select uuid, type ,4 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,4 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文物科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文物科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num4)
UNION
(select uuid, type ,5 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,5 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num5)
UNION
(select uuid, type ,6 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,6 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "文化市场管理科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num6)
UNION
(select uuid, type ,7 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,7 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "组织人事科" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num7)
UNION
(select uuid, type ,8 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,8 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "非遗办公室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num8) ;
END
这是一个正确的存储过程,现在我想把这个存储过程添加一个科室,新添加代码如下
declare num9 int;
select count(*) into num9 from (select * from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) a9;
set num9 = 10 - num9;
UNION
(select uuid, type ,9 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3)
UNION
(select uuid, type ,9 AS typeCode, title ,level, modifyDate, 0 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "研究室" and isPublish = 1 and status = 0 and id not in (select t.id from (select id from notice_base where type = "研究室" and isPublish = 1 and status = 0 and isTop = 1 ORDER BY id DESC limit 0 , 3 ) as t) ORDER BY id DESC limit 0 , num9)
由于粘贴不了那么多代码,只把新添加的放上去了。
但就是修改不了,提示我语法错误,请问是什么原因?
UNION
(select uuid, t这段的问题,UNION的两个子查询需要返回同类型,同列数的结果集,
自己先看看UNION的用法。
set num9 = 10 - num9;
UNION
(select uuid, type ,9 AS typeCode, title ,level, modifyDate, 1 as isTop, DATEDIFF(now(),modifyDate) as diff from notice_base where type = "研究室" and isPublish = 1 and status把这个UNION删除再试。