我怎样才能修改这个查询与两个内部联接，以便它停止重复结果？ [英] How can I modify this query with two Inner Joins so that it stops giving duplicate results?

问题描述

编辑：我会按照原样离开帖子，但我真正需要完成的任务需要重新发布。我没有很好地解释这个问题。在以不同的起点重新尝试之后，我能够获得我需要的查询。这是这里的解释。

原始问题：
我遇到问题了。我看过类似的线程，我无法找到特定于此查询的解决方案。数据库非常大，而group by似乎非常缓慢。

问题是我得到重复的结果。这是我的查询导致重复：

  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艺术家，
 itpitnam.type_code 
从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在其他重复结果中有不同的结果。

由于将GROUP BY添加到查询的末尾导致服务器过于紧张（它正在搜索约30万条记录）我还能做什么？

这可以重写为子查询吗？我只是无法弄清楚如何消除type_code已经改变的第二个实例。

感谢您的帮助和帮助。

我也试过SELECT DISTINCT itpitems.identifier，出了相同的结果，并有重复（其中type_code是唯一的区别）。我不希望type_code发生更改的第二个实例。我只想为每个标识符提供一个结果，而不管type_code是否有多个实例。

解决方案

没有看到输出的例子，很难说。但是，您是否尝试过使用简单的 DISTINCT 添加到 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.ategorygrade，itprank.overall_ranking，itpnamnam .name AS艺术家，itpitnam.type_code 
从itpitems 
 INNER JOIN itprank ON（itprank.item_number = itpitems.identifier）
 INNER JOIN itpitnam ON（itpitems.identifier = itpitnam.item_number）
 WHERE mainprice> 1 
  

EDIT: 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.

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

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

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?

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.

Thank you for your help and assistance.

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.

解决方案

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

这篇关于我怎样才能修改这个查询与两个内部联接，以便它停止重复结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！