createtable tb_1(
id int,
xiaofei money,
userid int,
addtime datetime
)
insertinto tb_1 values(1, 880, 1, getdate())
insertinto tb_1 values(2, 950, 2, getdate())
insertinto tb_1 values(3, 740, 1, getdate())
insertinto tb_1 values(4, 254, 4, getdate())
insertinto tb_1 values(5, 2541, 5, getdate())
insertinto tb_1 values(6, 80, 3, getdate())
insertinto tb_1 values(7, 3870, 1, getdate())
insertinto tb_1 values(8, 100, 2, getdate())
insertinto tb_1 values(9, 180, 3, getdate())
insertinto tb_1 values(10, 3240, 6, getdate())
select*from tb_1
select userid, sum(xiaofei) as'消费总金额'from tb_1 groupby userid orderbysum(xiaofei) desc
select userid,sum(xiaofei) as'2000以下'from tb_1 groupby userid havingsum(xiaofei) <2000
select userid,sum(xiaofei) as'2000-4000'from tb_1 groupby userid havingsum(xiaofei) between2000and4000
select userid,sum(xiaofei) as'4000-6000'from tb_1 groupby userid havingsum(xiaofei) between4000and6000
droptable tb_1
/*
userid 消费总金额
----------- ---------------------
1 5490.00
6 3240.00
5 2541.00
2 1050.00
3 260.00
4 254.00
(6 行受影响)
userid 2000以下
----------- ---------------------
2 1050.00
3 260.00
4 254.00
(3 行受影响)
userid 2000-4000
----------- ---------------------
5 2541.00
6 3240.00
(2 行受影响)
userid 4000-6000
----------- ---------------------
1 5490.00
(1 行受影响)
*/
--查询连续数字,统计个数
INSERT INTO tb1
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 12
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 25
UNION ALL SELECT 30
go
select * from tb1
drop table tb1,#tb
-- sql2000
select num,rid = identity( int, 1, 1) into #tb from tb1
select min(num) minNum, max(num) maxNum, count( 1) cnt
from #tb
group by (num -rid)
having count( *) > 1
-- sql2005
select 开始 = min(num),结束 = max(num), count( 1) cnt from (
select row_number() over ( order by num) as rid, * from tb1
) a group by num -rid having( count( 1) > 1)
-- 结果：
-- 1 5 5
-- 12 12 1
-- 17 20 4
-- 25 25 1
--30 30 1
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638193.html，如需转载请自行联系原作者
