多列的 MySQL Select 语句 DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1181374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Select Statement DISTINCT for Multiple Columns
提问by Andrew Moore
I am currently trying to construct a somewhat tricky MySQL Select Statement. Here is what I am trying to accomplish:
我目前正在尝试构建一个有点棘手的 MySQL Select 语句。这是我想要完成的：
I have a table like this:
我有一张这样的表：
data_table
uniqueID stringID subject
1 144 "My Subject"
2 144 "My Subject - New"
3 144 "My Subject - Newest"
4 211 "Some other column"
Bascially, what I'd like to do is be able to SELECT/GROUP BY the stringID (picture that the stringID is threaded) and not have it duplicated. Furthermore, I'd like to SELECT the most recent stringID row, (which in the example above is uniqueID 3).
基本上，我想要做的是能够按 stringID（字符串 ID 被线程化的图片）进行 SELECT/GROUP 并且不会重复它。此外，我想选择最近的 stringID 行（在上面的示例中是 uniqueID 3）。
Therefore, if I were to query the database, it would return the following (with the most recent uniqueID at the top):
因此，如果我要查询数据库，它将返回以下内容（最新的 uniqueID 位于顶部）：
uniqueID stringID subject
4 211 "Some other column"
3 144 "My Subject - Newest" //Notice this is the most recent and distinct stringID row, with the proper subject column.
I hope this makes sense. Thank you for you help.
我希望这是有道理的。谢谢你的帮助。
回答by Andrew Moore
Try the following. It might not be the most efficient query, but it will work:
请尝试以下操作。它可能不是最有效的查询，但它会起作用：
SELECT uniqueID, stringID, subject
FROM data_table
WHERE uniqueID IN
(
SELECT MAX(uniqueID)
FROM data_table
GROUP BY stringID
)
ORDER BY uniqueID DESC
回答by Bill Frederickson
SELECT DISTINCT(a),
( SELECT DISTINCT(b) ) AS b,
( SELECT DISTINCT(c) ) AS c
FROM tblMyTBL
WHERE...
Order By...
Etc.
回答by mechanical_meat
Edit:Based on new info provided by the OP in a comment, this would be preferable to relying on uniqueID:
编辑：根据 OP 在评论中提供的新信息，这比依赖uniqueID：
select t.uniqueID
, t.stringID
, t.subject
, t.your_timestamp_col
from data_table t
left outer join data_table t2
on t.stringID = t2.stringID
and
t2.your_timestamp_col > t.your_timestamp_col
where t2.uniqueID is null
If, as lexu mentions in a comment, you are certain that the highest uniqueIDvalue always corresponds with the newest subject, you could do this:
如果，正如 lexu 在评论中提到的，您确定最高uniqueID值始终与最新主题相对应，您可以这样做：
select t.uniqueID
, t.stringID
, t.subject
from data_table t
left outer join data_table t2
on t.stringID = t2.stringID
and
t2.uniqueID > t.uniqueID
where t2.uniqueID is null
Which basically means: return to me only those records from data_tablewhere there exists no higher uniqueIDvalue.
这基本上意味着：只返回那些data_table不存在更高uniqueID价值的记录。
回答by WebMuse
I had a similar situation and found a different query. Try this:
我遇到了类似的情况，发现了不同的查询。尝试这个：
SELECT MAX(uniqueID), stringID, subject
FROM data_table
GROUP BY stringID
回答by dino
private void LoadAllFamilyMembers(string relationShip)
{
lbFamilyMembers.SelectedIndexChanged -= new EventHandler(lbFamilyMembers_SelectedIndexChanged);
SqlCommand cmd = new SqlCommand("select familymemberid,name from FamilyMembers where relationship = @relationship", con);
cmd.Parameters.AddWithValue("@relationship", relationShip);
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
lbFamilyMembers.DataSource = dt;
lbFamilyMembers.DisplayMember = "name";
lbFamilyMembers.ValueMember = "familymemberid";
lbFamilyMembers.SelectedIndex = -1;
lbFamilyMembers.SelectedIndexChanged += new EventHandler(lbFamilyMembers_SelectedIndexChanged);
}
