MySQL 在每个唯一值第一次出现时选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12065931/
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 rows on first occurrence of each unique value
提问by TPoy
Let's say you have the following table (the column of interest here is cid):
假设您有下表（此处感兴趣的列是cid）：
+-----+-------+-------+-------+---------------------+--------------+
| cid | pid | rid | clink | time | snippet |
+-----+-------+-------+-------+---------------------+--------------+
| 155 | 11222 | 1499 | 1137 | 2012-08-22 03:05:06 | hi |
| 138 | 11222 | 241 | 1136 | 2012-08-21 05:25:00 | again |
| 138 | 11222 | 241 | 1135 | 2012-08-21 05:16:40 | hi |
| 155 | 11222 | 1499 | 1134 | 2012-08-21 05:11:00 | hi cute |
| 140 | 11222 | 11223 | 1133 | 2012-08-21 05:05:18 | hi |
| 154 | 11222 | 565 | 1132 | 2012-08-21 05:04:47 | 7 |
| 153 | 11222 | 272 | 1131 | 2012-08-21 05:04:41 | 6 |
| 146 | 11222 | 362 | 1130 | 2012-08-21 05:04:33 | 5 |
| 152 | 11222 | 364 | 1129 | 2012-08-21 05:04:27 | 4 |
| 151 | 11222 | 390 | 1128 | 2012-08-21 05:04:22 | 3 |
| 150 | 11222 | 333 | 1127 | 2012-08-21 05:04:16 | 2 |
| 148 | 11222 | 268 | 1126 | 2012-08-21 05:04:10 | 1 |
| 140 | 11222 | 11223 | 1125 | 2012-08-21 04:59:57 | hi sir |
| 147 | 11222 | 283 | 1123 | 2012-08-21 03:29:55 | yo |
| 140 | 11222 | 11223 | 1121 | 2012-08-21 02:12:13 | hello! |
| 139 | 11222 | 249 | 1120 | 2012-08-21 02:11:53 | hi :) |
| 140 | 11222 | 11223 | 1119 | 2012-08-21 02:11:26 | hi :) |
| 140 | 11222 | 11223 | 1118 | 2012-08-21 02:11:08 | hi too |
| 139 | 11222 | 249 | 1117 | 2012-08-21 02:11:00 | :P |
| 139 | 11222 | 249 | 1116 | 2012-08-21 02:10:57 | hi |
| 139 | 11222 | 249 | 1115 | 2012-08-21 02:10:51 | helo |
| 139 | 11222 | 249 | 1114 | 2012-08-21 02:06:19 | hi |
| 139 | 11222 | 249 | 1113 | 2012-08-21 02:05:45 | hi baby |
| 139 | 11222 | 249 | 1112 | 2012-08-21 02:05:00 | hi |
| 139 | 11222 | 249 | 1111 | 2012-08-21 02:04:41 | hi |
| 140 | 11222 | 11223 | 1110 | 2012-08-21 02:04:26 | hi |
| 140 | 11222 | 11223 | 1108 | 2012-08-21 01:47:40 | hey :) |
| 139 | 11222 | 249 | 1107 | 2012-08-21 01:44:43 | hi |
| 138 | 11222 | 241 | 1106 | 2012-08-21 01:44:11 | hi |
| 138 | 11222 | 241 | 1105 | 2012-08-21 01:09:20 | conv 1 msg 1 |
+-----+-------+-------+-------+---------------------+--------------+
How to extract only the first occurrence of each cid? The resulting table would be:
如何仅提取每个的第一次出现cid？结果表将是：
+-----+-------+-------+-------+---------------------+--------------+
| cid | pid | rid | clink | time | snippet |
+-----+-------+-------+-------+---------------------+--------------+
| 155 | 11222 | 1499 | 1137 | 2012-08-22 03:05:06 | hi |
| 138 | 11222 | 241 | 1136 | 2012-08-21 05:25:00 | again |
| 140 | 11222 | 11223 | 1133 | 2012-08-21 05:05:18 | hi |
| 154 | 11222 | 565 | 1132 | 2012-08-21 05:04:47 | 7 |
| 153 | 11222 | 272 | 1131 | 2012-08-21 05:04:41 | 6 |
| 146 | 11222 | 362 | 1130 | 2012-08-21 05:04:33 | 5 |
| 152 | 11222 | 364 | 1129 | 2012-08-21 05:04:27 | 4 |
| 151 | 11222 | 390 | 1128 | 2012-08-21 05:04:22 | 3 |
| 150 | 11222 | 333 | 1127 | 2012-08-21 05:04:16 | 2 |
| 148 | 11222 | 268 | 1126 | 2012-08-21 05:04:10 | 1 |
| 147 | 11222 | 283 | 1123 | 2012-08-21 03:29:55 | yo |
| 140 | 11222 | 11223 | 1121 | 2012-08-21 02:12:13 | hello! |
| 139 | 11222 | 249 | 1120 | 2012-08-21 02:11:53 | hi :) |
+-----+-------+-------+-------+---------------------+--------------+
回答by Bohemian
mysql has a "cheat" for this:
mysql对此有一个“作弊”：
select *
from mytable
group by cid;
That's all you need, because in mysql it allows you to notaggregate the non-grouped-by columns (other databases would throw a syntax error), in which case it outputs only the first occurrence of each group-by value(s). Note though that this won't guarantee the wayin which the "first" occurrence is determined (it will be just how the rows are read in)
这就是你所需要的，因为在 mysql 中，它允许你不聚合非分组列（其他数据库会抛出语法错误），在这种情况下，它只输出每个分组值的第一次出现。不过，请注意，这将无法保证的方式中，“第一”发生确定（这将是行只是如何读）
If you want a particularfirst occurrence, sort first, then apply the group-by cheat:
如果您想要特定的第一次出现，请先排序，然后应用分组秘籍：
select *
from (
-- order by the "time" column descending to get the "most recent" row
select * from mytable order by time desc
) x
group by cid
回答by John Woo
Try this one,
试试这个，
SELECT *
FROM tableName a
INNER JOIN
(
SELECT cid, MIN(`time`) MinTime
FROM tableName
GROUP BY cid
) b ON a.CID = B.cid AND
a.time = b.MinTime
回答by Andomar
You could use a filtering join:
您可以使用过滤连接：
select *
from (
select cid
, min(time) as min_time
from YourTable
group by
cid
) filter
join YourTable yt
on filter.cid = yt.cid
and filter.min_time = yt.time
回答by Lukas Eder
In MySQL 8, you would use window functionsfor this
在MySQL 8，你可以使用窗口功能，此
SELECT cid, pid, rid, clink, time, snippet
FROM (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY time) rn
FROM t
) t
WHERE rn = 1
回答by Carolina Marques
I know it's an old thread, the accepted solution would only retrieve me the collumns that had more than one occurence. This worked for me:
我知道这是一个旧线程，接受的解决方案只会检索出现多次的列。这对我有用：
SELECT cid,pid,rid,clink,max(time),snippet FROM mytable GROUP BY cid
SELECT cid,pid,rid,clink,max(time),snippet FROM mytable GROUP BY cid
