sql server 中 left join 外加convert 转换时间
没有固定得相对应值,根据时间取出最接近得值
SELECT
pt.AddDate,
CONVERT (datetime,SUBSTRING ( CONVERT ( VARCHAR, DateNow, 23 ), 0, 11 ) + ' ' + SUBSTRING ( zjsjd, 0, 3 ) + ':' + SUBSTRING ( zjsjd, 4, 5 ) + ':00',
120 ) AS DateNow1,
d.zjsjd,
d.MaterialID,
wl.MaterialDes,
d.ValueNow,
pt.bs,
pt.FeO,
d.classNow,
m.classDes
FROM
DMDataNow AS d
LEFT JOIN MDclass AS m ON d.classNow = m.ClassID
LEFT JOIN MDMaterial AS wl ON d.MaterialID = wl.MaterialID
left JOIN PTMatattributeHis AS pt ON pt.LastEdit in
(
select top 1 LastEdit from
(
SELECT CONVERT
( VARCHAR, AddDate, 120 ) AS ptdate,LastEdit,FeO,
row_number () OVER ( partition BY CONVERT ( VARCHAR, AddDate, 120 ) ORDER BY LastEdit DESC ) AS addDate1
FROM
PTMatattributeHis
WHERE
CONVERT ( VARCHAR, AddDate, 120 ) <= ( CONVERT (datetime,SUBSTRING ( CONVERT ( VARCHAR, DateNow, 23 ), 0, 11 ) + ' ' + SUBSTRING ( zjsjd, 0, 3 ) + ':' + SUBSTRING ( zjsjd, 4, 5 ) + ':00',
120 ) )
and bs = '4'
) as t
where t.addDate1 =1 ORDER BY t.ptdate DESC
)
WHERE
itemtypeid = '0007'
AND itemid = 'zj02'
AND d.SeriesID= '1'
order by DateNow1 DESC
仅供自己参考