适配kingbase

Question

如何将以下mysql语法修改成kingbase8语法，希望给予解答

     SELECT
        date_format(t1.DAY,'%Y-%m-%d') date,
        IFNULL(t2.number, 0) AS number
        FROM
        (
        SELECT
        date_format(@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),'%Y-%m-%d') DAY
        FROM
        (
        SELECT
        @cdate := DATE_ADD('2019-07-07', INTERVAL + 1 DAY)
        FROM
        animal_list
        ) t0
        ) t1
        LEFT JOIN (
        SELECT
        sum(number) number,
        date_format(aet.detection_time,'%Y-%m-%d') DAY
        FROM
        animal_event_tag aet
        left join animal_event_index aei on aet.event_id=aei.id
        where 1=1
        and aet.detection_time between '2019-07-07 10:39:45' and '2023-07-07 10:39:45'
                 and aet.dept_id in('YBbyEr','4195bacc64614089ba98ff75d2482fbb')
                   GROUP BY
        DATE(aet.detection_time)
        ) t2 ON t1. DAY = t2. DAY
        where t1.DAY BETWEEN '2019-07-07' and '2023-07-07'
        ORDER BY
        t1.DAY asc            

Answer 1

可以看一下：
下边是将MySQL语法修改为Kingbase8语法的查询：

SELECT
    to_char(t1.DAY, 'YYYY-MM-DD') AS date,
    COALESCE(t2.number, 0) AS number
FROM
    (
    SELECT
        to_char(@cdate := DATEADD('DAY', -1, @cdate), 'YYYY-MM-DD') AS DAY
    FROM
        (
        SELECT
            @cdate := DATEADD('DAY', 1, '2019-07-07') AS dummy
        FROM
            animal_list
        ) t0
    ) t1
LEFT JOIN
    (
    SELECT
        sum(number) AS number,
        to_char(aet.detection_time, 'YYYY-MM-DD') AS DAY
    FROM
        animal_event_tag aet
    LEFT JOIN
        animal_event_index aei ON aet.event_id = aei.id
    WHERE
        aet.detection_time BETWEEN '2019-07-07 10:39:45' AND '2023-07-07 10:39:45'
        AND aet.dept_id IN ('YBbyEr','4195bacc64614089ba98ff75d2482fbb')
    GROUP BY
        to_date(aet.detection_time, 'YYYY-MM-DD')
    ) t2 ON t1.DAY = t2.DAY
WHERE
    t1.DAY BETWEEN '2019-07-07' AND '2023-07-07'
ORDER BY
    t1.DAY ASC;