34,590
社区成员
发帖
与我相关
我的任务
分享
select *
from FZR_DH_TechPlanTrackPlan as d
where NOT EXISTS (SELECT *
FROM FZR_DH_TechPlanTrackPlan AS t
WHERE t.ID <> d.ID -- 先用这两个条件限定t记录不是d记录自己
AND t.PlanID = d.PlanID
AND ( -- 下面只要有一个满足条件,说明d记录不是最大的
(t.TrackProgress > d.TrackProgress)
OR (t.TrackProgress = d.TrackProgress AND
t.TrackDate > d.TrackDate)
OR (t.TrackProgress = d.TrackProgress AND
t.TrackDate = d.TrackDate AND
t.CDate > d.CDate)
OR (t.TrackProgress = d.TrackProgress AND
t.TrackDate = d.TrackDate AND
t.CDate = d.CDate AND
t.CTime > d.CTime)
OR (t.TrackProgress = d.TrackProgress AND
t.TrackDate = d.TrackDate AND
t.CDate = d.CDate AND
t.CTime > d.CTime AND
t.ID > d.ID)
)
)
--第二个问题,要先显示转为字符串再连接。否则会优先按INT相加。
rtrim(a.PlanID)+rtrim(a.TrackProgress)+rtrim(a.TrackDate)+rtrim(a.CreateDate)+rtrim(a.CreateTime)
select * from FZR_DH_TechPlanTrackPlan t
where ID=(select top 1 ID from FZR_DH_TechPlanTrackPlan where PlanID=t.PlanID
order by TrackProgress desc,TrackDate desc,CDate desc,CTime desc,ID desc)
select * from (
-- 选择计划跟踪数量为1的所有信息
select * from FZR_DH_TechPlanTrackPlan as d
where PlanID in ( select PlanID from (select PlanID, count(PlanID) rn from FZR_DH_TechPlanTrackPlan group by planid) as b
where b.rn = 1 )
union
-- 选择计划跟踪数量不为1的最大信息
select a.* from FZR_DH_TechPlanTrackPlan as a
left join (select PlanID , max(PlanID+TrackProgress+TrackDate+CreateDate+CreateTime) as rMax from FZR_DH_TechPlanTrackPlan
group by PlanID) as c on c.PlanID = a.PlanID
where a.PlanID in ( select PlanID from (select PlanID, count(PlanID) rn from FZR_DH_TechPlanTrackPlan group by planid) as b
where b.rn <> 1)
and a.PlanID+a.TrackProgress+a.TrackDate+a.CreateDate+a.CreateTime = c.rMax
) as d
order by d.PlanID
比较粗鲁的方法 。
还能 有更好的写法不?
另外,全是数字,如何相加的时候当字符串处理?
a.PlanID+a.TrackProgress+a.TrackDate+a.CreateDate+a.CreateTime
本意应该是12202015122320151223141212
现在加出来变成了40443284
-- 大概这个样子
with m as (
select * ,
row_number() over(partition by PlanID order by TrackProgress desc ,TrackDate desc , CDate desc ,CTime desc ,id desc) rn
from test
)
select * from m where rn = 1