sql - 将多个查询合二为一
问题描述
我想显示以下内容:
month no. 1
month January
total income $xx
一站式“查询”
目前我正在使用下面的代码来显示我想要的输出。但是,我想知道是否可以在一个查询中对其进行编码以显示所需的输出?
当前使用的代码:
DECLARE @january int = 0
SELECT Month(Transaction_Date) AS Month_Number,
DATENAME(MONTH, DATEADD(MONTH, @january, 0)) AS 'Month',
CONCAT('$', SUM(Credit_Amount)) AS Total_Income
FROM Income
WHERE Transaction_Date
BETWEEN '2020-01-01'
AND '2020-01-31'
GROUP BY
MONTH(Transaction_Date)
DECLARE @february int = 1
SELECT Month(Transaction_Date) AS Month_Number,
DATENAME(MONTH, DATEADD(MONTH, @february, 0)) AS 'Month',
CONCAT('$', SUM(Credit_Amount)) AS Total_Income
FROM Income
WHERE Transaction_Date
BETWEEN '2020-02-01'
AND '2020-02-29'
GROUP BY
MONTH(Transaction_Date)
DECLARE @march int = 2
SELECT Month(Transaction_Date) AS Month_Number,
DATENAME(MONTH, DATEADD(MONTH, @march, 0)) AS 'Month',
CONCAT('$', SUM(Credit_Amount)) AS Total_Income
FROM Income
WHERE Transaction_Date
BETWEEN '2020-03-01'
AND '2020-03-31'
GROUP BY
MONTH(Transaction_Date)
DECLARE @april int = 3
SELECT Month(Transaction_Date) AS Month_Number,
DATENAME(MONTH, DATEADD(MONTH, @april, 0)) AS 'Month',
CONCAT('$', SUM(Credit_Amount)) AS Total_Income
FROM Income
WHERE Transaction_Date
BETWEEN '2020-04-01'
AND '2020-04-30'
GROUP BY
MONTH(Transaction_Date)
解决方案
您可以按EOMONTH
哪个月的最后一天分组
SELECT
Month(EOMONTH(Transaction_Date)) AS Month_Number,
DATENAME(MONTH, EOMONTH(Transaction_Date)) AS [Month],
CONCAT('$', SUM(Credit_Amount)) AS Total_Income
FROM Income
WHERE Transaction_Date >= '2020-01-01' AND Transaction_Date < '2020-05-01'
GROUP BY
EOMONTH(Transaction_Date);
推荐阅读
- ios - 使用组合网络 ios 刷新令牌
- ruby-on-rails - 如何从 Ruby On Rails 版本 6.0.3.2 更改为版本 5?
- excel - 重写 Sumproduct 公式。去除或忽略字母字符 - 插入 VBA
- azure-pipelines - Azure 管道 ArchiveFiles 存档创建失败
- xml - 如何修复“XSD 文件中的错误解析组件”?
- pine-script - 安全函数返回“不能使用可变变量作为参数”
- python-3.x - 在 Django 中构建表单时,使用一个“胖”模型来包含多个表单的所有字段是一种好习惯吗?
- go - 将结构组合传递给函数
- python - 如何为 matplotlib 安装草书字体:找不到字体系列 ['cursive']
- javascript - 当搜索字符串在 typescript/javascript 中包含通配符时搜索数组?