mysql - 如何使用MySQL语句选择重复记录并保留数据？

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

例如，我的表 categories是这样的:

categories_id                    categories_name
----------------------         -------------------- 
1                                  aaa               
2                                  bbb               
3                                  ccc                 
5                                  ddd             
9                                  eee

我使用以下语句来保留重复记录。但重复的记录不会出现。

SELECT categories_id, categories_name 
FROM categories
WHERE categories_name in ("aaa","bbb","bbb","ccc","ccc","ddd","ddd","ddd")
ORDER BY categories_name

我想获取这样的记录:

categories_id                    categories_name
----------------------         -------------------- 
1                                  aaa               
2                                  bbb
2                                  bbb               
3                                  ccc
3                                  ccc                 
5                                  ddd 
5                                  ddd            
5                                  ddd

有人可以帮助我吗？

Answer 1

以下代码给出所需的输出:

SELECT Tab1.categories_id, Tab1.categories_name
FROM categories AS Tab1
RIGHT JOIN
(
   SELECT "aaa" AS categories_name
   UNION ALL
   SELECT "bbb" 
   UNION ALL
   SELECT "bbb"
   UNION ALL
   SELECT "ccc"
   UNION ALL
   SELECT "ccc"
   UNION ALL
   SELECT "ddd"
   UNION ALL
   SELECT "ddd"
   UNION ALL
   SELECT "ddd"
) AS Tab2 
ON Tab1.categories_name = Tab2.categories_name
ORDER BY Tab1.categories_name;

安SQL Fiddle .