常见mysql sql 技巧一
1，数字辅助表//创建表
create table test(id int unsigned not null primary key);
delimiter //
create procedure pnum(cnt int unsigned)
begin
declare i int unsigned default 1;
insert into numselect i;
while i*2 < cnt do
insert into num select i+id from num ;
set i=i*2;
end while;
end
//
delimiter ;
#####列值不连续问题：
表a中id值为1，2,3,100,101,110，111
set @q=0;
select> #####对不连续的进行分组
set @a=0;
select min(id) as start_v,max(id) as end_v from (
select>
select> #####对不连续的值填充
use test;
DROP TABLE if EXISTS pincer;
create table pincer(a int UNSIGNED);
insert into pincer values(1),(2),(5),(100),(101),(103),(104),(105);
select a+1 as start ,(select min(a)-1 from pincer as ww where ww.a>qq.a) as end from pincer as qq where
not exists (select * from pincer as pp where qq.a+1=pp.a)
and a ################
2,生日问题
selectname,birthday,if(cur>today,cur,next) as birth_day
from(
select name,birthday,today,date_add(cur,interval if(day(birthday)=29 && day(cur)=28,1,0) day)as cur, date_ad(next,interval if(day(birthday)=29 && day(next)=28,1,0) day) as next
from(
select name,birthday,today,
date_add(birthday,interval diff year) ascur,
date_add(birthday,interval diff+1 year) as next,
from(
select concat(laster_name,'',first_name) as name,
birth_date as birthday,
(year(now())-year(birth_date) )as diff,
now() as today
from employees) as a
) as b
) as c
3,日期问题----计算工作日
create table sals(id int ,date datetime ,cost int,primary key(id);
select date_add('1900-01-01',
interval floor(datediff(date,'1900-01-01')/7)*7 day)
as week_start,
date_add('1900-01-01',
interval floor(datediff(date,'1900-01-01')/7*7+6 day)
as week_end,
sum(cost) from sales;
计算工作日（指定2个日期段 有多少工作日）
create procedure pgetworkdays (s datetime,e datetime)
begin
select floor(days/7)*5+days%7
case when 6 between wd and wd+days%7-1 then 1 else 0 end
case then 7 between wd and wd+days%7-1 then 1 else 0 end
from
(select datediff(e,s)+1 as days,weekday(s)+1 as wd) as a;
end;
页:
[1]