SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE id=T.ID AND fiD=T. fid AND name=T.NAME)
select * from table t inner join (select id from table group by fid) as d on t.id = d.id
select * from QUEST_USER_INFO t inner join (select id from QUEST_USER_INFO group by questid ) as d on t.id = d.id列 'QUEST_USER_INFO.ID' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE fiD=T.fid)
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE fiD=T.fid)
select * from tb t where not exists(select 1 from tb where fid=t.fid and date<t.date)
晕多打了字段呀, SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE fiD=T.fid)
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-23 15:01:30 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[fid] int,[name] varchar(2),[date] datetime) insert [tb] select 1,1,'你','2009-9-23' union all select 2,2,'你','2009-9-22' union all select 3,3,'你','2009-9-21' union all select 4,3,'你','2009-9-20' --------------开始查询-------------------------- select * from tb t where not exists(select 1 from tb where fid=t.fid and date>t.date) ----------------结果---------------------------- /* id fid name date ----------- ----------- ---- ----------------------- 1 1 你 2009-09-23 00:00:00.000 2 2 你 2009-09-22 00:00:00.000 3 3 你 2009-09-21 00:00:00.000(3 行受影响)*/
select * from table t inner join
(select id from table group by fid) as d
on t.id = d.id
select * from QUEST_USER_INFO t inner join
(select id from QUEST_USER_INFO group by questid ) as d
on t.id = d.id列 'QUEST_USER_INFO.ID' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE fiD=T.fid)
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE fiD=T.fid)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-23 15:01:30
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[fid] int,[name] varchar(2),[date] datetime)
insert [tb]
select 1,1,'你','2009-9-23' union all
select 2,2,'你','2009-9-22' union all
select 3,3,'你','2009-9-21' union all
select 4,3,'你','2009-9-20'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where fid=t.fid and date>t.date)
----------------结果----------------------------
/* id fid name date
----------- ----------- ---- -----------------------
1 1 你 2009-09-23 00:00:00.000
2 2 你 2009-09-22 00:00:00.000
3 3 你 2009-09-21 00:00:00.000(3 行受影响)*/