查询记录中最小记录

查询入库记录中最低进价记录入库记录表（store_in）单号(p1)   单序(p2) 代码(pru_p20)  品名(pru_p1)规格(pru_p4)产地(pru_p3)          进价(p8)    数量(p7)   供应商代码(p14)
0510170001      1     1010008 鼻炎康片 50T*10瓶 佛山德众 5.500000 60.0000 00021
0510180001     10     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 50.0000 00021
0504050022 1     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 20.0000 00026
0504080008 7     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 80.0000 00026
0509290025 4     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 50.0000 00026
0511250017 5     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 200.0000 05226
0510310009 15    1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 100.0000 05188
0511200002 12    1010008 鼻炎康片 50T*10瓶 佛山德众 5.600000 300.0000 05213
0511230022 2     1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 200.0000 05213 查询第一种结果， 最低进价（如果有相等的取每个供应商最大单号）0510180001     10 1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 50.0000 00021
0511250017 5 1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 200.0000 05226
0510310009 15 1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 100.0000 05188
0511230022 2 1010008 鼻炎康片 50T*10瓶 佛山德众 5.400000 200.0000 05213查询第二种结果，最低进价的最大单号.0511250017 5 1010008 鼻炎康片(新) 50T*10瓶 佛山德众 5.400000 200.0000 05226

解决方案 »

  1.   

    Create table #store_in
    (
    单号 varchar(50),
    单序 int,
    代码 varchar(50),
    品名 varchar(50),
    规格 varchar(50),
    产地 varchar(50),
    进价 float,
    数量 int,
    供应商代码 varchar(50)
    )
    insert into #store_in(单号,单序,代码,品名,规格,产地,进价,数量,供应商代码)
    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'
    select a.代码,a.品名,a.规格,a.产地,a.进价,a.供应商代码 from #store_in a 
    where a.[进价]=(select min([进价]) from #store_in where a.代码=代码)
    group by a.代码,a.品名,a.规格,a.产地,a.进价,a.供应商代码
    order by a.供应商代码 desc
    drop table #store_in
      

  2.   

    楼上写的SQL语句不对啊select a.pru_p20,a.p8,a.p0,a.p20,a.p1,a.pru_p1,a.p14,a.p7,a.p4,a.p15,a.p2,a.pru_p3,a.pru_p4
      from store_in_close a
        inner join (select  min(p8) as minp8, pru_p20
          from store_in_close
              group by  pru_p20) b
                  on a.pru_p20=b.pru_p20  and a.p8=b.minp8我这个句语,只取出来了最低进价的记录,没有取出"最低进价（如果有相等的取每个供应商最大单号）"和"最低进价的最大单号."请赐教
      

  3.   

    Create table #store_in
    (
    单号 varchar(50),
    单序 int,
    代码 varchar(50),
    品名 varchar(50),
    规格 varchar(50),
    产地 varchar(50),
    进价 float,
    数量 int,
    供应商代码 varchar(50)
    )
    insert into #store_in(单号,单序,代码,品名,规格,产地,进价,数量,供应商代码)
    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.进价)=0
    ) c where (select count(*) from
    (
    select * from #store_in a  where (select count(*) from #store_in where 进价<a.进价)=0
    ) d where d.供应商代码=c.供应商代码 and d.单号>c.单号
     )=0
    --结果
    单号                                                 单序          代码                                                 品名                                                 规格                                                 产地                                                 进价                     数量          供应商代码
    -------------------------------------------------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- ----------- --------------------------------------------------
    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(4 行受影响)
      

  4.   

    Create table #store_in
    (
    单号 varchar(50),
    单序 int,
    代码 varchar(50),
    品名 varchar(50),
    规格 varchar(50),
    产地 varchar(50),
    进价 float,
    数量 int,
    供应商代码 varchar(50)
    )
    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.单号
     )=0
    --结果
    单号                                                 单序          代码                                                 品名                                                 规格                                                 产地                                                 进价                     数量          供应商代码
    -------------------------------------------------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- ----------- --------------------------------------------------
    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
    0510170001                                         1           1010009                                            眼炎康片                                               50T*10瓶                                            佛山德众                                               5.5                    60          00021(5 行受影响)
      

  5.   

    楼上写的还是不对啊,没有显示出最小的记录啊,是都显示出来了啊!
    现在补充说明一个问题
    查询入库记录中最低进价记录入库记录表（store_in）里面有很多记录，挑一些如下：单号        单序     代码        品名              规格         产地   进价     数量    供应商代码
    0412060001    1     2011800   螺旋霉素片      0.1g*12t*2板    山东新华    1.40      10        00008
    0504080101    5     2011800   螺旋霉素片      0.1g*12t*2板    山东新华    1.50      20        00010     
    0508010006    2     2011800   螺旋霉素片      0.1g*12t*2板    山东新华    1.40      30        00008  
    0508090009    7     2011800   螺旋霉素片      0.1g*12t*2板    山东新华    1.40      50        00008  
    0510170001    1     1010008 鼻炎康片 50T*10瓶      佛山德众    5.50      60        00021
    0510180001   10     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40     50        00021
    0504050022    1     1010008 鼻炎康片 50T*10瓶      佛山德众    5.40      20       00026
    0504080008    7     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40      80       00026
    0509290025    4     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40      50        00026
    0511250017    5     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40     200       05226
    0510310009   15     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40     100       05188
    0511200002   12     1010008 鼻炎康片 50T*10瓶      佛山德众  5.600     300       05213
    0511230022    2     1010008 鼻炎康片 50T*10瓶      佛山德众   5.40     200       05213 查询第一种结果， 最低进价（如果有相等的取每个供应商最大单号）0412060001      1     2011800   螺旋霉素片      0.1g*12t*2板    山东新华   1.40      10    00008
    0508090009      7     2011800   螺旋霉素片      0.1g*12t*2板    山东新华   1.40      50    00008  
    0510180001     10     1010008 鼻炎康片 50T*10瓶 佛山德众   5.40      50    00021
    0511250017 5     1010008 鼻炎康片 50T*10瓶 佛山德众   5.40     200    05226
    0510310009     15     1010008 鼻炎康片 50T*10瓶 佛山德众   5.40     100    05188
    0511230022 2     1010008 鼻炎康片 50T*10瓶 佛山德众   5.40     200    05213查询第二种结果，最低进价的最大单号.0508090009    7     2011800   螺旋霉素片      0.1g*12t*2板    山东新华     1.40      50      00008  
    0511250017    5     1010008   鼻炎康片(新)     50T*10瓶       佛山德众    5.40     200      05226
      

  6.   

    --再精确一点
    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 行受影响)
      

  7.   

    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,'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'
    select * 
    from #store_in a
    where not exists(select * 
                     from #store_in 
                     where a.供应商代码 = 供应商代码 and (进价 - a.进价) < 0.1
                           and  cast(单号 as int) > cast(a.单号 as int)  )
    /*
    单号         单序          代码         品名         规格         产地         进价                                                    数量          供应商代码      
    ---------- ----------- ---------- ---------- ---------- ---------- ----------------------------------------------------- ----------- ---------- 
    0510170001 1           1010008    鼻炎康片       50T*10瓶    佛山德众       5.5                                                   60          00021
    0510180001 10          1010008    鼻炎康片       50T*10瓶    佛山德众       5.4000000000000004                                    50          00026
    0511250017 5           1010008    鼻炎康片       50T*10瓶    佛山德众       5.4000000000000004                                    200         05226
    0510310009 15          1010008    鼻炎康片       50T*10瓶    佛山德众       5.4000000000000004                                    100         05188
    0511230022 2           1010008    鼻炎康片       50T*10瓶    佛山德众       5.4000000000000004                                    200         05213
    */drop table #store_in
      

  8.   

    谢谢hrb2008()了，非常感谢，不过运算速度很慢，CUP都100%