源表a
name   date    price
aa    20120501  12
aa    20120502  16
aa    20120503  13
aa    20120504  14
bb    20120502  22
bb    20120503  26

能不能用一个sql语句得到结果表b?（日期最早和日期最晚的值）
name open high low close
aa    12   16   12  14
bb    22   26   22  26
open，日期date最小的price
close，日期date最大的price
high,low，最高和最低的price

想不出得到open和close的sql解决方法！
我的笨方法是用下面三个sql语句，先单独得到open、close、high、low值，然后再合并一起。
select name,max(price) as high,min(price) as low from 表a group by name
select name,min(date) as date,price as open from 表a group by name
select name,max(date) as date,price as close from 表a group by name


[ 本帖最后由 sh600839 于 2012-5-14 17:56 编辑 ]

日期最早和日期最晚的值，即找出日期字段中的最大值和最小值，是这样吗？

日期最早和日期最晚的值，即找出日期字段中的最大值和最小值，是这样吗？

**************
日期最晚的值：是找出日期最晚相对应的价格字段值。

create cursor a (name c(2),date c(8),price n(2))
insert into a values ("aa","20120501",12)
insert into a values ("aa","20120502",16)
insert into a values ("aa","20120503",13)
insert into a values ("aa","20120504",14)
insert into a values ("bb","20120502",22)
insert into a values ("bb","20120503",26)

*---PRICE的最大值与最小值
select name,min(price) as low,max(price) as high from a group by name order by name into cursor a_low_high 

*---最小DATE对应PRICE
select name,date,price as open from a where date==(select min(date) from a group by name) group by date into cursor a_min

*---最大DATE对应PRICE
select name,date,price as close from a where date==(select max(date) from a group by name) group by date into cursor a_max
select a.name,a.open,b.close,c.low,c.high from a_min a,a_max b,a_low_high c where a.name==b.name and a.name==c.name order by a.name into cursor a_tj
browse

VFP6.0

[ 本帖最后由 sdta 于 2012-5-15 00:05 编辑 ]

a表的记录多了怎么结果不对？
下面这句没有看懂！
select name,date,price as open from aa where date==(select min(date) from aa group by name) group by date



我得分两句才能正确
select name,min(date) as date from aa group by name into dbf bb
select a.name,a.date,a.price as open from aa a,bb b where a.name=b.name and a.date=b.date



[ 本帖最后由 sh600839 于 2012-5-15 17:12 编辑 ]

select min(date) from aa group by name &&查询最小日期

你的要求没说清楚，最后要达到什么效果

[ 本帖最后由 sdta 于 2012-5-15 22:07 编辑 ]

能用一个SQL语句实现吗？