救命啊!!!!!!!!!!!!! 这个 sql 语句怎么写??? 哪位高手知道???? >>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>

[zhuanxin]

编号 公司 产品 日期 价格
id (key) company product date value
--------------------------------------------------------------
000 a p0 2002-12-02 30
001 a p0 2002-01-02 40
002 a p0 2002-06-02 48
003 b p0 2002-02-02 35
004 b p0 2002-03-02 44
005 b p0 2002-10-02 50
006 a p1 2002-12-02 30

.......



怎样得出 所有产品 的 **最终** **最低** 价格表 (如下图)?


编号 公司 产品 日期 价格
id (key) company product date value
-----------------------------------------------------------------
002 a p0 2002-06-02 48
006 a p1 2002-12-02 30

[dlkfth]

select * from 表 a inner join ( select company,product,max(value) as maxvalue from 表 a group by company,product) b on a.company=b.company and a.product=b.product and a.value=b.maxvalue

[愉快的登山者]

select * from tablename as A
where date = (select max(date) from tablename where product = A.product)
and value = (select min(value) from tablename where product = A.product
and date = A.date)
order by product

[zhuanxin]

select * from x a inner join
( select company,product,max(value) as maxvalue from x a group by company,product) b
on a.company=b.company and a.product=b.product and a.value=b.maxvalue


结果:
--------------------------------------------------------------------
id product company value company product maxvalue
--------------------------------------------------------------
0005 p0 v1 80 v1 p0 80
0004 p0 v0 65 v0 p0 65


需要的其实是如下数据
--------------------------------------------------------------------
id product company value company product maxvalue
--------------------------------------------------------------
0004 p0 v0 65 v0 p0 65

也就是:

在得出所有产品在 每个公司最终的价格之后, 对全体公司的价格求出最低价. 结果集的结构和原始表的结构一样.

[zhuanxin]

tj_dns(愉快的登山者):

结果:
--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0005 p0 v1 80 2003-05-23 11:16:29.000

还是不对哦



原始表是:
--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0005 p0 v1 80 2003-05-23 11:16:29.000
0000 p0 v0 50 2002-06-23 11:14:58.000
0001 p0 v0 60 2003-01-23 11:15:14.000
0004 p0 v0 65 2003-04-23 11:16:05.000
0002 p0 v1 55 2003-02-23 11:15:29.000
0003 p0 v1 70 2003-03-23 11:15:43.000
0005 p0 v1 80 2003-05-23 11:16:29.000
...

我需要的结果是: 所有产品(p0, p1, .....) 的 最低价格, 以及这个最低的价格对应的:
价格编号,产品,公司,时间,价格,

--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0004 p0 v0 65 2003-04-23 11:16:05.000
p1
p2

[zhuanxin]

原始表写错了 原始表应该是:
--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0000 p0 v0 50 2002-06-23 11:14:58.000
0001 p0 v0 60 2003-01-23 11:15:14.000
0004 p0 v0 65 2003-04-23 11:16:05.000
0002 p0 v1 55 2003-02-23 11:15:29.000
0003 p0 v1 70 2003-03-23 11:15:43.000
0005 p0 v1 80 2003-05-23 11:16:29.000
...


我需要的结果是: 所有产品(p0, p1, .....) 的 最低价格, 以及这个最低的价格对应的:
价格编号,产品,公司,时间,价格,

--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0004 p0 v0 65 2003-04-23 11:16:05.000
p1
p2
...



tj_dns(愉快的登山者):

结果:
--------------------------------------------------------------------
id product company value date
--------------------------------------------------------------
0005 p0 v1 80 2003-05-23 11:16:29.000

还是不对哦

[pengdali]

select * from 表 tem where [date]=(select max([date]) from 表 where product=tem.product)

[CrazyFor]

select * from 表 a where not exists(select 1 from 表 where product=a.product and [date]>a.[date]) or not exists(select 1 from 表 where product=a.product and [value]<a.[value])

[Hrb]

SELECT ISNULL(tminp.id,tmaxp.id) Id,
ISNULL(tminp.product,tmaxp.product) Product,
ISNULL(tminp.company,'') Company(Min),
ISNULL(tminp.value,0) Min Value,
ISNULL(tmaxp.company,'') Company(Max),
ISNULL(tmaxp.value,0) Value(Max),
FROM
(
SELECT DISTINCT *
FROM TABLE1 t1
INNER JOIN
(
SELECT product,MIN(value) AS value
FROM TABLE1
GROUP BY product) tMin tm ON tm.product=t1.product and tm.value=t1.value)
) tminp

FULL JOIN
(
SELECT DISTINCT *
FROM TABLE1 t1
INNER JOIN
(
SELECT product,MAX(value) AS value
FROM TABLE1
GROUP BY product) tMax tm ON tm.product=t1.product and tm.value=t1.value)
) tmaxp ON tmaxp.id=tminp.id

[愉快的登山者]

你是只考虑日期，不考虑时间，去掉时间即可：
select * from tablename as A
where convert(char(10), date, 120) =
(select convert(char(10), date, 120) from tablename where product = A.product)
and value = (select min(value) from tablename where product = A.product
and convert(char(10), date, 120) = convert(char(10), A.date, 120))
order by product