下面是我的右边left join 的语句:不知道哪里错了:请各位指点一下:
select * from a读数 XX
LEFT OUTER JOIN
(SELECT a.*
FROM a读数 a,
(SELECT NO, D= MAX(D)
FROM a读数
WHERE NUM IS NOT NULL AND D < D.抄表日期--(可能是这里的时间有问题)
GROUP BY NUM) b
WHERE a.NUM = b.NUM AND a.D = b.D) Z ON
XX.NUM = Z.NUM
select * from a读数 XX
LEFT OUTER JOIN
(SELECT a.*
FROM a读数 a,
(SELECT NO, D= MAX(D)
FROM a读数
WHERE NUM IS NOT NULL AND D < D.抄表日期--(可能是这里的时间有问题)
GROUP BY NUM) b
WHERE a.NUM = b.NUM AND a.D = b.D) Z ON
XX.NUM = Z.NUM
A1.NO,
A1.Num,
A1.D,
A2.Num AS L_Num,
A2.D AS L_D
FROM A读书表 AS A1 LEFT JOIN A读书表 AS A2
ON A1.ID = A2.ID + 3
AND A1.NO = A2.NO
GO
业务就是要把对象(NO)为标识的对象用它的NUM和它的上一次的读数连接起来作个对比,看看当前记录的NUM和上一次的NUM作比较啊!如果上一次没有数据就使用NULL
select A.*,B.* from A LEFT OUTER JOIN (select ID+3 AS ID1,NO,NUM,D FROM A) B
ON A.ID=B.ID1
select a.* ,b.num ,b.d from a读数 a left join a读数 b on a.no=b.no and datediff(m,b.d,a.d)=1
如果统计时间不确定
select a.* ,b.num ,b.d from a读数 a left join a读数 b on b.id=(select max(id) from a读数 c where c.id<a.id and c.no=a.no)
SELECT B.水表ID, B.抄表日期 AS 本月抄表日期, B.本月行度, S.抄表日期 AS 上月抄表日期,
S.本月行度 AS 上月行度
FROM dbo.水表读数 B INNER JOIN
dbo.水表读数 S ON B.水表ID = S.水表ID AND DATEDIFF(month, B.抄表日期, S.抄表日期)
= - 1 AND B.本月行度 IS NOT NULL AND S.本月行度 IS NOT NULL AND B.换表 = 0大家拿来参考一下!谢谢大家的支持