如何使用两个内部联接修改此查询，以使其停止给出重复的结果?

2022-01-23 00:00:00 group-by subquery mysql inner-join

我将把帖子原样留在这里，但我真正需要完成的工作需要重新发布.我没有很好地解释这个问题.在以完全不同的起点再次尝试后，我能够获得所需的查询.此处对此进行了解释.

I will leave the post here as is, but what I really needed to accomplish needed to be reposted. I didn't explain the problem well enough. After trying again with quite a different starting point, I was able to get the query that I needed. That is explained here.

原始问题:我有麻烦了.我查看了类似的线程，但找不到特定于该查询的解决方案.数据库非常大，group by 似乎大大减慢了它.

ORIGINAL QUESTION: I'm having trouble. I have looked at similar threads, and I am unable to find a solution specific to this query. The database is very large, and group by seems to slow it down immensely.

问题是我得到了重复的结果.这是我的查询导致重复:

The problem is I am getting duplicate results. Here is my query which causes duplicates:

SELECT 
  itpitems.identifier, 
  itpitems.name, 
  itpitems.subtitle, 
  itpitems.description, 
  itpitems.itemimg, 
  itpitems.mainprice, 
  itpitems.upc, 
  itpitems.isbn, 
  itpitems.weight, 
  itpitems.pages, 
  itpitems.publisher, 
  itpitems.medium_abbr, 
  itpitems.medium_desc, 
  itpitems.series_abbr, 
  itpitems.series_desc, 
  itpitems.voicing_desc,
  itpitems.pianolevel_desc,
  itpitems.bandgrade_desc,
  itpitems.category_code,
  itprank.overall_ranking,
  itpitnam.name AS artist,
  itpitnam.type_code 
FROM itpitems 
  INNER JOIN itprank ON ( itprank.item_number = itpitems.identifier ) 
  INNER JOIN itpitnam ON ( itpitems.identifier = itpitnam.item_number ) 
WHERE mainprice >1

结果实际上并不完全重复.itpitnam.type_code 在其他重复的结果中有不同的结果.

The results are actually not complete duplicates. itpitnam.type_code has a different result in the otherwise duplicated results.

由于在查询末尾添加 GROUP BY 会对服务器造成太大压力(它正在搜索大约 300,000 条记录)，我还能做什么?

Since adding GROUP BY to the end of the query is causing too much strain on the server (It's searching through about 300,000 records) what else can I do?

这可以重写为子查询吗?我只是不知道如何消除 type_code 已更改的第二个实例.

Can this be re-written as a sub-query? I just can't figure out how to eliminate the 2nd instances where type_code has changed.

感谢您的帮助和帮助.

我也尝试了 SELECT DISTINCT itpitems.identifier，但这提供了相同的结果并且有重复项(其中 type_code 是唯一的区别).我不想要 type_code 已更改的第二个实例.无论 type_code 是否有多个实例，我只想要每个标识符一个结果.

I also tried SELECT DISTINCT itpitems.identifier, but this served out the same results and had the duplicates (where type_code was the only difference). I don't want the second instance where type_code has changed. I just want one result per identifier regardless of whether or not type_code has multiple instances.

推荐答案

没有看到输出的例子，很难说.但是您是否尝试过将简单的 DISTINCT 添加到 SELECT 的相同查询?

Without seeing examples of the output, hard to say. But have you tried the same exact query with a simple DISTINCT added to the SELECT?

SELECT DISTINCT itpitems.identifier, itpitems.name, itpitems.subtitle, itpitems.description, itpitems.itemimg, itpitems.mainprice, itpitems.upc, itpitems.isbn, itpitems.weight, itpitems.pages, itpitems.publisher, itpitems.medium_abbr, itpitems.medium_desc, itpitems.series_abbr, itpitems.series_desc, itpitems.voicing_desc, itpitems.pianolevel_desc, itpitems.bandgrade_desc, itpitems.category_code, itprank.overall_ranking, itpitnam.name AS artist, itpitnam.type_code 
FROM itpitems 
INNER JOIN itprank ON ( itprank.item_number = itpitems.identifier ) 
INNER JOIN itpitnam ON ( itpitems.identifier = itpitnam.item_number ) 
WHERE mainprice >1

相关文章