我可以执行mysql命令来过滤和删除重复的条目吗？
我可以执行mysql命令来过滤和删除重复的条目吗？
提问于 2013-02-21 06:49:03
我的表为linkage，其值如下
++++++++++++++++++++++++++
+ company_id + industry +
++++++++++++++++++++++++++
+ 1 + a +
+ 1 + b +
+ 2 + a +
+ 2 + c +
+ 3 + a +
+ 4 + c +
+ 5 + a +
++++++++++++++++++++++++++是否有一种方法，我可以分组我的行业，以获得最高的计数排序，通过desc订单的例子。
a = count 4
c = count 2
b = count 1然后删除重复的行业，只留下每个company_id计数较高的行业。
编辑1
此编辑基于OP注释I wish to only have the industry with the highest count, and deleting the rest of the entry for the same company_id. say for company_id 1, we will delete the second row, for company_id 2 we will delete the forth row.。
下面是我的资料。
++++++++++++++++++++++++++
+ company_id + industry +
++++++++++++++++++++++++++
+ 1 + a +
+ 1 + b +
+ 1 + c +
+ 2 + a +
+ 2 + c +
+ 3 + a +
+ 4 + c +
+ 5 + a +
++++++++++++++++++++++++++正如我们在专栏行业中看到的，a有最大计数，我想保留这个条目每个重复的company_id，并删除其余的所有企业。
考虑company_id=1，我需要删除第二行和第三行。以company_id=2为例，我需要删除第五行。对于id=3,4,5来说，不会发生任何事情，因为它们不会被复制。
因此，我的表中的最终数据应该是
++++++++++++++++++++++++++
+ company_id + industry +
++++++++++++++++++++++++++
+ 1 + a +
+ 2 + a +
+ 3 + a +
+ 4 + c +
+ 5 + a +
++++++++++++++++++++++++++回答 3
Stack Overflow用户
回答已采纳
发布于 2013-02-21 08:34:21
select t6.company_id,t6.industry from
(select t5.company_id,t5.industry,
row_number() over (partition by t5.company_id order by t5.company_id) rn
from
(select t3.company_id,t4.industry from
(select t2.company_id,max(t2.count) count from(
select m.company_id,m.industry,t1.count from linkage m
join
(select n.industry,count(n.industry) count from linkage n
group by n.industry
order by count desc)t1
on m.industry = t1.industry
order by m.company_id)t2
group by t2.company_id
order by t2.company_id)t3
join
(
select m.company_id,m.industry,t1.count from linkage m
join
(select n.industry,count(n.industry) count from linkage n
group by n.industry
order by count desc)t1
on m.industry = t1.industry
order by m.company_id)t4
on t3.company_id = t4.company_id
and t3.count = t4.count)t5
)t6
where t6.rn = '1'Stack Overflow用户
发布于 2013-02-21 06:54:01
这个怎么样？
SELECT industry, count(industry) as "total"
FROM linkage
GROUP BY industry
ORDER BY total DESC在广场小提琴演示
编辑1
你能看看下面的问题吗？
我想这就是你要找的。
Stack Overflow用户
发布于 2013-02-21 07:35:18
select n.industry,count(n.industry) count from linkage n
group by n.industry
order by count descselect t3.company_id,t4.industry from
(select t2.company_id,max(t2.count) count from(
select m.company_id,m.industry,t1.count from linkage m
join
(select n.industry,count(n.industry) count from linkage n
group by n.industry
order by count desc)t1
on m.industry = t1.industry
order by m.company_id)t2
group by t2.company_id
order by t2.company_id)t3
join
(
select m.company_id,m.industry,t1.count from linkage m
join
(select n.industry,count(n.industry) count from linkage n
group by n.industry
order by count desc)t1
on m.industry = t1.industry
order by m.company_id)t4
on t3.company_id = t4.company_id
and t3.count = t4.count在广场小提琴演示
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14996202
复制相关文章
相似问题
腾讯云开发者
