如何创建一个选择查询，从多个行中仅选择一个colums中的重复值（示例内部）(How to create a select query that selects a repeating value i

如何创建一个选择查询，从多个行中仅选择一个colums中的重复值（示例内部）(How to create a select query that selects a repeating value in one of colums only once from multiple rows(example inside))

这就是我所拥有的（这是不同条件下来自不同表的select查询的结果，我们称之为Select X）：

ID c2 c3 c4 1 A de ys 2 A rq pm 3 B rq qs

我想要一个只能在c2中给出一个具有特定值的行的查询，例如：

ID c2 c3 c4 1 A de ys 3 B rq qs

要么

ID c2 c3 c4 2 A rq pm 3 B rq qs

我知道如何使用数据结构，但它更复杂，我想知道是否有办法使用查询？

编辑：这是我尝试过的，它给出了错误：“From子句中的语法错误”（如果重要X = SELECT Responses.ID,Responses.PostID,Responses.Responding,Posts.ID,Posts.UserID,Users.ID,Users.Username FROM Responses,Posts,Users WHERE Responses.PostID =3 and Posts.ID = Responses.PostID AND Responses.Responding = Users.ID ）

SELECT(Select X).* FROM (Select X) as (Select X) JOIN ( SELECT MIN(ID) AS ID, Users.ID FROM (Select X) GROUP BY Users.ID ) AS t2 ON (Select X).ID = t2.ID

编辑：好吧我会保持开放，但最后我选择的数据结构方式不是查询方式。

This is for example what I have(This is the result of a select query from different tables by different conditions let's call it Select X) :

ID c2 c3 c4 1 A de ys 2 A rq pm 3 B rq qs

I want a query that will give only one row with a certain value in c2, for example:

ID c2 c3 c4 1 A de ys 3 B rq qs

or

ID c2 c3 c4 2 A rq pm 3 B rq qs

I know how to do that using a data structure but it's more complicated, I wonder if there is a way to do that using a query?

Edit : This is what I tried and it gives error : " Syntax error in From clause " ( If it matters X = SELECT Responses.ID,Responses.PostID,Responses.Responding,Posts.ID,Posts.UserID,Users.ID,Users.Username FROM Responses,Posts,Users WHERE Responses.PostID =3 and Posts.ID = Responses.PostID AND Responses.Responding = Users.ID)

SELECT(Select X).* FROM (Select X) as (Select X) JOIN ( SELECT MIN(ID) AS ID, Users.ID FROM (Select X) GROUP BY Users.ID ) AS t2 ON (Select X).ID = t2.ID

EDIT : well I'll leave this open but at the end I chosen the data structure way not the query way.

最满意答案

像这样的东西：

SELECT t1.* FROM mytable as t1 JOIN ( SELECT MIN(ID) AS ID, c2 FROM mytable GROUP BY c2 ) AS t2 ON t1.ID = t2.ID

这将选择具有相同c2值的组内的最小ID值的行。 您可以使用MAX聚合函数来获取第二个结果集。

注意：我假设ID是你桌子的PK。

编辑：

您可以使用SELECT查询中的派生表，如下所示：

SELECT t1.* FROM (SELECT X ...) as t1 JOIN ( SELECT MIN(ID) AS ID, c2 FROM (SELECT X ...) AS t GROUP BY c2 ) AS t2 ON t1.ID = t2.ID

Something like this:

SELECT t1.* FROM mytable as t1 JOIN ( SELECT MIN(ID) AS ID, c2 FROM mytable GROUP BY c2 ) AS t2 ON t1.ID = t2.ID

This will pick the row having the minimum ID value within a group of same c2 values. You can use MAX aggregate function to get the second result set.

Note: I've made the assumption that ID is the PK of your table.

Edit:

You can use a derived table from you SELECT query like this:

SELECT t1.* FROM (SELECT X ...) as t1 JOIN ( SELECT MIN(ID) AS ID, c2 FROM (SELECT X ...) AS t GROUP BY c2 ) AS t2 ON t1.ID = t2.ID