具有唯一列值的Sql查询 [英] Sql Query with unique column value

问题描述

我的目标是编写查询.我有三个表，A、B 和 C.这些表是这样写的，A.bID = B.bID，B.cID = C.cID.这基本上允许我编写一个查询，其中我将一条记录从 a 链接到 b，并将 b 记录链接到来自 c 的记录.到目前为止，一切都很好，简单的查询.

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.

我的问题是……查询中包含的列之一(我们称之为 C.col3)必须具有唯一值；该列的值在查询结果中只能出现一次，其他表的其他列没有这个要求.

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.

有人可以帮我写这个查询吗?

Can anybody help me write this query?

谢谢...

更新 1:

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

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

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

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

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

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

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

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

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

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

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

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

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

更新 2:更多信息...
表A和表B是一对多的关系；A 是标题"，B 是项目".
表 B 和表 C 是一对一的关系.

这些表是缓存机制的一部分，因此很多数据看起来相似，但在某些列中仍然不同.

由于 A 是标题，因此大部分重复值都会在 A 中找到.

我首先需要按 A.aID 对行进行排序，但之后我只需要返回第一行，其中 C.col3 的值不会出现在该列的前一行中.

这是否让事情变得更清楚了，还是我仍然没有任何意义?:)

最终更新:

我选择了 Bartosz Klimek 的答案，因为它最接近我所需要的；我只需要修改中间的嵌套连接子句.

谢谢大家的帮助！

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!

推荐答案

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

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

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.

这篇关于具有唯一列值的Sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！