Question

求帮把下面这段sqlServer的语句改成MySql格式的 语句

update attendance set type=t2.type
from attendance t1
inner join
(
select name,id,time=_time,type=case when time<='08:00' and idd=1 then '上班'
when time>'08:00' and time<='12:00' and idd=1 then '迟到'
when time<'12:00' and idd<>1 then '上班重复刷卡'
when time>='13:00' and time<='17:30' and idd=1 then '早退'
when time>'17:30' and idd=1 then '下班'
when time>'13:00' and idd<>1 then '下班重复刷卡'
when time>='12:00' and time<='13:00' then '乱刷卡' end
from
(
select name,id,_time=time,time=convert(varchar(5),time,8),type,
idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
from attendance where convert(varchar(5),time,8)<='12:00'
union all
select name,id,_time=time,time=convert(varchar(5),time,8),type,
idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
from attendance where convert(varchar(5),time,8)>='13:00'
union all
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=0
from attendance where convert(varchar(5),time,8)>='12:00'
and convert(varchar(5),time,8)<='13:00'
)t
) t2
on t1.id=t2.id and t1.time=t2.time

Answer 1

这句话 本就是SQLserver 语句 无需要修改，在SQL2008下能执行，你只需要添加上
declare @classifyID int
set @classifyID=1;--此处可以是你想要查询的任何ID；
with tabs as( select ClassifyID,ParentID from ProductClassify where ClassifyID=@classifyID
union all
select b.ClassifyID,b.ParentID from tabs a,ProductClassify b where a.ParentId=b.ClassifyID )

SELECT AttrbuteID,ProductID,AttrName,AttrValue,AttrType,AddTime,AttrbuteSort,ClassifyID FROM ProductAttrbute where ClassifyID IN(select ClassifyID from tabs) order by ClassifyID asc