mysql拼接日期语句

<select id="selectNowDayBySum" resultType="com.paperpass.backstage.util.PageData">
SELECT a.click_date AS clickDate,IFNULL(b.SUM,0) AS `sum`, IFNULL(b.attachment_type,0) AS attachmentType
FROM (
<include refid="time_HOUR_TODAY"></include>
) a LEFT JOIN (
SELECT DATE_FORMAT(create_time,'%Y-%m-%d/%H') AS DATETIME, IFNULL(CAST(SUM(sum_price)AS DECIMAL(10,2)),0) AS SUM,COUNT(1) AS COUNT,attachment_type
FROM `order_copyright`
WHERE prdid = #{prdid} and state=1
GROUP BY DATETIME
) b ON a.click_date = b.datetime
ORDER BY click_date desc
</select>

 

 

SELECT a.click_date AS clickDate,IFNULL(b.SUM,0) AS `sum`, IFNULL(b.attachment_type,0) AS attachmentType
FROM (
<include refid="time_hour"></include>
) a left JOIN (
SELECT DATE_FORMAT(create_time,'%Y-%m-%d/%H') AS DATETIME, IFNULL(CAST(SUM(sum_price)AS DECIMAL(10,2)),0) AS SUM,attachment_type ,COUNT(1) AS COUNT
FROM `order_copyright`
WHERE prdid = #{prdid} and state=1
GROUP BY DATETIME
) b ON a.click_date = b.datetime
ORDER BY click_date desc
</select>

<select id="selectNowDayBySum" resultType="com.paperpass.backstage.util.PageData">
SELECT a.click_date AS clickDate,IFNULL(b.SUM,0) AS `sum`, IFNULL(b.attachment_type,0) AS attachmentType
FROM (
<include refid="time_HOUR_TODAY"></include>
) a LEFT JOIN (
SELECT DATE_FORMAT(create_time,'%Y-%m-%d/%H') AS DATETIME, IFNULL(CAST(SUM(sum_price)AS DECIMAL(10,2)),0) AS SUM,COUNT(1) AS COUNT,attachment_type
FROM `order_copyright`
WHERE prdid = #{prdid} and state=1
GROUP BY DATETIME
) b ON a.click_date = b.datetime
ORDER BY click_date desc
</select>

 

SELECT a.click_date AS clickDate,IFNULL(b.SUM,0) AS `sum`, IFNULL(b.attachment_type,0) AS attachmentType
FROM (
<include refid="time_hour"></include>
) a left JOIN (
SELECT DATE_FORMAT(create_time,'%Y-%m-%d/%H') AS DATETIME, IFNULL(CAST(SUM(sum_price)AS DECIMAL(10,2)),0) AS SUM,attachment_type ,COUNT(1) AS COUNT
FROM `order_copyright`
WHERE prdid = #{prdid} and state=1
GROUP BY DATETIME
) b ON a.click_date = b.datetime
ORDER BY click_date desc
</select>