insert into erp_ls_speed_1(pz,sj,sj1,speed,sl,d1,d2) (select mc+hd pz,sj,sj1,speed,datediff(mi,sj,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj>=@v_d11 and sj1<=@v_d22 and scx=@v_scx)
union select mc+hd pz,@v_d1 sj,sj1,speed,datediff(mi,@v_d1,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj<@v_d11 and sj1>@v_d11 and scx=@v_scx)
union select mc+hd pz,sj,@v_d2 sj1,speed,datediff(mi,sj,@v_d22) sl,@v_d11,@v_d22 from erp_yx_speed where sj>@v_d11 and sj<@v_d22 and scx=@v_scx and (sj1>@v_d22 or sj1 is null) )
请告诉我这段代码得到的什么样的结果集?
union select mc+hd pz,@v_d1 sj,sj1,speed,datediff(mi,@v_d1,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj<@v_d11 and sj1>@v_d11 and scx=@v_scx)
union select mc+hd pz,sj,@v_d2 sj1,speed,datediff(mi,sj,@v_d22) sl,@v_d11,@v_d22 from erp_yx_speed where sj>@v_d11 and sj<@v_d22 and scx=@v_scx and (sj1>@v_d22 or sj1 is null) )
请告诉我这段代码得到的什么样的结果集?
select * from
(
select mc+hd pz,sj,sj1,speed,datediff(mi,sj,sj1) sl,@v_d11,@v_d22 from erp_yx_speed
where (sj>=@v_d11 and sj1<=@v_d22 and scx=@v_scx)
union
select mc+hd pz,@v_d1 sj,sj1,speed,datediff(mi,@v_d1,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj<@v_d11 and sj1>@v_d11 and scx=@v_scx)
union
select mc+hd pz,sj,@v_d2 sj1,speed,datediff(mi,sj,@v_d22) sl,@v_d11,@v_d22 from erp_yx_speed where sj>@v_d11 and sj<@v_d22 and scx=@v_scx and (sj1>@v_d22 or sj1 is null)
) k
改成这样也许好明白些
上面的结构化解成
insert erp_ls_speed_1
select * from k这里的k表就是
select mc+hd pz,sj,sj1,speed,datediff(mi,sj,sj1) sl,@v_d11,@v_d22 from erp_yx_speed
where (sj>=@v_d11 and sj1<=@v_d22 and scx=@v_scx)
union
select mc+hd pz,@v_d1 sj,sj1,speed,datediff(mi,@v_d1,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj<@v_d11 and sj1>@v_d11 and scx=@v_scx)
union
select mc+hd pz,sj,@v_d2 sj1,speed,datediff(mi,sj,@v_d22) sl,@v_d11,@v_d22 from erp_yx_speed where sj>@v_d11 and sj<@v_d22 and scx=@v_scx and (sj1>@v_d22 or sj1 is null)三个结果集UNION得到的临时结果集
(select mc+hd pz,sj,sj1,speed,datediff(mi,sj,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj>=@v_d11 and sj1<=@v_d22 and scx=@v_scx) union select mc+hd pz,@v_d1 sj,sj1,speed,datediff(mi,@v_d1,sj1) sl,@v_d11,@v_d22 from erp_yx_speed where (sj<@v_d11 and sj1>@v_d11 and scx=@v_scx) union select mc+hd pz,sj,@v_d2 sj1,speed,datediff(mi,sj,@v_d22) sl,@v_d11,@v_d22 from erp_yx_speed where sj>@v_d11 and sj<@v_d22 and scx=@v_scx and (sj1>@v_d22 or sj1 is null) )看一下效果,如果还不明白,就一句一句运行,两句两句运行,再三句运行。语句的意思你应该明白,就是取表中符合条件的字段。UNION就是把三个SELECT语句输出的结果合并后排序生成一个输出。再加上INSERT就是把这个输出插入到目标表中。
drop table tb
create table tb(ID int, Num int)
insert into tb
select 1,1 union all
select 1,2 union all
select 2,1 select * from tb
/*
ID Num
----------- -----------
1 1
1 2
2 1(3 行受影响)
*/
这个看起来没那么费劲