1 20070903 2944
2 20070903 132
3 20070903 1463
4 20070903 10461
5 20070903 4306
6 20070904 5130
7 20070904 801
8 20070904 665
9 20070904 4844
10 20070904 4589
11 20070904 3314
12 20070905 1871
13 20070905 1453
14 20070905 55
15 20070905 50
16 20070906 110
17 20070906 5121
18 20070906 1351
19 20070907 20359
20 20070907 10462
21 20070908 0
22 20070908 5545 有这么一张表:我想求每天,数值最大的两位之和
2 20070903 132
3 20070903 1463
4 20070903 10461
5 20070903 4306
6 20070904 5130
7 20070904 801
8 20070904 665
9 20070904 4844
10 20070904 4589
11 20070904 3314
12 20070905 1871
13 20070905 1453
14 20070905 55
15 20070905 50
16 20070906 110
17 20070906 5121
18 20070906 1351
19 20070907 20359
20 20070907 10462
21 20070908 0
22 20070908 5545 有这么一张表:我想求每天,数值最大的两位之和
解决方案 »
- 指定内容添加
- 有字段,A,B,C,D,想要查询表中字段A,B值重复的行,怎么写哦???
- 存储过程返回的数据怎么再被SQL使用?谢谢
- 高分求个SQL查询或过程、函数
- MSSQL 出现“多行数据更新”问题,怎么解决?
- 关于票号的处理,通过库存信息和已使用的信息,算出还可以使用的票号信息
- 为什么我的Recordset中表的项目必须顺序读取?
- winxp 下可以install sql server 2k 么?
- 不对:sp_executesql N'use @db',N'@db nvarchar(25)',@db='northwind'
- 奇怪:net8 easy config怎么用不起来了?
- SQL高级模糊查询语句 高手请帮忙
- 数据匹配问题
--这样?
--> 测试数据: @s
declare @s table (id int,date datetime,num int)
insert into @s
select 1,'20070903',2944 union all
select 2,'20070903',132 union all
select 3,'20070903',1463 union all
select 4,'20070903',10461 union all
select 5,'20070903',4306 union all
select 6,'20070904',5130 union all
select 7,'20070904',801 union all
select 8,'20070904',665 union all
select 9,'20070904',4844 union all
select 10,'20070904',4589 union all
select 11,'20070904',3314 union all
select 12,'20070905',1871 union all
select 13,'20070905',1453 union all
select 14,'20070905',55 union all
select 15,'20070905',50 union all
select 16,'20070906',110 union all
select 17,'20070906',5121 union all
select 18,'20070906',1351 union all
select 19,'20070907',20359 union all
select 20,'20070907',10462 union all
select 21,'20070908',0 union all
select 22,'20070908',5545select date,sum(num) from @s a where (select count(num) from @s where date=a.date and num>a.num)<2
group by date
Set Nocount On
declare @1 table([id] int,[date] Datetime,[x] int)
Insert @1
select 1,'20070903',N'2944' union all
select 2,'20070903',N'132' union all
select 3,'20070903',N'1463' union all
select 4,'20070903',N'10461' union all
select 5,'20070903',N'4306' union all
select 6,'20070904',N'5130' union all
select 7,'20070904',N'801' union all
select 8,'20070904',N'665' union all
select 9,'20070904',N'4844' union all
select 10,'20070904',N'4589' union all
select 11,'20070904',N'3314' union all
select 12,'20070905',N'1871' union all
select 13,'20070905',N'1453' union all
select 14,'20070905',N'55' union all
select 15,'20070905',N'50' union all
select 16,'20070906',N'110' union all
select 17,'20070906',N'5121' union all
select 18,'20070906',N'1351' union all
select 19,'20070907',N'20359' union all
select 20,'20070907',N'10462' union all
select 21,'20070908',N'0' union all
select 22,'20070908',N'5545'
Select date,Sum(x) As x
From @1 a
Where id in(Select Top 2 id From @1 Where date=a.date Order By x Desc)
Group By date/*
date x
----------------------- -----------
2007-09-03 00:00:00.000 14767
2007-09-04 00:00:00.000 9974
2007-09-05 00:00:00.000 3324
2007-09-06 00:00:00.000 6472
2007-09-07 00:00:00.000 30821
2007-09-08 00:00:00.000 5545
*/
Set Nocount On
declare @1 table([id] int,[date] Datetime,[x] int)
Insert @1
select 1,'20070903',N'2944' union all
select 2,'20070903',N'132' union all
select 3,'20070903',N'1463' union all
select 4,'20070903',N'10461' union all
select 5,'20070903',N'4306' union all
select 6,'20070904',N'5130' union all
select 7,'20070904',N'801' union all
select 8,'20070904',N'665' union all
select 9,'20070904',N'4844' union all
select 10,'20070904',N'4589' union all
select 11,'20070904',N'3314' union all
select 12,'20070905',N'1871' union all
select 13,'20070905',N'1453' union all
select 14,'20070905',N'55' union all
select 15,'20070905',N'50' union all
select 16,'20070906',N'110' union all
select 17,'20070906',N'5121' union all
select 18,'20070906',N'1351' union all
select 19,'20070907',N'20359' union all
select 20,'20070907',N'10462' union all
select 21,'20070908',N'0' union all
select 22,'20070908',N'5545'Select date,Sum(x) x
From(select * from(select *,row_number() over (partition by date order by x desc) as xj
FROM @1) a where xj in (1,2)) b
Group By date