SQL 查询返回虚假的重复结果? [英] SQL Query returns fake duplicate results?

问题描述

我一直在尝试编写一些小插件供个人使用 WHMCS.本质上，我在这里要做的是获取有关某个订单的一堆信息，并将其作为 Perl 中的数组返回.

I've been trying to write a few little plugins for personal use with WHMCS. Essentially what I'm trying to do here is grab a bunch of information about a certain order(s), and return it as an array in Perl.

Perl 部分我很好，这是我形成的 MySQL 查询让我感到压力..

The Perl bit I'm fine with, it's the MySQL query I've formed that's giving me stress..

我知道它又大又乱，但我拥有的是:

I know it's big and messy, but what I have is:

SELECT tblhosting.id, tblhosting.userid, tblhosting.orderid, tblhosting.packageid,   tblhosting.server, tblhosting.domain, tblhosting.username, tblorders.invoiceid, tblproducts.gid, tblservers.ipaddress, tblinvoices.status
    FROM tblhosting, tblproducts, tblorders, tblinvoices, tblservers
  WHERE tblorders.status = 'Pending'
  AND tblproducts.gid = '2'
  AND tblservers.id = tblhosting.server
  AND tblorders.id = tblhosting.orderid
  AND tblinvoices.id = tblorders.invoiceid
  AND tblinvoices.status = 'Paid'

我不知道这是否/应该/工作，但我认为我在正确的轨道上，因为它确实返回了我正在寻找的东西，但是它返回了两次.

I don't know if this /should/ work, but I assume I'm on the right track as it does return what I'm looking for, however it returns everything twice.

例如，我使用域sunshineee.info"创建了一个新帐户，然后在 PHPMyAdmin 中运行了上述查询.

For example, I created a new account with the domain 'sunshineee.info', and then in PHPMyAdmin ran the above query.

id  userid  orderid     packageid   server  domain  username    invoiceid   gid     ipaddress   status
13  7   17  6   1   sunshineee.info     sunshine    293     2   184.22.145.196  Paid
13  7   17  6   1   sunshineee.info     sunshine    293     2   184.22.145.196  Paid

谁能告诉我我在这个问题上哪里出错了..显然(可能不够明显)我想要这个，因为每场比赛只返回一行..我已经尝试过>1个域在数据库中，它返回每个匹配项的重复项..

Could anyone give me a heads up on where I've gone wrong with this one.. Obvioiusly (maybe not obviously enough) I want this as only one row returned per match.. I've tried it with >1 domain in the database and it returned duplicates for each of the matches..

任何帮助将不胜感激:)

Any help would be much appreciated :)

推荐答案

好吧，如果没有任何表定义，这几乎是不可能的，但是您在那里进行了很多连接.您从 tblhosting.id 开始并从那里向上"工作.如果任何连接的表有双重条目，您将获得更多点击

Well, its near impossible without any table definitions, but you are doing a lot of joins there. You are starting with tblhosting.id and working your way 'up' from there. If any of the connected tables has a double entry, you'll get more hits

您可以在查询中添加 DISTINCT，但这并不能解决根本问题.您的数据可能有问题:您有 2 张发票吗?也许您应该选择所有内容(SELECT * FROM)并检查返回的内容，或者检查您的表格是否存在双重内容.

You could add a DISTINCT to your query, but that would not fix the underlying issue. It could be a problem with your data: do you have 2 invoices? Maybe you should select everything (SELECT * FROM) and check what is returned, maybe check your tables for double content.

使用 DISTINCT 在大多数情况下不是一个好的选择:这意味着您的查询或您的数据不正确(或者您没有彻底理解它们).它现在可能会给您带来正确的结果，但可能会在以后给您带来麻烦.

Using DISTINCT is most of the time not a good choice: it means either your query or your data is incorrect (or you don't understand them thoroughly). It might get you the right result for now, but can get you in trouble later.

猜测发生这种情况的原因:您没有将产品表连接到 id 链.因此，据我所知，您基本上是在结果中添加了2".您加入产品，唯一限制该表的是gid"应为 2.因此，如果您添加 gid 为 2 的产品，则会得到另一个结果.要么加入它(也许 tblproduct.orderid = tblorders.id ?只是在这里猜测)或者只是删除它，因为据我所知它什么都不做.

A guess about the reason this happens: You do not connect the products table to the chain of id's. So you are basically adding a '2' to your result as far as I can see. You join on products, and the only thing that limits that table is that "gid" should be 2. So if you add a product with gid 2 you get another result. Either join it (maybe tblproduct.orderid = tblorders.id ? just guessing here) or just remove it, as it does nothing as far as I can see.

如果你想让你的查询更清楚一点，不要尝试隐式加入，而是这样做.所以你实际上可以看到发生了什么

If you want to make your query a bit clearer, try not implicitly joining, but do it like this. So you can actually see what's happening

SELECT tblhosting.id, tblhosting.userid, tblhosting.orderid, tblhosting.packageid,   tblhosting.server, tblhosting.domain, tblhosting.username, tblorders.invoiceid, tblproducts.gid, tblservers.ipaddress, tblinvoices.status
FROM tblhosting
  JOIN tblproducts ON /*you're missing something here!*/
  JOIN tblorders ON tblorders.id = tblhosting.orderid
  JOIN tblinvoices ON tblinvoices.id = tblorders.invoiceid
  JOIN tblservers ON tblservers.id = tblhosting.server
WHERE 
  tblorders.status = 'Pending'
  AND tblproducts.gid = '2'
  AND tblinvoices.status = 'Paid'

这篇关于SQL 查询返回虚假的重复结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！