我正在一个项目上,该项目旨在提供一个系统,您可以在其中分析SQL表中的数据。我正在此查询(在VB.NET程序内部)以将所需的数据获取到GridView中:
SELECT [task], [activity], SUM([consumption]) AS TotalMin
FROM [TimeTable]
WHERE [name]=@name
AND [month]=@month
AND [year]=@year
GROUP BY [task], [activity]
ORDER BY [task] ASC
我得到的是:
task | activity | TotalMin
------------+----------+---------
new website | meeting | 60
new website | planing | 240
new website | testing | 340
new app | meeting | 60
new app | planing | 240
new app | testing | 340
是否有可能不对每个活动重复执行任务,因此只显示一次任务,然后仅显示与下面列出的该任务相关的每个活动?
所需的输出是:
task | activity | TotalMin
------------+----------+---------
new website | meeting | 60
| planing | 240
| testing | 340
new app | meeting | 60
| planing | 240
| testing | 340
您可以在查询中尝试以下操作:
SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY [task] ORDER BY [task]) = 1 THEN [task] ELSE '' END as [task],
[activity], SUM([consumption]) AS TotalMin
FROM [TimeTable]
WHERE [name]=@name
AND [month]=@month
AND [year]=@year
GROUP BY [task], [activity]
ORDER BY [task] ASC
该查询使用ROW_NUMBER()函数将相关任务分组在一起,并为每个任务分配一个连续的行号。并仅显示第1行的实际任务
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句