具有唯一列值的Sql Query

My goal is to get a query written. I have three tables, A, B and C. The tables are written such that A.bID = B.bID, and B.cID = C.cID. This basically allows me to write a query where I link a record from a to b, and link the b record to a record from c. So far so good, simple query.

我的目标是编写一个查询。我有三个表,A,B和C.这些表的编写使得A.bID = B.bID,B.cID = C.cID。这基本上允许我编写一个查询,其中我将记录从a链接到b,并将b记录链接到c中的记录。到目前为止这么好,简单的查询。

What my problem is... one of the columns included in the query (let's call it C.col3) has to have unique values; the values in this column can only show up once in the query result, but other columns from the other tables do not have this requirement.

我的问题是什么......查询中包含的一个列(我们称之为C.col3)必须具有唯一值;此列中的值只能在查询结果中显示一次,但其他表中的其他列不具有此要求。

Can anybody help me write this query?

任何人都可以帮我写这个查询吗?

Thanks...

Update 1:

Here is the table layout (sorry, I have to use generic names)

这是表格布局(抱歉,我必须使用通用名称)

Table A
aID, bID, aCol1, aCol2, aCol3 ... aCol10

表A aID,bID,aCol1,aCol2,aCol3 ... aCol10

Table B
bID, cID, bCol1, bCol2, bCol3 ... bCol10

表B bID,cID,bCol1,bCol2,bCol3 ... bCol10

Table C
cID, cCol1, cCol2, col3, cCol4 ... cCol10

表C cID,cCol1,cCol2,col3,cCol4 ... cCol10

Without the unique value constraint in col3, I would write the query like this:

如果没有col3中的唯一值约束,我会像这样编写查询:

SELECT
    A.aID, A.bID, A.aCol1 ... A.aCol10,
    B.bID, B.cID, B.bCol1 ... B.bCol10,
    C.cID, C.cCol1, C.cCol2, C.col3 ... C.cCol10
FROM
    A, B, C
WHERE 
    A.bID = B.bID AND B.cID = C.cID

... but of course that doesn't make sure the that values in C.col3 are unique.

...但当然这并不能确保C.col3中的值是唯一的。

Update 2: More info...
Table A and Table B have a one to many relationship; A is the "header", B is the "item".
Table B and Table C have a one to one relationship.

These tables are part of a caching mechanism, so lots of data that looks similar, but is still different in some cols.

Since A is the header, most of the duplicate values will be found in A.

I first need to order the rows by A.aID, but then after that I only need the first rows returned, where the value for C.col3 does not appear in a previous row for that col.

Does that make things a little clearer, or am I still not making any sense? :)

Final Update:

I chose Bartosz Klimek's answer as it was the closest to what I needed; I just had to modify the nested join clause in the middle.

Thank you all for your help!

更新2:更多信息...表A和表B有一对多的关系; A是“标题”,B是“项目”。表B和表C具有一对一的关系。这些表是缓存机制的一部分,因此大量数据看起来相似,但在某些cols中仍然不同。由于A是标题,因此大多数重复值都将在A中找到。我首先需要按A.aID对行进行排序,但之后我只需要返回第一行,其中C.col3的值不是出现在该col的前一行中。这会让事情变得更清楚,还是我还没有任何意义? :)最后更新:我选择了Bartosz Klimek的答案,因为它最接近我的需要;我只需要修改中间的嵌套连接子句。感谢大家的帮助!

5 个解决方案

#1

SELECT A.*, B.*, C.*
  FROM C
    JOIN B ON B.cID = C.cID
    JOIN A ON A.bID = B.bID
    JOIN
    (
      SELECT id = min(aID)
        FROM C
          JOIN B ON B.cID = C.cID
          JOIN A ON A.bID = B.bID
        GROUP BY col3
    ) D ON D.id = A.aID

Note that the subquery at the end assures that for each col3 value you will have at most one record in the final resultset. The record selected is the one with the minimal aID. Obviously I assume that aID, bID and cID are primary keys of A, B and C, respectively.

请注意,最后的子查询确保对于每个col3值,最终结果集中最多只有一条记录。选择的记录是具有最小aID的记录。显然,我假设aID,bID和cID分别是A,B和C的主键。

#2

I'm going to quickly make a little example of what you're trying to do and hopefully this will help clarify why what you are asking (currently) is impossible.

我将快速举一个你想要做的事情的例子,希望这将有助于澄清为什么你所要求的(目前)是不可能的。

If you had a Customer Table [CustomerID, CustomerName] and an Orders Table [OrderID, CustomerID, DollarAmount]

如果您有客户表[CustomerID,CustomerName]和订单表[OrderID,CustomerID,DollarAmount]

If you wanted all orders for customers:

如果您想要客户的所有订单:

SELECT CustomerName, OrderID, DollarAmount
FROM Customer, Orders
WHERE Customer.CustomerID = Orders.CustomerID

it would return

它会回来

 "Acme Corp.", 1, $2300
 "Acme Corp.", 2, $3022
 "A company",  3, $1234

Everything is good.

一切都是好的。

But the equivalent of your question is asking for this query, but with unique CustomerNames. What would you display for OrderID and DollarAmount beside "Acme Corp"?

但是,相当于您的问题是要求此查询,但使用唯一的CustomerNames。你会在“Acme Corp”旁边为OrderID和DollarAmount展示什么?

You could use aggregates to display something,

您可以使用聚合来显示某些内容,

SELECT CustomerName, MAX(OrderID), SUM(DollarAmount)
FROM Customer, Orders
WHERE Customer.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID

But I believe that you mentioned that you do not want to use aggregates.

但我相信你提到你不想使用聚合。

Does this explain the issue clearly?

这是否清楚地解释了这个问题?

#3

I started to post another answer, but after rethinking it I deleted it. If I am reading the question correctly, I think this is an impossible/illogical question. Let me explain with an example. if I read this wrong, please clarify the question with an exampe of what you are looking for.

我开始发布另一个答案,但在重新思考之后我删除了它。如果我正确地阅读这个问题,我认为这是一个不可能/不合逻辑的问题。让我举个例子来解释一下。如果我读错了,请说明您要查找的问题。

Table A

BID COL1
1   Value1
2   Value1
3   Value2

Table B

BID CID COL 2
1   4   ValueX
2   5   ValueY
3   6   ValueZ

Table C

CID COL3
4   Value#
5   Value@
6   Value~

Expected Result

A.Col1   A.BID B.BID B.CID B.COL2 C.CID C.COL3
Value1?? 1     1     4     ValueX 4     Value#
Value1?? 2     1     5     ValueY 5     Value@ 
Value2   3     3     6     ValyeZ 6     Value~

Per the question you don't want value1 repeated in the first column, but what do you propose goes into the second row where it would normally be repeated if you didn't have the unique constraint?

根据你不想在第一列中重复value1的问题,但你建议进入第二行,如果没有唯一约束,它通常会重复?

#4

select distinct c.col3 from c inner join b on c.cID = b.cID inner join a on b.bID = a.bID

#5

If you need other values from the tables as well, use:

如果您还需要表中的其他值,请使用:

select max(a.col1), sum(b.col2), col3 from a, b, c
where A.bID = B.bID, and B.cID = C.cID
group by C.col3

On all columns not grouped by you need to use aggregate functions such as

在未按您分组的所有列上,需要使用聚合函数,例如

• AVG: Average of the column.

AVG:列的平均值。

• COUNT: Number of records.

COUNT:记录数。

• MAX: Maximum of the column.

MAX:列的最大值。

• MIN: Minimum of the column.

MIN:列的最小值。

• SUM: Sum of the column.

SUM:列的总和。