22,300
社区成员




Declare @Tb TABLE ([SMT_id] INT,[dyid] INT,[SMT_data] DATETIME)
INSERT Into @Tb
SELECT 1,9,'2012-4-12 14:33:36' UNION ALL
SELECT 2,11,'2012-4-18 19:23:36' UNION ALL
SELECT 3,9,'2012-4-12 14:33:36' UNION ALL
SELECT 4,9,'2012-4-12 14:33:36' UNION ALL
SELECT 5,12,'2012-5-19 19:23:36' UNION ALL
SELECT 6,11,'2012-3-17 19:23:36'
--------------开始查询--------------------------
Select * From @Tb
Select MAX(T1.SMT_id) As SMT_id , T1.dyid, T1.SMT_data
From @Tb T1 , (Select dyid, MAX(SMT_data) As SMT_data From @Tb
Group By dyid) T2
Where (T1.dyid = T2.dyid And T1.SMT_data = T2.SMT_data)
Group By T1.dyid, T1.SMT_data
Order By T1.SMT_data Desc
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[SMT_id] int,
[dyid] int,
[SMT_data] datetime
)
go
insert [test]
select 1,9,'2012-4-12 14:33:36' union all
select 2,11,'2012-4-18 19:23:36' union all
select 3,9,'2012-4-12 14:33:36' union all
select 4,9,'2012-4-12 14:33:36' union all
select 5,12,'2012-5-19 19:23:36' union all
select 6,11,'2012-3-17 19:23:36'
go
select * from test as a
where not exists(select 1 from test as b
where a.dyid=b.dyid and (a.SMT_data<b.SMT_data
or (a.SMT_data=b.SMT_data and a.SMT_id<b.SMT_id)))
--主意:我的表明后面你看都有一个as什么的,as后面的那个是给表取的别名,然后用别名标示字段,就表示这个字段是那个表的
/*
SMT_id dyid SMT_data
-------------------------------------------
2 11 2012-04-18 19:23:36.000
4 9 2012-04-12 14:33:36.000
5 12 2012-05-19 19:23:36.000
*/
sql="select SMT_cp.SMT_id as cpid,SMT_cp.SMT_sortid,SMT_cp.SMT_cpjm,SMT_cp.SMT_cpjg,SMT_cp.SMT_cpjgdw,SMT_cp.SMT_cpname,SMT_cp.SMT_cpjl,SMT_cp.SMT_yp_id,SMT_cp.SMT_pic,SMT_yp.paixu,SMT_yp.SMT_id,SMT_yp.SMT_user,SMT_yp.SMT_coname,SMT_yp.SMT_qyqq,SMT_yp.SMT_addtwo_id,SMT_yp.SMT_co,SMT_yp.SMT_vip,SMT_cp.SMT_date,0 as px from SMT_cp,SMT_yp where SMT_cp.SMT_id in (select max(SMT_cp.SMT_id) from SMT_cp Where 1=1 group by SMT_cp.SMT_yp_id )"&cjsort&" "&addsort&"and SMT_cp.SMT_yp_id=SMT_yp.SMT_id and (SMT_yp.SMT_vip<=5) and SMT_yp.SMT_key=1 union all select SMT_cp.SMT_id as cpid,SMT_cp.SMT_sortid,SMT_cp.SMT_cpjm,SMT_cp.SMT_cpjg,SMT_cp.SMT_cpjgdw,SMT_cp.SMT_cpname,SMT_cp.SMT_cpjl,SMT_cp.SMT_yp_id,SMT_cp.SMT_pic,SMT_yp.paixu,SMT_yp.SMT_id,SMT_yp.SMT_user,SMT_yp.SMT_coname,SMT_yp.SMT_qyqq,SMT_yp.SMT_addtwo_id,SMT_yp.SMT_co,SMT_yp.SMT_vip,SMT_cp.SMT_date,1 as px from SMT_cp,SMT_yp where SMT_cp.SMT_yp_id=SMT_yp.SMT_id and SMT_yp.SMT_key=1 order by px,SMT_yp.paixu,SMT_cp.SMT_date DESC"
--F姐姐写的就是对的
select
*
from
tb t
where
not exists(select 1 from tb where dyid=t.dyid
and (SMT_data>t.SMT_data or (SMT_data=t.SMT_data and SMT_id>t.SMT_id)))