MySQL查询返回带有in和find_int_set的重复条目 [英] Mysql query return duplicate entries with in and find_int_set

问题描述

我有一个mysql查询，显示用户上次查看的文档.某种历史记录视图，我想在历史记录"选项卡中显示它，但是我无法使其正常工作.

I have a mysql query that shows the users last viewed documents. Some sort of history view, and I want to show this in a history tab, but I cannot get this to work.

历史记录保存在另一台服务器上，因此我要获取ID列表，并且必须从文档数据库中获取文档信息.我希望它在历史记录中显示重复的条目(当前不起作用)

The history is saved on another server so I'm getting a list of ID's, and I have to get the document's info from the document database. I want it to show duplicate entries in the history (Which is currently not working)

我已经尝试了多种方法来使它正常工作，而我所得到的只是获得ID字段上使用DISTINCT选择的最后25个文档，但是我也想获取重复的文档.

I've tried multiple things to get it to work, and all I got is get the last 25 documents which are selected with a DISTINCT on the id field, but I also want to get duplicates.

这是我正在使用的查询(已填充示例数据)

This is the query I'm using (Filled with example data)

SELECT 
    stuff_from_table
FROM 
    a_secret_table
WHERE 
    `id`
IN ( 6176, 6176, 6176, 3091, 3454, 5927, 5929, 5938, 1975, 177, 6123, 5943, 4325, 4326, 4327, 6176, 4327, 1990, 3400, 2650, 2649, 2649, 6176, 4297, 6145 ) 
ORDER BY FIND_IN_SET( id, '6176, 6176, 6176, 3091, 3454, 5927, 5929, 5938, 1975, 177, 6123, 5943, 4325, 4326, 4327, 6176, 4327, 1990, 3400, 2650, 2649, 2649, 6176, 4297, 6145') 
LIMIT 0 , 25

此代码有效，但不会返回任何重复项.它只会像17个结果一样返回，因为它会忽略重复项.

This code works, but will not return any duplicates. It will only return like 17 results, because it ignores the duplicates.

任何人都知道如何获取此查询以还返回重复项吗?还是我必须在php中手动添加缺少的内容?

Anyone knows how to get this query to also return duplicates? Or do I have to manually add the missing ones in php?

提前谢谢！

推荐答案

除了使用IN子句外，您还需要加入id值. IE浏览器:

Rather than use an IN clause, you need to join on the id values. IE:

  SELECT t.stuff
    FROM YOUR_TABLE t
    JOIN (SELECT 6176 AS tid,
          UNION ALL
          SELECT 6176
          UNION ALL
          SELECT 6176
          UNION ALL
          SELECT 3091) x ON x.tid = t.id
ORDER BY FIND_IN_SET(t.id, '6176, 6176, 6176, 3091') 
   LIMIT 25

您需要将该逗号分隔的列表拆分为一个具有单个列的表-请参见:

You need to split that comma separated list into a table with a single column - see: http://forge.mysql.com/tools/tool.php?id=4

这篇关于MySQL查询返回带有in和find_int_set的重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！