SQL JOIN中的重复记录 [英] duplicate records in SQL JOIN

问题描述

我的SQL查询中得到多个结果.我有三个表:付款，贷项和费用.在每个月的月底，我需要根据帐户持有者(我们称其为所有者")来结转余额，我尝试了不同的查询，但是不断得到重复的行.我知道它与我的WHERE子句有关，但是我不知道如何解决它.

I am getting multiple results in my SQL query. I have three tables: payments, credits, and charges. At the end of each month I need to have a balance forward based on the account holder (let's call said person owner) I have tried different queries but keep getting duplicate rows. I know it has to do with my WHERE clause but I don't know how to fix it.

在我的测试数据中，我有一条关于payments的记录:

In my test data I have one record for payments:

owner:1001 date:2014-03-23 amount:50.00

owner: 1001 date:2014-03-23 amount:50.00

我从查询中返回了该记录的3条记录:

I get 3 of that record returned from the query:

SELECT p.amount
FROM payments p, charges ch, credits cr
WHERE (p.owner = 1001 AND ch.owner = 1001 AND cr.owner = 1001)

我知道这是不对的，但是我不知道查询它的正确方法.我只想返回一行.最终，我将在每个表的amount上使用SUM()并将日期添加到WHERE子句中.

I know that isn't right, but I do not know the proper way to query it. I want just the one row returned. Eventually I will use a SUM() on the amount from each table and add a dates to the WHERE clause.

不过，首先，我需要解决此问题.感谢您的帮助.

First, though, I need to get this resolved. Thanks for any help.

我对表做了 SQL提琴.名称不太通用.我实际上正在使用unit，而不是owner.虽然真的没关系.

I have made a SQL Fiddle of the tables. The names are less generic. I am actually using unit, not owner. Though it really wouldn't matter.

推荐答案

尝试这个

SELECT p.amount
FROM payments p, charges ch, credits cr
WHERE (p.owner = 1001 AND ch.owner = 1001 AND cr.owner = 1001 AND p.owner=ch.owner AND
p.owner=cr.owner
)

这篇关于SQL JOIN中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！