解决方案 »
- 控制台插入excle数据可以 删除不行 求解 第三次找人结贴了
- 如何处理SQL注入和script注入?
- WCF技术,会的请进
- C# MSChart 获取任意点坐标
- 关于xml文件保存的问题
- 强烈关注:关于.net程序的发布问题
- 当前最活跃、问题解决实时性最高的技术组织-----c#圣地.net(顺便散分)
- 请问怎样在win2000中用C#实现的关机程序啊?
- 向有经验的高手请教WEB下倒计时问题
- 我在用Visual C#时给窗体添加了一个按钮,又给这个按钮添加了一个背景图片,当我想去掉背景图片时发现那是不可能的。
- c#里如何在datagirdview里直接添加新的数据并在数据库中更新,求求大神
- WPF,ClickOne部署的应用程序能被反编译吗?
(
select 1,'2014-01-01',10 union
select 1,'2014-01-02',12 union
select 1,'2014-01-03',14 union
select 1,'2014-01-04',11 union
select 1,'2014-01-05',19 union
select 2,'2014-01-01',10 union
select 2,'2014-01-02',11 union
select 2,'2014-01-03',11 union
select 2,'2014-01-04',11 union
select 2,'2014-01-05',10 union
select 2,'2014-01-06',10 union
select 3,'2014-01-01',13 union
select 3,'2014-01-02',14 union
select 3,'2014-01-03',17 union
select 3,'2014-01-04',10 union
select 3,'2014-01-05',11
)
select * from (select ID,TM,VALUE,row_number() over(partition by ID order by VALUE desc) as rownumber from tb where TM between '2014-01-05' and '2014-01-06') as T where T.rownumber = 1;
(
select 1,'2014-01-01',10 union
select 1,'2014-01-02',12 union
select 1,'2014-01-03',14 union
select 1,'2014-01-04',11 union
select 1,'2014-01-05',19 union
select 2,'2014-01-01',10 union
select 2,'2014-01-02',11 union
select 2,'2014-01-03',11 union
select 2,'2014-01-04',11 union
select 2,'2014-01-05',10 union
select 2,'2014-01-06',10 union
select 3,'2014-01-01',13 union
select 3,'2014-01-02',14 union
select 3,'2014-01-03',17 union
select 3,'2014-01-04',10 union
select 3,'2014-01-05',11
)
select * from (select ID,TM,VALUE,row_number() over(partition by ID order by VALUE desc) as rownumber from tb where TM between '2014-01-05' and '2014-01-06') as T where T.rownumber = 1 and ID in (1,2);
ID TM VALUE rownumber
1 2014-01-05 19 1
2 2014-01-05 10 1
CTE要sql 2005以上版本才支持的
学习一下 sql 语句教程,了解“分组”的概念。
(1)
本来用
select ID, TM,VALUE
FROM A b where b.VALUE=(select MAX(VALUE) FROM A where ID=b.ID and datatype=‘X’) order by ID
但是发现最大的VALUE值也有重复的,取最大值最早出现的时间,所以用以下语句
select ID,min(TM) TM,VALUE
FROM A b where b.VALUE=(select MAX(VALUE) FROM A where ID=b.ID and datatype=‘X’) group by ID,VALUE order by ID
(2)用一个很长的语句
select ID,TM,VALUE from (SELECT TOP 1 ID,TM,VALUE FROM A where ID=1 and datatype=‘X’ order by DI desc) b
union
select ID,TM,VALUE from (SELECT TOP 1 ID,TM,VALUE FROM A where ID=2 and datatype=‘X’ order by DI desc) b
union
...............
select ID,TM,VALUE from (SELECT TOP 1 ID,TM,VALUE FROM A where ID=77 and datatype=‘X’ order by DI desc) b
union
select ID,TM,VALUE from (SELECT TOP 1 ID,TM,VALUE FROM A where ID=78 and datatype=‘X’ order by DI desc) b
(3)
SELECT TOP 1 ID,TM,VALUE FROM A where ID=1 and datatype=‘X’ order by DI desc
SELECT TOP 1 ID,TM,VALUE FROM A where ID=2 and datatype=‘X’ order by DI desc
...............
SELECT TOP 1 ID,TM,VALUE FROM A where ID=77 and datatype=‘X’ order by DI desc
SELECT TOP 1 ID,TM,VALUE FROM A where ID=78 and datatype=‘X’ order by DI desc
测试结果(耗时):
(1)4125ms
(2)2373ms
(3)4867ms