SELECT TOP 10 id,which,sh,tt,dtime FROM Article WHERE (type='resource') AND (tj=1) AND (sh=1) ORDER BY dtime DESC我现在选出排在表article中前10条记录. 但是发现 tt (标题)这10条记录中有重复的记录.怎么加筛选语句筛掉 tt 相同的记录(tt要保留一条)..................................................................
INNER JOIN
SELECT (max(id) mid,tt mtt from Article group by dtime) b
ON mid=id and tt=mtt
ORDER BY dtime DESC我假没你的ID不重复.
未测试,可能有手误
FROM Article a
WHERE (type='resource') AND (tj=1) AND (sh=1)
and not exists (
select 1 from Article
where (type='resource') AND (tj=1) AND (sh=1)
and tt=a.tt
and dtime>dtime
)
ORDER BY dtime DESC
SELECT TOP 10 id,which,sh,tt,dtime
FROM Article a
WHERE (type='resource') AND (tj=1) AND (sh=1)
and not exists (
select 1 from Article
where (type='resource') AND (tj=1) AND (sh=1)
and tt=a.tt
and dtime>a.dtime
)
ORDER BY dtime DESC
主要考虑排序,但是有可能还会重复,如果重复,改成SELECT TOP 10 id,which,sh,tt,dtime
FROM Article a
WHERE (type='resource') AND (tj=1) AND (sh=1)
and id= (
select top 1 id from Article
where (type='resource') AND (tj=1) AND (sh=1)
and tt=a.tt
ORDER BY dtime DESC
)
ORDER BY dtime DESC
FROM
(
SELECT id,which,sh,tt,dtime
FROM Article
WHERE (type='resource') AND (tj=1) AND (sh=1)
GROUP BY id,which,sh,tt,dtime
)A
ORDER BY dtime DESC
FROM
Article a
inner join
(
SELECT TOP 10 which,sh,tt,dtime
FROM Article
WHERE (type='resource') AND (tj=1) AND (sh=1)
GROUP BY which,sh,tt,dtime
ORDER BY dtime DESC
)b
on
a.which=b.which
and a.sh=b.sh
and a.tt=b.tt
and a.dtime=b.dtime