可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I'm not sure what should I write in the following SQL query to show 'date' column like this: "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)
So, what I want to do is to change the data from the first column to show month and year instead of showing month only.
回答1:
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)
回答2:
I guess is MS SQL as it looks like MS SQL syntax.
So you should put in the group line the same thing as in select ex:
Select MONTH(date)+'-'+YEAR(date), ....
...
...
...
group by MONTH(date)+'-'+YEAR(date)
回答3:
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)
回答4:
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')
回答5:
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?
回答6:
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
回答7:
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/
回答8:
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!