sql - 以列的形式返回结果的动态 SQL 查询

Question

我有这个基本的数据库表并且是 SQL 初学者。

+-------------+--------+----------+
|  location   |  Week  | Expenses |
+-------------+--------+----------+
| Backoffice  | 201851 |       80 |
| frontoffice | 201852 |      110 |
| Backoffice  | 201901 |      120 |
| Backoffice  | 201902 |       70 |
| frontoffice | 201903 |       68 |
+-------------+--------+----------+

有没有办法动态检索最近 5 周的结果，而不是每次都在我的数据透视表中进行硬编码？

这是我的代码:

  SELECT *  from
  (SELECT[week]
        ,[Expenses]
from [cafe].[dbo].[table]where location = 'Backoffice'
) as Total_Expenses pivot (sum([expenses]) for [week] in ([201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909])) as pivotable;

我希望能够只输入“5”，它会显示过去 5 周的情况。可能是通过“最大”公式”

Answer 1

如果您想选择前 5 周(最近几周)，请使用 TOP:See here

SELECT TOP 5 *
FROM [cafe].[dbo].[table]
WHERE location = 'Backoffice'
ORDER BY Week Desc

将最后一行更改为 ORDER BY Week [ ASC | DESC ] 更改顺序。

---

如果你想获得前 5 周，并按周计算费用总和(我假设你想按周和位置分组)然后试试这个:

select top (5) Location, Week, sum(Expenses)
FROM table_name
WHERE location = 'backoffice'
GROUP BY Week, Location
Order By Week Desc

SQL Fiddle

附言我建议不要使用 [table] 作为您的表名。