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
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
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
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
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
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 行)
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