SELECT T3.FRAMENO AS FRAMENO ,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9'
END AS MaintainGrade,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@'
END AS MaintainMsg
FROM
(select distinct Max(t1.WalkDistance) over(partition by t1.FrameNo) as MaxWalkDistance,Min(t1.WalkDistance) over(partition by t1.FrameNo) as MinWalkDistance
,Count(t1.WalkDistance) over(partition by t1.FrameNo) as WalkDistanceCount
,t1.FrameNo from rt_repair t1
left join RT_RepairType t2 on t1.RepairNo = T2.RepairNo where T2.RepairTypeCode = '2' order by t1.FrameNo) AS T3
改进版本
SELECT T3.FRAMENO AS FRAMENO ,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9'
END AS MaintainGrade,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
END AS MaintainMsg
FROM
(select distinct Max(t1.WalkDistance) over(partition by t1.FrameNo) as MaxWalkDistance,Min(t1.WalkDistance) over(partition by t1.FrameNo) as MinWalkDistance
,Count(t1.WalkDistance) over(partition by t1.FrameNo) as WalkDistanceCount
,t1.FrameNo from rt_repair t1
left join RT_RepairType t2 on t1.RepairNo = T2.RepairNo where T2.RepairTypeCode = '2' AND DATE(T1.INPUTTIME) between '2010-1-1' and '2012-01-01' ) AS T3
数据统计
SELECT DISTINCT T2.ChangeType,COUNT(T2.CHANGETYPE) OVER(partition by T2.CHANGETYPE) FROM(
SELECT DISTINCT T1.* FROM (SELECT D2.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2
LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 ON D2.FRAMENO=D1.FRAMENO
UNION ALL
SELECT D1.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1
LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 ON D2.FRAMENO=D1.FRAMENO) AS T1) AS T2
数据统计,增加增减标志
SELECT DISTINCT T2.ChangeType,COUNT(T2.CHANGETYPE) OVER(partition by T2.CHANGETYPE), T2.ADDFLAG FROM(
SELECT DISTINCT T1.* FROM (SELECT D2.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType,
CASE WHEN VALUE(D1.MAINTAINGRADE,'L0') > VALUE(D2.MAINTAINGRADE,'L0') THEN '0' WHEN VALUE(D1.MAINTAINGRADE,'L0') < VALUE(D2.MAINTAINGRADE,'L0') THEN '1'END AS ADDFLAG
FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2
LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 ON D2.FRAMENO=D1.FRAMENO
UNION ALL
SELECT D1.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType,
CASE WHEN VALUE(D1.MAINTAINGRADE,'L0') > VALUE(D2.MAINTAINGRADE,'L0') THEN '0' WHEN VALUE(D1.MAINTAINGRADE,'L0') < VALUE(D2.MAINTAINGRADE,'L0') THEN '1'END AS ADDFLAG
FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1
LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 ON D2.FRAMENO=D1.FRAMENO) AS T1) AS T2
数据统计,INSERT INTO
INSERT INTO RT_MaintainSumDT (ID,MONTHS,FRAMENO,MaintainGrade,MaintainMsg )
SELECT ID,MONTHS,FRAMENO,VALUE(MaintainGrade,'L1'),VALUE(MaintainMsg,'L1@@') FROM
(SELECT 1 AS ID, 24 AS MONTHS ,T3.FRAMENO AS FRAMENO ,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9'END AS MaintainGrade,
CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'
WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'END AS MaintainMsg
FROM (SELECT DISTINCT Max(T1.WalkDistance) over(partition by T1.FrameNo) as MaxWalkDistance,Min(T1.WalkDistance)
over(partition by T1.FrameNo) as MinWalkDistance,Count(T1.WalkDistance) over(partition by T1.FrameNo) as WalkDistanceCount,T1.FrameNo
FROM RT_REPAIR T1 LEFT JOIN RT_RepairType T2 on T1.RepairNo = T2.RepairNo WHERE T2.RepairTypeCode = '2' AND DATE(T1.INPUTTIME) BETWEEN '2010-09-18' AND '2012-09-18' ) AS T3 ) AS T4