疑难问题
各位大俠﹐請幫我看一看﹐下面這個查詢怎么寫才能提高效率
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区select work_id,sdate,
work_from1=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=1 and substring(state,2,1)=1),
work_to1=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=2 and substring(state,2,1)=0),
work_from2=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=3 and substring(state,2,1)=1),
work_to2=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=4 and substring(state,2,1)=0),
work_from3=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=5 and substring(state,2,1)=1),
work_to3=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=6 and substring(state,2,1)=0)
from #er a
group by work_id,sdate
#er表格式如下:
card_id sdate stime state
107073 2006-02-28 0800 60A
107073 2006-02-28 1950 11
107073 2006-02-28 2330 20
107073 2006-03-01 0020 31A
107073 2006-03-01 0800 60A
106376 2006-02-20 0753 11
106376 2006-02-20 1130 20
高手幫幫我﹐首先謝謝大家了
work_from1=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=1 and substring(state,2,1)=1),
work_to1=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=2 and substring(state,2,1)=0),
work_from2=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=3 and substring(state,2,1)=1),
work_to2=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=4 and substring(state,2,1)=0),
work_from3=(select min(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=5 and substring(state,2,1)=1),
work_to3=(select max(stime) from #er where a.work_id=work_id and case when right(state,1)='A' then convert(varchar(10),DATEADD(day,1,a.sdate),120) else a.sdate end=sdate and left(state,1)=6 and substring(state,2,1)=0)
from #er a
group by work_id,sdate
#er表格式如下:
card_id sdate stime state
107073 2006-02-28 0800 60A
107073 2006-02-28 1950 11
107073 2006-02-28 2330 20
107073 2006-03-01 0020 31A
107073 2006-03-01 0800 60A
106376 2006-02-20 0753 11
106376 2006-02-20 1130 20
高手幫幫我﹐首先謝謝大家了
...全文
