S4 S6 时间
0.00 0.00 2012-03-01 07:32:46
0.00 106.03 2012-04-03 17:56:09
0.00 106.66 2012-03-27 17:20:22
43.50 106.78 2012-04-06 16:34:34
30.10 107.33 2012-04-11 16:26:11
4.10 108.63 2012-04-04 10:56:23
17.00 111.78 2012-04-04 16:59:07
10.20 111.78 2012-04-04 17:00:48
0.50 112.17 2012-03-03 10:29:20
45.00 112.68 2012-04-02 12:26:23
32.70 116.13 2012-04-04 18:45:50
1.00 118.33 2012-03-24 12:03:39
需要查询S6最小时间的的数据,去掉多余的数据
0.00 0.00 2012-03-01 07:32:46
0.00 106.03 2012-04-03 17:56:09
0.00 106.66 2012-03-27 17:20:22
43.50 106.78 2012-04-06 16:34:34
30.10 107.33 2012-04-11 16:26:11
4.10 108.63 2012-04-04 10:56:23
17.00 111.78 2012-04-04 16:59:07
10.20 111.78 2012-04-04 17:00:48
0.50 112.17 2012-03-03 10:29:20
45.00 112.68 2012-04-02 12:26:23
32.70 116.13 2012-04-04 18:45:50
1.00 118.33 2012-03-24 12:03:39
需要查询S6最小时间的的数据,去掉多余的数据
解决方案 »
- update 后字段就变成科学计数法了求如何解决
- 有来麻烦大家拉SQL中有没有用第一条数据的U_ID和第二条的ID比较的方法啊
- 请问 SQLServer中的Image类型,有着什么用处
- 两个sql 服务器上的数据互相传输
- 如何让数据库向应用程序发送消息
- 如何实现排序且进行分类汇总 sql
- 流水号问题
- [100]超市前台在与后台数据库断开连接后,自动存放在本地,重新连接后,再上传。如何设计??
- 因改选择那个数据库?!
- GROUP BY后面 时间(yyyy-mm-dd hh-mm-ss) 怎么处理成 短日期:yyyy-mm-dd
- 新手学SQLServer 求指教
- SQL中 查询与指定时间最相近的记录
(
s4 decimal(18,2),
s6 decimal(18,2),
riqi datetime
)
insert into t2
select 0.00, 0.00, '2012-03-01 07:32:46' union all
select 0.00, 106.03, '2012-04-03 17:56:09' union all
select 0.00, 106.66, '2012-03-27 17:20:22' union all
select 43.50, 106.78, '2012-04-06 16:34:34' union all
select 30.10, 107.33, '2012-04-11 16:26:11' union all
select 4.10, 108.63, '2012-04-04 10:56:23' union all
select 17.00, 111.78, '2012-04-04 16:59:07' union all
select 10.20, 111.78, '2012-04-04 17:00:48' union all
select 0.50, 112.17, '2012-03-03 10:29:20' union all
select 45.00, 112.68, '2012-04-02 12:26:23' union all
select 32.70, 116.13, '2012-04-04 18:45:50' union all
select 1.00, 118.33, '2012-03-24 12:03:39'
select * from t2 order by s6select * from t2 as a where not exists (select 1 from t2 where t2.s6=a.s6 and t2.riqi<a.riqi)------------------------------
s4 s6 riqi
0.00 0.00 2012-03-01 07:32:46.000
0.00 106.03 2012-04-03 17:56:09.000
0.00 106.66 2012-03-27 17:20:22.000
43.50 106.78 2012-04-06 16:34:34.000
30.10 107.33 2012-04-11 16:26:11.000
4.10 108.63 2012-04-04 10:56:23.000
17.00 111.78 2012-04-04 16:59:07.000
0.50 112.17 2012-03-03 10:29:20.000
45.00 112.68 2012-04-02 12:26:23.000
32.70 116.13 2012-04-04 18:45:50.000
1.00 118.33 2012-03-24 12:03:39.000
是这个意思?
PDT ----> DATETIMESELECT max( wk1.col1 ) AS '第一列',
max( wk1.col2 ) AS '第二列',
max( wk1.col3 ) AS '第三列',
max( wk1.col4 ) AS '第四列',
max( wk1.col5 ) AS '第五列',
max( wk1.col6 ) AS '第六列',
max( wk1.col7 ) AS '第七列'
FROM(
SELECT pid, CASE aa WHEN 1 THEN pdt ELSE NULL END AS 'col1',
CASE aa WHEN 2 THEN pdt ELSE NULL END AS 'col2',
CASE aa WHEN 3 THEN pdt ELSE NULL END AS 'col3',
CASE aa WHEN 4 THEN pdt ELSE NULL END AS 'col4',
CASE aa WHEN 5 THEN pdt ELSE NULL END AS 'col5',
CASE aa WHEN 6 THEN pdt ELSE NULL END AS 'col6',
CASE aa WHEN 7 THEN pdt ELSE NULL END AS 'col7'
FROM
(
SELECT pid, pdt, ROW_NUMBER() OVER( PARTITION BY pid ORDER BY pid, pdt ) as aa FROM TEST11
) wk
) wk1
GROUP BY pid
0.00 0.00 2012-03-01 07:32:46
0.00 106.03 2012-04-03 17:56:09
0.00 106.66 2012-03-27 17:20:22
43.50 106.78 2012-04-06 16:34:34
30.10 107.33 2012-04-11 16:26:11
4.10 108.63 2012-04-04 10:56:23
17.00 111.78 2012-04-04 16:59:07
10.20 111.78 2012-04-04 17:00:48
0.50 112.17 2012-03-03 10:29:20
45.00 112.68 2012-04-02 12:26:23
32.70 116.13 2012-04-04 18:45:50
1.00 118.33 2012-03-24 12:03:39
需要查询S6最小时间的的数据,去掉多余的数据
要分s6最小的号码?
首先你要查最小时间的数据 可以用一个比较灵活的方式
datediff(day,'时间','2012-01-01)求时间和2012-01-01最小的差距的就是时间最小的
查询语句如下:
select s6
from table
where min(datediff(day,'时间','2012-01-01))desc