我想获得每个SPID的最后一次进发时间怎么弄

select a.rq,a.spid,a.dj from jh_rkmx a,spkfk b
where a.spid=b.spid
group by a.rq ,a.spid,a.dj
order by a.spid,a.rq desc我想要每个SPID的最后一次进货时间和单价 怎么弄啊

解决方案 »

  1.   

    表 jh_rkmx
    djbh spid  rq  dj 
    表 spkdk
    spid spbh spmch   
    2012-09-11 SPH00000001 13.800000
    2012-08-20 SPH00000001 16.850000
    2012-07-10 SPH00000001 16.350000
    2012-07-03 SPH00000001 16.350000
    2012-06-24 SPH00000001 16.350000
    2012-05-29 SPH00000001 16.350000
    2012-05-19 SPH00000001 16.850000
    2012-05-09 SPH00000001 16.350000
    2012-05-01 SPH00000001 16.350000
    2012-03-02 SPH00000001 16.350000
    2012-02-17 SPH00000001 16.350000
    2012-01-06 SPH00000001 16.350000
    2011-08-05 SPH00000001 16.350000
    2011-02-24 SPH00000001 16.150000
    2011-02-11 SPH00000001 16.850000
    2010-12-31 SPH00000001 16.850000
    2010-12-29 SPH00000001 16.850000
    2010-12-27 SPH00000001 16.850000
    2010-12-07 SPH00000001 17.184230
    2010-12-07 SPH00000005 .986000
    2010-12-07 SPH00000006 .935190
    2012-09-13 SPH00000009 4.000000
    2012-07-14 SPH00000009 3.890000
    2012-07-13 SPH00000009 4.000000
    2012-07-04 SPH00000009 3.890000
    2012-06-08 SPH00000009 3.890000
    2011-11-19 SPH00000009 4.000000
    2011-11-04 SPH00000009 4.000000
    2011-10-09 SPH00000009 4.000000
    2011-08-24 SPH00000009 4.000000
    2011-08-17 SPH00000009 4.120000
    2011-07-01 SPH00000009 4.200000
    2010-12-27 SPH00000009 4.000000
    2012-09-28 SPH00000011 64.320000
    2012-09-22 SPH00000011 64.320000
    2012-09-12 SPH00000011 64.320000
    2012-08-22 SPH00000011 71.000000
    2012-08-14 SPH00000011 71.000000
    2012-08-07 SPH00000011 71.000000
    2012-07-31 SPH00000011 71.000000
    2012-06-01 SPH00000011 65.000000
    2012-05-09 SPH00000011 68.500000
    2012-05-03 SPH00000011 68.500000
    2012-04-25 SPH00000011 68.500000
    2012-04-10 SPH00000011 68.500000
    2012-03-27 SPH00000011 68.500000
    2012-03-21 SPH00000011 68.500000
    2012-03-06 SPH00000011 68.500000
    2012-03-04 SPH00000011 68.500000
    2012-02-25 SPH00000011 68.500000
    2012-02-16 SPH00000011 68.500000
    2012-02-07 SPH00000011 68.500000
    2012-01-30 SPH00000011 68.500000
    2012-01-18 SPH00000011 68.500000
    2012-01-10 SPH00000011 68.500000
    2011-11-30 SPH00000011 68.500000
    2011-11-16 SPH00000011 68.500000
    2011-10-12 SPH00000011 68.500000
    2011-10-11 SPH00000011 68.500000
    2011-09-02 SPH00000011 68.000000
    2011-08-13 SPH00000011 68.000000
    2011-05-05 SPH00000011 65.000000
    2011-04-05 SPH00000011 68.000000
    2011-03-18 SPH00000011 68.000000
    2011-03-11 SPH00000011 68.000000
    2011-02-13 SPH00000011 68.000000
    2011-01-30 SPH00000011 68.000000
    2011-01-13 SPH00000011 68.000000
    2010-12-27 SPH00000011 68.000000
    2010-12-21 SPH00000011 68.000000
    2010-12-07 SPH00000011 67.199050
    2010-12-07 SPH00000011 67.200000
    2012-06-16 SPH00000013 1.300000
    2012-06-06 SPH00000013 1.300000
    2012-06-02 SPH00000013 1.300000
    2011-09-14 SPH00000013 1.130000
    2011-09-10 SPH00000013 1.130000
    2011-08-30 SPH00000013 1.000000
    2011-06-25 SPH00000013 .950000
    2010-12-31 SPH00000013 .750000
    2010-12-07 SPH00000013 .750000
    2011-09-10 SPH00000016 4.850000
    2010-12-11 SPH00000016 4.850000
    2012-02-07 SPH00000024 1.650000
    2012-06-26 SPH00000026 7.000000
    2012-06-14 SPH00000026 7.000000
    2012-06-12 SPH00000026 7.000000
    2011-08-27 SPH00000026 7.000000
    2011-08-19 SPH00000026 7.000000
    2011-07-16 SPH00000026 7.000000
    2011-05-05 SPH00000026 7.000000
    2011-05-03 SPH00000026 7.000000
    2011-04-23 SPH00000026 7.000000
    2011-01-27 SPH00000026 7.000000
    2011-01-17 SPH00000026 7.000000
    2010-12-15 SPH00000026 7.000000
    2010-12-07 SPH00000026 7.000000
    2012-08-06 SPH00000029 9.800000
      

  2.   

    不知道怎么说你好了。
    1、把两表的结构弄出来。最好给出数据类型。不然弄出来的脚本不合适。
    2、你那些数据是一个表的吗？spid怎么对应是日期？没想到睡醒了个午觉都还没人回答....你就这样数据一贴，个个人都要重新弄数据的脚本。非常浪费时间。
      

  3.   


    select a.spid,b.*  from spkdk a 
    cross apply
    (select top(1) rq, dj 
    from jh_rkmx spid = a.spid order by rq desc
    )b
    ,
      

  4.   

    最后一次也就是里当前日期最接近的一次吗？？可以先把两表连接查询，在用
    with t
    as(
    select *,
    px=row_number()over(partition by SPID order by riqi desc)
    from（你的查询语句)a
    select * from t where px=1
      

  5.   

    SELECT * FROM jh_rkmx  AS T WHERE 1>(SELECT COUNT(*) FROM jh_rkmx  WHERE SPID=T.SPID AND rq>T.rq)
      

  6.   

    select a.spid,b.*  from spkdk a 
    cross apply
    (select top(1) rq, dj 
    from jh_rkmx spid = a.spid order by rq desc
    )b