在MySQL 8中使用GROUP BY [英] using of GROUP BY in mysql 8
本文介绍了在MySQL 8中使用GROUP BY的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经升级了MySQL服务器，现在我的分组依据存在问题
I have upgraded my MySQL server and now I have a problem with group by
select * from tickets WHERE `status` = 0 group by `tnumber` ORDER BY `created_at` DESC
错误是:
SELECT列表不在GROUP BY子句中，并且包含未聚合的列
SELECT list is not in GROUP BY clause and contains nonaggregated column
已更新: 我的表内容是:
Updated: my table content is:
<!DOCTYPE html>
<html>
<head>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
</head>
<body>
<h2>id</h2>
<table>
<tr>
<th>id</th>
<th>tnumber</th>
<th>title</th>
<th>status</th>
</tr>
<tr>
<td>1</td>
<td>5c63e00a072c1</td>
<td>1111</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>5c63e00a072c1</td>
<td>1111</td>
<td>1</td>
</tr>
<tr>
<td>3</td>
<td>5c63e00a072c1</td>
<td>1111</td>
<td>1</td>
</tr>
<tr>
<td>4</td>
<td>5c63e00a072c1</td>
<td>1111</td>
<td>0</td>
</tr>
<tr>
<td>5</td>
<td>5c63ead5bd530</td>
<td>2222</td>
<td>1</td>
</tr>
<tr>
<td>6</td>
<td>5c63ead5bd530</td>
<td>2222</td>
<td>1</td>
</tr>
</table>
<br/>
I want to have following output:
<br/>
<br/>
<br/>
<table>
<tr>
<th>id</th>
<th>tnumber</th>
<th>title</th>
<th>status</th>
</tr>
<tr>
<td>1</td>
<td>5c63e00a072c1</td>
<td>1111</td>
<td>0</td>
</tr>
<tr>
<td>2</td>
<td>5c63ead5bd530</td>
<td>222</td>
<td>1</td>
</tr>
</table>
</body>
</html>
我的代码是:
SELECT * FROM tickets
WHERE `tnumber` IN (
SELECT tnumber FROM tickets GROUP BY tnumber
)
但是按第二个分组，在其中选择，没有任何错误！！！ 如何以适当的方式做到这一点?
but group by in second Select in where, doesn't have any affecs!!! How to do it in a propper way?
GROUP BY语句通常与聚合函数(COUNT，MAX，MIN，SUM，AVG)一起使用，以将结果集按一列或多列分组.
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
推荐答案
从以下位置更改代码:
select * from tickets WHERE `status` = 0 group by `tnumber`;
收件人:
SELECT * from tickets where id in (SELECT max(id) FROM tickets GROUP BY tnumber) AND `status` = 0;
这篇关于在MySQL 8中使用GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
