WITH TEC AS 和 DATEDIFF函数 和 limit 1

WITH TEC AS (
SELECT
	a.XXID,a.BBXM,DATE_FORMAT( a.BBSR, '%Y-%m-%d' ) BBSR,a.JZBM,a.SFZ,
	b.JZID,b.LBID,DATE_FORMAT( b.JZRQ, '%Y-%m-%d' ) JZRQ,b.TXZT,( SELECT datediff( b.JZRQ, now( ) ) ) CHA ,
	(select MS_HY_YMJZ.WSSMC from MS_HY_YMJZ MS_HY_YMJZ where a.WSS=MS_HY_YMJZ.JZID) WSSMC
FROM
	MS_HY_BBXX a
	LEFT JOIN MS_TY_BBYMJZ b ON a.XXID = b.XXID 
WHERE
	a.XXID=#{xxid} and b.JZRQ > now( )  
	) 
	SELECT
	c.*,d.YMMC,d.YMLX,d.NL,d.TIME,
	d.YMJL,d.FY 
FROM
	TEC c
	LEFT JOIN MS_HY_YMLB d ON c.LBID = d.LBID ORDER BY c.JZRQ limit 1

WITH AS：相当于创建了一个临时表
DATEDIFF ：获取两个日期的时间间隔天数
limit 1：限制查询的条数为一条