查询记录中最小记录
查询入库记录中最低进价记录

入库记录表（store_in）

单号(p1) 单序(p2) 代码(pru_p20) 品名(pru_p1)规格(pru_p4)产地(pru_p3) 进价(p8) 数量(p7) 供应商代码(p14)
0510170001 1 1010008鼻炎康片50T*10瓶佛山德众5.50000060.000000021
0510180001 10 1010008鼻炎康片50T*10瓶佛山德众5.40000050.000000021
05040500221 1010008鼻炎康片50T*10瓶佛山德众5.40000020.000000026
05040800087 1010008鼻炎康片50T*10瓶佛山德众5.40000080.000000026
05092900254 1010008鼻炎康片50T*10瓶佛山德众5.40000050.000000026
05112500175 1010008鼻炎康片50T*10瓶佛山德众5.400000200.000005226
051031000915 1010008鼻炎康片50T*10瓶佛山德众5.400000100.000005188
051120000212 1010008鼻炎康片50T*10瓶佛山德众5.600000300.000005213
05112300222 1010008鼻炎康片50T*10瓶佛山德众5.400000200.000005213

查询第一种结果， 最低进价（如果有相等的取每个供应商最大单号）

0510180001 101010008鼻炎康片50T*10瓶佛山德众5.40000050.000000021
051125001751010008鼻炎康片50T*10瓶佛山德众5.400000200.000005226
0510310009151010008鼻炎康片50T*10瓶佛山德众5.400000100.000005188
051123002221010008鼻炎康片50T*10瓶佛山德众5.400000200.000005213

查询第二种结果，最低进价的最大单号.

051125001751010008鼻炎康片(新)50T*10瓶佛山德众5.400000200.000005226


[解决办法]
--再精确一点
Create table #store_in
(
单号 varchar(10),
单序 int,
代码 varchar(10),
品名 varchar(10),
规格 varchar(10),
产地 varchar(10),
进价 float,
数量 int,
供应商代码 varchar(10)
)
insert into #store_in(单号,单序,代码,品名,规格,产地,进价,数量,供应商代码)
select '0510170001 ',1, '1010009 ', '眼炎康片 ', '50T*10瓶 ', '佛山德众 ',5.500000,60.0000, '00021 ' union all
select '0510170001 ',1, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.500000,60.0000, '00021 ' union all
select '0510180001 ',10, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,50.0000, '00026 ' union all
select '0504050022 ',1, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,80.0000, '00026 ' union all
select '0504080008 ',7, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,60.0000, '00026 ' union all
select '0509290025 ',4, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,50.0000, '00026 ' union all
select '0511250017 ',5, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,200.0000, '05226 ' union all
select '0510310009 ',15, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,100.0000, '05188 ' union all
select '0511200002 ',12, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.600000,300.0000, '05213 ' union all
select '0511230022 ',2, '1010008 ', '鼻炎康片 ', '50T*10瓶 ', '佛山德众 ',5.400000,200.0000, '05213 '
go
select * from
(
select * from #store_in a where (select count(*) from #store_in where 代码=a.代码 and 进价 <a.进价)=0
) c where (select count(*) from
(
select * from #store_in a where (select count(*) from #store_in where 代码=a.代码 and 进价 <a.进价)=0

) d where d.代码=c.代码 and d.供应商代码=c.供应商代码 and d.单号> c.单号
)=0

--结果
单号 单序 代码 品名 规格 产地 进价 数量 供应商代码
---------- ----------- ---------- ---------- ---------- ---------- ---------------------- ----------- ----------
0510170001 1 1010009 眼炎康片 50T*10瓶 佛山德众 5.5 60 00021
0510180001 10 1010008 鼻炎康片 50T*10瓶 佛山德众 5.4 50 00026
0511250017 5 1010008 鼻炎康片 50T*10瓶 佛山德众 5.4 200 05226
0510310009 15 1010008 鼻炎康片 50T*10瓶 佛山德众 5.4 100 05188
0511230022 2 1010008 鼻炎康片 50T*10瓶 佛山德众 5.4 200 05213

(5 行受影响)