按月份和年份进行 SQL 分组

我不知道应该在下面的 SQL 查询中写什么来显示“ date”列: “ month-year”-“9-2011”。

SELECT MONTH(date) + '.' + YEAR(date) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY MONTH(date), YEAR(date)

因此，我想要做的是将第一列中的数据更改为显示月份和年份，而不是仅显示月份。

316644 次浏览
小开

I'm guessing this is MS SQL, as it looks like MS SQL syntax.

You should put the same thing in the group by as you have in the select.

For example:

Select MONTH(date)+'-'+YEAR(date), ....
...
...
...
group by MONTH(date)+'-'+YEAR(date)
小开
最佳答案 
SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY
CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2)
小开

If I understand correctly. In order to group your results as requested, your Group By clause needs to have the same expression as your select statement.

GROUP BY MONTH(date) + '.' + YEAR(date)

To display the date as "month-date" format change the '.' to '-' The full syntax would be something like this.

SELECT MONTH(date) + '-' + YEAR(date) AS Mjesec, SUM(marketingExpense) AS
SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY MONTH(date) + '.' + YEAR(date)
小开

In postgresql I can write a similar query with a date-format function (to_char) and grouping just by date: 

SELECT to_char (datum, 'MM-YYYY') AS mjesec
FROM test
GROUP BY datum
ORDER BY datum;

Such thing is surely possible with SQL-Server too, isn't it?

小开

If you want to stay having the field in datetime datatype, try using this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order] o
WHERE (idCustomer = 1) AND (o.[date] BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0)

It it also easy to change to group by hours, days, weeks, years...
I hope it is of use to someone,

Regards!

小开

SQL Server 2012 above, I prefer use format() function, more simplify.

SELECT format(date,'MM.yyyy') AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY format(date,'MM.yyyy')
小开

You can try multiplication to adjust the year and month so they will be one number. This, from my tests, runs much faster than format(date,'yyyy.MM'). I prefer having the year before month for sorting purpose. Code created from MS SQL Server Express Version 12.0.

SELECT (YEAR(Date) * 100) + MONTH(Date) AS yyyyMM
FROM [Order]
...
GROUP BY (YEAR(Date) * 100) + MONTH(Date)
ORDER BY yyyyMM
小开

For mariaDB you can:

SELECT DATE_FORMAT(date, '%m-%Y')
FROM [Order]
GROUP BY
DATE_FORMAT(date, '%m-%Y')

Link: https://mariadb.com/kb/en/library/date_format/

小开 
Yet another alternative:


Select FORMAT(date,'MM.yy')
...
...
group by FORMAT(date,'MM.yy')
小开

Years later, it still was a problem for me. Didn't find a solution that worked well for me for pre 2012 SQL Server in this forum, so I propose this workaround, which also works with the ORDER BY clause:

SELECT
CONVERT(DATE, CAST(DAY(0) AS NVARCHAR) + '/' +
CAST(MONTH(GETDATE()) AS NVARCHAR) + '/' +
CAST(YEAR(GETDATE()) AS NVARCHAR), 105)

Which yields:

2022-08-01

Obviously, replace the GETDATE() with the relevant field. Hope it's some use to someone.


提交答案