mysql - 创建 View 的SQL语句

Question

我的目标是创建一个包含三列的 View ，分别是id、收入(计算得出)和year。我可以获得 id 和 revenue。但是，我的日期列采用 varchar 格式。

我不知道该怎么做。下面是我的SQL语句:

SELECT DISTINCT
    id,
    SUM(fborders.quantity * fbmenu.fbMenuUnitPrice) as revenue
FROM
    fb.fborders
        INNER JOIN
    fb.fbmenu ON fborders.fbMenuId = fbmenu.fbMenuId
WHERE
    date = (SELECT 
            YEAR(STR_TO_DATE(date, '%d/%m/%Y'))
        from
            fb.fborders
        WHERE
            YEAR(STR_TO_DATE(date, '%d/%m/%Y')) = '2011'
                OR YEAR(STR_TO_DATE(date, '%d/%m/%Y')) = '2012'
                OR YEAR(STR_TO_DATE(date, '%d/%m/%Y')) = '2013')
GROUP BY id

我的输出:

id | revenue | year _______________________R1 | 100     | 2011R1 | 999     | 2012R1 | 832     | 2013R2 | 323     | 2011R2 | 318     | 2012R2 | 111     | 2013R3 | 432     | 2011R3 | 342     | 2012R3 | 873     | 2013

Answer 1

如果我理解正确的话，你真正想做的是按年份分组:

SELECT DISTINCT
    id,
    SUM(fborders.quantity * fbmenu.fbMenuUnitPrice) as revenue,
    YEAR(STR_TO_DATE(date, '%d/%m/%Y')) as year
FROM
    fb.fborders
        INNER JOIN
    fb.fbmenu ON fborders.fbMenuId = fbmenu.fbMenuId
GROUP BY
    id,
    YEAR(STR_TO_DATE(date, '%d/%m/%Y'))