后台报这个错
org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column ‘dateStr’ from result set. Cause: java.sql.SQLException: Invalid length (10) for type TIMESTAMP
; Invalid length (10) for type TIMESTAMP; nested exception is java.sql.SQLException: Invalid length (10) for type TIMESTAMP
SELECT
a.date AS dateStr,
SUM( c.RS ) AS userNum,
IFNULL( ROUND( SUM( c.RS * c.HSFBZ )/ SUM( c.RS ), 2 ), 0.0 ) AS stdMoneyAvg
FROM
(
SELECT
DATE_ADD( ‘2021-02-01’, INTERVAL ( key - 1 ) DAY ) AS date
FROM
temp_date_nums
WHERE key <= ( DATEDIFF( ‘2021-02-28’, ‘2021-02-01’ )+ 1 )) a
LEFT JOIN (
SELECT
RYBZGL_TYWYSBM AS pid,
KS_RQ AS startDate,
JS_RQ AS endDate
FROM
hs_rybzgl
WHERE
SCBS = ‘0’
AND SFXG_PDBS = ‘1’
AND YWZT = ‘2’
AND JG_TYWYSBM = ‘8adee28450d6bd170150dfa89ffb1321’
) b ON ((
a.date >= b.startDate
AND a.date <= b.endDate ) OR ( b.endDate IS NULL AND a.date >= b.startDate
))
LEFT JOIN ( SELECT RYBZGL_TYWYSBM, RS, HSFBZ FROM hs_rybzglmx WHERE SCBS = ‘0’ ) c ON c.RYBZGL_TYWYSBM = b.pid
GROUP BY
a.date
ORDER BY
a.date;
<select id="listUserNumStdMoneyAvg" resultType="java.util.Map">
SELECT a.`date`, COALESCE(SUM(c.RS),0) AS userNum, COALESCE(ROUND(SUM(c.RS*c.HSFBZ)/SUM(c.RS),2),0.0) AS stdMoneyAvg FROM
(SELECT DATE_ADD(#{startDate},INTERVAL(`key`-1) DAY) AS `date` FROM temp_date_nums
WHERE `key` <![CDATA[ <= ]]> (DATEDIFF(#{endDate},#{startDate})+1)) a
LEFT JOIN
(SELECT RYBZGL_TYWYSBM AS pid,KS_RQ AS startDate,JS_RQ AS endDate FROM hs_rybzgl
WHERE SCBS = '0' AND SFXG_PDBS = '1' AND YWZT = '2' AND JG_TYWYSBM = #{orgId}
) b ON ((a.`date` <![CDATA[ >= ]]> b.startDate AND a.`date` <![CDATA[ <= ]]> b.endDate) OR (b.endDate IS NULL AND a.`date` <![CDATA[ >= ]]> b.startDate))
LEFT JOIN
(SELECT RYBZGL_TYWYSBM,RS,HSFBZ FROM hs_rybzglmx WHERE SCBS = '0') c ON c.RYBZGL_TYWYSBM = b.pid
GROUP BY a.`date` ORDER BY a.`date`;
</select>