数据如下id date price
1 2012-10-01 5
2 2012-10-02 6
3 2012-10-03 8
4 2012-10-04 3
5 2012-10-05 7如何用一条语句得到如下结果(分别求出最小日期及最大日期的price)date price date price
2012-01-01 5 2012-10-05 7
1 2012-10-01 5
2 2012-10-02 6
3 2012-10-03 8
4 2012-10-04 3
5 2012-10-05 7如何用一条语句得到如下结果(分别求出最小日期及最大日期的price)date price date price
2012-01-01 5 2012-10-05 7
解决方案 »
- 请问数据库文件是哪个
- 请帮我看下这个varchar(8000)的变量怎么到500就被截断了?
- |zyciis| 关于商品的类别和小类的设计问题 大家来帮帮看字段要怎么来设计
- 同一记录里面的字段统计
- 请问在WinXP下安装SQL Server2005能否完备使用其对XML的支持功能。
- 多表连接如何做 ?
- 附加数据库时报错了,大家都看看啊
- 请问各位大侠该功能用SQL语句如何实现(数据库为ase)?急(在线等待)
- 请教一个Oracle问题,各路高手请帮忙!
- 请问如何在SQLSever中实现类似Oracle这条语句的功能: Select distinct * from test where rownum <= 100
- 如何写这样的SQL语句,十万火急,谢谢
- MS-SQL中,在CASE判断时能否将int类型转换为Varchar类型
insert into tb values(1 , '2012-10-01', 5)
insert into tb values(2 , '2012-10-02', 6)
insert into tb values(3 , '2012-10-03', 8)
insert into tb values(4 , '2012-10-04', 3)
insert into tb values(5 , '2012-10-05', 7)
goselect m.date , m.price,
n.date , n.price
from
(select top 1 date , price from tb order by date) m,
(select top 1 date , price from tb order by date desc) n
/*
date price date price
------------------------------------------------------ ----------- ------------------------------------------------------ -----------
2012-10-01 00:00:00.000 5 2012-10-05 00:00:00.000 7(所影响的行数为 1 行)
*/select * from (select top 1 date , price from tb order by date) t
union all
select * from (select top 1 date , price from tb order by date desc) t
/*
date price
------------------------------------------------------ -----------
2012-10-01 00:00:00.000 5
2012-10-05 00:00:00.000 7(所影响的行数为 2 行)
*/drop table tb
b.maxDate,
a.price,
b.minDate,
a.price
FROM [table] AS a
INNER JOIN
(SELECT
id,
MAX(date) AS maxDate,
MIN(date) AS minDate,
FROM [table]
GROUP BY price) AS b
ON a.id = b.id
(
--最大
select top 1 date,price from 表名 order by date desc
) x ,(
--最小
select top 1 date,price from 表名 order by date asc
) y
drop table ge
create table ge(
id int identity(1,1),
date datetime,
price real)
insert into ge select '2012-10-01',5 union all
select '2012-10-02',6 union all
select '2012-10-03',8 union all
select '2012-10-04',3 union all
select '2012-10-05',7
go
with cs as(
select c_id=row_number() over(order by date),* from ge)
select date,price from cs
where c_id=1 or id=SCOPE_IDENTITY()
(
Select Max(date) as dateMax, Min(date) as dateMin From T
) as a
inner join T as t1 on a.dateMax = t1.date
inner jon T as t2 on a.dateMin = t2.date
(select date,val from dfTable order by date asc limit 1) a
,
(select date,val from dfTable order by date desc limit 1) d
)