英文:
sql server how to distinct data from multiple column but not all column
问题
我有一个问题，我需要从某个表中显示唯一的数据，基于 SQL Server，我有查询，但如果我选择的不是所有列都包含在 GROUP BY 中，就会出现错误。
源表格：
+------+------+----------+-------+
| code | name | process | time |
+------+------+----------+-------+
| a | qq | write | 10:10 |
| b | qq | read | 11:10 |
| c | qq | read | 12:11 |
| a | qq | write | 09:40 |
| b | yy | write | 08:50 |
| a | yy | read | 11:10 |
| b | yy | write | 09:40 |
+------+------+----------+-------+
我需要的表格：
+------+------+----------+-------+
| code | name | process | time |
+------+------+----------+-------+
| a | qq | write | 10:10 |
| b | qq | read | 11:10 |
| b | yy | write | 09:40 |
| c | qq | read | 12:11 |
| a | yy | read | 11:10 |
+------+------+----------+-------+
我使用的代码：
select code, name, process, time
from tablesource
group by code, name, process;
但这个查询导致错误：
[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120)
但如果我在 GROUP BY 中插入时间列，结果与源表格相同。请帮助我。
英文:
I have a problem, I need to show unique data from some table, sql server based, ihave the query but I have a error if not all column I select is not contain in the GROUP BY.
Table source:
+------+------+----------+-------+
| code | name | process | time |
+------+------+----------+-------+
| a | qq | write | 10:10 |
| b | qq | read | 11:10 |
| c | qq | read | 12:11 |
| a | qq | write | 09:40 |
| b | yy | write | 08:50 |
| a | yy | read | 11:10 |
| b | yy | write | 09:40 |
+------+------+----------+-------+
Table I need:
+------+------+----------+-------+
| code | name | process | time |
+------+------+----------+-------+
| a | qq | write | 10:10 |
| b | qq | read | 11:10 |
| b | yy | write | 09:40 |
| c | qq | read | 12:11 |
| a | yy | read | 11:10 |
+------+------+----------+-------+
Code I use:
select code, name, process, time
from tablesource
group by code, name, process;
But this query result in error:
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Column
> 'time' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause. (8120),
But if column time I insert in group by, the result is just same as the table source. Please help me.
答案1
得分: 2
需要修改查询以添加时间的聚合函数。
select code, name, process, max(time) from tablesource
group by code, name, process;
英文:
You need to modify query to add aggregate function for time.
select code, name, process, max(time) from tablesource
group by code, name, process;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论