表tab有列colA、colB、creDate,colA、colB列类型都为guid。creDate为datetime类型colA colB creDate
a…… ab…… 2012-10-11
a…… ab…… 2012-9-2
b…… ac…… 202-2-9
b…… 按creDate倒序,查询出列colB重复的最近创建的一条的所有信息
sql server 2008
a…… ab…… 2012-10-11
a…… ab…… 2012-9-2
b…… ac…… 202-2-9
b…… 按creDate倒序,查询出列colB重复的最近创建的一条的所有信息
sql server 2008
from tab as a inner join (select colb,MAX(credate) as credate from tab group by colb COUNT(1)>1) as b on a.colb=b.colb and a.credate=b.credate
--INSERT INTO Huang
--SELECT NEWID() ,NEWID(),'2012-10-11'
--UNION ALL
--SELECT NEWID() ,NEWID(),'2012-9-2'
--UNION ALL
--SELECT NEWID() ,NEWID(),'2012-2-9'
--按creDate倒序,查询出列colB重复的最近创建的一条的所有信息
SELECT cola ,
colb ,
MAX(credate) creDate
FROM Huang
WHERE colb IN ( SELECT colb
FROM Huang
GROUP BY colb
HAVING COUNT(1) > 1 )
语句有点问题消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'colB' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
where not exists(select 1 from tab where colB=a.colB and creDate>a.creDate)
colb ,
MAX(credate) creDate
FROM Huang
WHERE colb IN ( SELECT colb
FROM Huang
GROUP BY colb
HAVING COUNT(1) > 1 )
GROUP BY cola ,
colb
错误,子查询里面就不满足要求。colb 重复的就根据时间选最近的,不重复就选唯一的一条。。
FROM huang a
WHERE EXISTS ( SELECT 1
FROM ( SELECT colb ,
MAX(credate) creDate
FROM Huang
GROUP BY colb
) b
WHERE a.colb = b.colb
AND a.credate = b.credate )
where not exists(select 1 from tab as x
where x.colB=a.colB And x.creDate>a.creDate
)
where not exists(select 1 from tab as x
where x.colB=a.colB And x.creDate>a.creDate
)
group by colA,colB,creDateselect max(colA) as colA,colB,creDate from tab as a
where not exists(select 1 from tab as x
where x.colB=a.colB And x.creDate>a.creDate
)
group by colB,creDateselect min(colA) as colA,colB,creDate from tab as a
where not exists(select 1 from tab as x
where x.colB=a.colB And x.creDate>a.creDate
)
group by colB,creDate