MySQL为多列选择语句DISTINCT

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).

基本上,我想要做的是能够SELECT / GROUP BY stringID(stringID是线程化的图片)而不是重复。此外,我想选择最近的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.

我希望这是有道理的。谢谢你的帮助。

5 个解决方案

#1

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

#2

SELECT DISTINCT(a),
  ( SELECT DISTINCT(b) ) AS b,
  ( SELECT DISTINCT(c) ) AS c

FROM tblMyTBL

WHERE...
Order By...
Etc.

#3

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 uniqueID value 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_table where there exists no higher uniqueID value.

这基本上意味着:只返回data_table中那些没有更高的uniqueID值的记录。

#4

I had a similar situation and found a different query. Try this:

我有类似的情况,发现了不同的查询。尝试这个:

SELECT MAX(uniqueID), stringID, subject
 FROM data_table
 GROUP BY stringID

#5

-1

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);
        }

#1