如何显示每30Th的记录数。 [英] How Do I Display Records For Every 30Th Count.
本文介绍了如何显示每30Th的记录数。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
根据我在sql server 2008中的查询,我有一个类似的o / p.
天 Cnt 价值
0 2 10.40
1 4 77.95
2 0 15.00
3 7 74.00
4 1 16.21
5 0 6.03
6 1 6.00
7 2 69.29
8 1 96.00
9 2 90.58
10 3 15.60
我需要显示为(此示例仅供参考)
TotDays TotCnt TotValue
0-30 36 8552.19
31-60 39 8552.19
61-90 96 8552.19
是否可以在sql server 2008中显示如上所示?
请帮助。
- 在此先感谢:)
解决方案
尝试:
SELECT CONVERT ( NVARCHAR ,Min(Totdays))+ ' - ' +CONVERT ( NVARCHAR ,Max(TotDays)) AS TotDays,
SUM(TotCnt) AS TotCnt,
SUM(TotValue) AS TotValue FROM MyTable
GROUP BY TotDays / 31
另一种选择演员(1 +(30 *((TotDays - 1)/ 30))as as varchar)+'to'+ cast(30 +(30 *((TotDays - 1)/ 30))作为varchar)作为TotDays
,SUM(TotCnt)作为TotCnt,SUM(TotValue)作为TotValue
来自pn46
GROUP BY((TotDays - 1)/ 30)
我使用以下创建 表 pn46
(
TotDays int 身份( 1 , 1 ),
TotCnt int ,
TotValue float
)
go
插入 进入 pn46 值( 1 , 10 。 0 )
go 100
它正确返回
< pre lang =text> 1 to 30 30 300
31 to 60 30 300
61 to 90 30 300
91 to 120 10 100etc
选择ID,LastName,FirstName FROM Users WHERE ID%30 = 0 ORDER BY ID
而不是id你必须使用totdays,可能会有所帮助。
Hi,
I have an o/p like as per my query in sql server 2008.
Days Cnt Value 0 2 10.40 1 4 77.95 2 0 15.00 3 7 74.00 4 1 16.21 5 0 6.03 6 1 6.00 7 2 69.29 8 1 96.00 9 2 90.58 10 3 15.60
I need to display as (this example is just for a reference purpose)
TotDays TotCnt TotValue 0-30 36 8552.19 31-60 39 8552.19 61-90 96 8552.19
Is it possible to display like above shown in sql server 2008 ?
Please Help .
-- Thanks in Advance :)
解决方案
Try:
SELECT CONVERT(NVARCHAR, Min(Totdays)) + '-' + CONVERT(NVARCHAR, Max(TotDays)) AS TotDays, SUM(TotCnt) AS TotCnt, SUM(TotValue) AS TotValue FROM MyTable GROUP BY TotDays/31
An alternativeselect cast(1 + (30 * ((TotDays - 1)/ 30)) as varchar) + ' to ' + cast(30 + (30 * ((TotDays - 1)/ 30)) as varchar) as TotDays , SUM(TotCnt) as TotCnt, SUM(TotValue) as TotValue from pn46 GROUP BY ((TotDays - 1)/ 30)
Which I tested with the followingCreate table pn46 ( TotDays int identity(1,1), TotCnt int, TotValue float ) go insert into pn46 values(1,10.0) go 100
And it correctly returned
1 to 30 30 300 31 to 60 30 300 61 to 90 30 300 91 to 120 10 100etc
Select ID, LastName, FirstName FROM Users WHERE ID%30 = 0 ORDER BY ID
instead of id u have to use totdays ,may be it will helpful.
这篇关于如何显示每30Th的记录数。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文