table_1:
zhuti_id(int)   zhuti_addtime(datetime)   zhuti_state(int)
-------------------------------------------------
   1           2007-4-1 10:00:00         0
   2           2007-4-2 10:00:00         1
   3           2007-4-3 10:00:00         0
   4           2007-4-4 10:00:00         2
table_2:
huifu_zhutiid(int)   huifu_addtime(datetime)
--------------------------------------------------
   1                 2007-4-1 11:00:00
   1                 2007-4-4 11:00:00
   3                 2007-4-4 10:00:00条件:查询表table_1级联table_2条件zhuti_id = huifu_zhutiid,只级联huifu_addtime最大的那一条,如果huifu_addtime为空,那么huifu_addtime = zhuti_addtime。
排序方法:先按照zhuti_state降序排列,在按照huifu_addtime降序排列。结果:
zhuti_id(int)   zhuti_addtime(datetime)   zhuti_state(int)   huifu_addtime(datetime)
------------------------------------------------------------------------------------------
     4               2007-4-4 10:00:00          2               2007-4-4 10:00:00
     2               2007-4-2 10:00:00          1               2007-4-2 10:00:00
     1               2007-4-1 10:00:00          0               2007-4-4 11:00:00
     3               2007-4-3 10:00:00          0               2007-4-4 10:00:00

解决方案 »

  1.   

    select table_1.zhuti_id, table_1.zhuti_addtime,table_1.zhuti_state,(case when a.huifu_addtime is null then table_1.zhuti_addtime end ) as huifu_addtime
    from table_1 left outer  join (select huifu_zhutiid , max(huifu_addtime) as huifu_addtime from  table_2)a
    on table_1.zhuti_id=a.huifu_zhutiid 
    order by table_1.zhuti_state  DESC , huifu_addtime DESC
      

  2.   

    上面那个错了,这个对的select table_1.zhuti_id, table_1.zhuti_addtime,table_1.zhuti_state,(case when a.huifu_addtime is null then table_1.zhuti_addtime else a.huifu_addtime end ) as huifu_addtime
    from table_1 left outer  join (select huifu_zhutiid , max(huifu_addtime) as huifu_addtime from  table_2 group by huifu_zhutiid )a
    on table_1.zhuti_id=a.huifu_zhutiid 
    order by table_1.zhuti_state  DESC , huifu_addtime DESC
      

  3.   

    select zhuti_id,zhuti_addtime,zhuti_state,case huifu_addtime when is null then zhuti_addtime
                else huifu_addtime
      end as huifu_addtime
    from table_1 a inner join (
    Select huifu_zhutiid,huifu_addtime
    From table_2 c
    Where Not Exists(Select * From table_2 Where huifu_zhutiid = c.huifu_zhutiid 
    And huifu_addtime > a.huifu_addtime)
    ) b 
    on a.zhuti_id=b.huifu_zhutiid
    order by zhuti_state desc,huifu_addtime desc
      

  4.   

    更改一下:
    select a.zhuti_id,a.zhuti_addtime,a.zhuti_state,huifu_addtime = case when b.huifu_addtime is null then zhuti_addtime
                else huifu_addtime
      end
    from table_1 a left join (
    Select huifu_zhutiid,huifu_addtime
    From table_2 c
    Where Not Exists(Select * From table_2 Where huifu_zhutiid = c.huifu_zhutiid 
    And huifu_addtime > c.huifu_addtime)
    ) b 
    on a.zhuti_id=b.huifu_zhutiid
    order by zhuti_state desc,huifu_addtime desc
    返回:
    zhuti_id    zhuti_addtime                                          zhuti_state huifu_addtime                                          
    ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ 
    4           2007-04-04 10:00:00.000                                2           2007-04-04 10:00:00.000
    2           2007-04-02 10:00:00.000                                1           2007-04-02 10:00:00.000
    1           2007-04-01 10:00:00.000                                0           2007-04-04 11:00:00.000
    3           2007-04-03 10:00:00.000                                0           2007-04-04 10:00:00.000(所影响的行数为 4 行)
      

  5.   

    那如果我想结果里面多一列huifu_numb ,也就是table_2中记录出现的次数请问如何实现?
    zhuti_id(int) zhuti_addtime(datetime) zhuti_state(int) huifu_addtime(datetime) huifu_numb
    -------------------------------------------------------------------------
         4      2007-4-4 10:00:00          2      2007-4-4 10:00:00    0
         2      2007-4-2 10:00:00          1      2007-4-2 10:00:00    0
         1      2007-4-1 10:00:00          0      2007-4-4 11:00:00    2
         3      2007-4-3 10:00:00          0      2007-4-4 10:00:00    1