7,714
社区成员
发帖
与我相关
我的任务
分享
SELECT d.BH, d.NAME, d.MyValue,
(SELECT IIF(COUNT(*)=5, Round(AVG(p.MyValue),1), 0)
FROM AA as p
WHERE d.BH = p.BH
AND p.MyDate IN
(SELECT TOP 5 pp.MyDate
FROM AA AS pp
WHERE d.MyDate >= pp.MyDate
AND d.BH = pp.BH
ORDER BY pp.MyDate DESC )) as five_days_average,
(SELECT IIF(COUNT(*)=10, Round(AVG(p.MyValue),1), 0)
FROM AA as p
WHERE d.BH = p.BH
AND p.MyDate IN
(SELECT TOP 10 pp.MyDate
FROM AA AS pp
WHERE d.MyDate >= pp.MyDate
AND d.BH = pp.BH
ORDER BY pp.MyDate DESC )) as ten_days_average,
d.MyDate
FROM AA as d
ORDER BY d.BH, d.MyDate;
select a.id,iif(b.MyValue is null,0,(a.MyValue+b.MyValue)/2) As MyMa,a.Date
from table1 a left join table1 b on a.id=b.id and a.Date=b.Date+1
where a.id='001'