肯定有难度的SQL语句

[java_liu]

有一个视图test,有如下列：

product inDate batch innum
A 2002-10-12 QC2002 8000
B 2002-11-10 QC2002 5000
C 2002-10-15 QC2002 6000
C 2003-1-15 QC200A 3000
D 2002-10-4 QC2002 0
D 2003-1-15 QC2001 2000

我在相同产品多条记录，按日期最前的原则取批号、数量（不等于0），然后再在产品A、B、C、D中取最小的数量。
如产品C：要取QC2002的数量6000,D只能取QC2001数量2000
然后就A:8000,B:5000,C:6000,D:2000中选出最小数量2000
怎样写这个的SQL语句？
如能写出真是万分感激，不够分可以再加！！

[CCEO]

select min(innum) from
(
select * from test a
where a.inDate=(select min(inDate) from test b where b.product=a.product)
) as a

[CCEO]

select min(innum) from
(
select * from test a
where a.inDate=(select min(inDate) from test b where b.product=a.product and b.innum>0)
) as a

[happydreamer]

select min(innum) from
(
SELECT L.product, L.inDate, L.batch,L.innum
FROM test L
JOIN test R
ON L.product= R.prodct
GROUP BY L.product, L.inDate, L.batch,L.innum
HAVING L.inDate= Min(R.inDate)
) as a

[java_liu]

感谢各位朋友，等我试了后再给分了！

[java_liu]

非常感谢，原来是我本人功力还不够呀！
结了！

[greenery]

我也试了一下，回来就有这么多人了。

我试过可以。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO

CREATE TABLE [dbo].[test] (
[product] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[inDate] [datetime] NULL ,
[batch] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[innum] [int] NULL
) ON [PRIMARY]
GO


INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('A', '2002-10-12', 'QC2002', 8000)
INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('B', '2002-11-10', 'QC2002', 5000)
INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('C', '2002-10-15', 'QC2002', 6000)
INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('C', '2003-1-15', 'QC200A', 3000)
INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('D', '2002-10-4', 'QC2002', 0)
INSERT INTO [Northwind].[dbo].[test]([product], [inDate], [batch], [innum])
VALUES('D', '2003-1-15', 'QC2001', 2000)

SELECT [product], [inDate], [batch], [innum] FROM [dbo].[test] a
WHERE a.[inDate] = (select min([inDate]) FROM [dbo].[test] b where a.[product] = b.[product] and (b.[innum] >0) )