SQL高手帮忙啊!一个去除重复的语句 要实现类似select * from TABLE where count(ziduan2)>2的功能
我有一个表,4个字段:id(主键自增),name(姓名),chepai(车牌号),wherePunish(处罚地点)chepai有重复数据,id没有。现在要查询出chep...
我有一个表,4个字段:id(主键自增),name(姓名),chepai(车牌号),wherePunish(处罚地点)
chepai有重复数据,id没有。现在要查询出chepai大于2次的所有数据,怎么查?select * from TABLE where count(chepai)>2,这样查肯定不行。网上搜索了好多查询重复数据的,都不太对。select * from TABLE where chepai in (select chepai from TABLE group by chepai having count(chepai)>2,这条语句能查询出重复的,但结果不是唯一的,如果chepai有5个,查询结果就有5条,而我只想保留1条。加distinct的没有效果
4个字段,除了id,其余全是varchar类型的
tangjun0102 和 殒尘 你们的方法都不行啊 展开
chepai有重复数据,id没有。现在要查询出chepai大于2次的所有数据,怎么查?select * from TABLE where count(chepai)>2,这样查肯定不行。网上搜索了好多查询重复数据的,都不太对。select * from TABLE where chepai in (select chepai from TABLE group by chepai having count(chepai)>2,这条语句能查询出重复的,但结果不是唯一的,如果chepai有5个,查询结果就有5条,而我只想保留1条。加distinct的没有效果
4个字段,除了id,其余全是varchar类型的
tangjun0102 和 殒尘 你们的方法都不行啊 展开
6个回答
展开全部
刚才直接复制你代码改的,有点问题,已经修正
select *
from TABLE
where id in (
select max(id) as id from TABLE group by chepai having count(chepai)>2
)
select *
from TABLE
where id in (
select max(id) as id from TABLE group by chepai having count(chepai)>2
)
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
用分组试试
select id,name,chepai,punish table where sum(chepai)>2 group by chepai,id,name,punish
select id,name,chepai,punish table where sum(chepai)>2 group by chepai,id,name,punish
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
2011-11-23
展开全部
select top 1 * 是否符合要求呢?比如:
select top 1 * from TABLE
where chepai in
(select chepai from TABLE group by chepai having count(chepai)>2
select top 1 * from TABLE
where chepai in
(select chepai from TABLE group by chepai having count(chepai)>2
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
2011-11-23
展开全部
你的具体情况不清楚,不过思路应该是如下:
select id,name,chepai,wherepunish from (select id,name,chepai,wherepunish , count(chepai) over (partition by chepai) dd,rank() over (partition by id) ss from table ) where dd>2 and ss=1;
select id,name,chepai,wherepunish from (select id,name,chepai,wherepunish , count(chepai) over (partition by chepai) dd,rank() over (partition by id) ss from table ) where dd>2 and ss=1;
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
什么数据库?版本多少?
追问
SQL Server 2008
追答
用殒尘的就行,或者
declare @t table (id int, name varchar(10), chepai varchar(10), wherePunish varchar(10))
insert into @t
SELECT '1','A','A001','AA' UNION ALL
SELECT '2','A','A001','AB' UNION ALL
SELECT '3','A','A001','AC' UNION ALL
SELECT '4','B','A002','DD' UNION ALL
SELECT '5','B','A002','CD' UNION ALL
SELECT '6','B','A002','BD' UNION ALL
SELECT '7','C','B001','BB' UNION ALL
SELECT '8','C','C001','CC'
SELECT * from (
SELECT *
,ROW_NUMBER() OVER(PARTITION BY chepai ORDER BY id) AS RowNumber
FROM @t
WHERE 1=1
and chepai in (
select chepai from @t group by chepai having COUNT(1)> 2
)
) as x
where x.RowNumber = 1
想告诉的是08里面有很多新函数,对排名很有帮助
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询