SQL Server Max语句返回多个结果 [英] SQL Server Max statement returns multiple results
问题描述
这似乎是因为我也是查询t2中包含唯一条目的其他信息。
简化表格示例
t1.number t2.number_id t2.sync_id t2.text
1 1 1'我的问题是
1 1 2那
2 2 3多项
2 2 1是
2 2 2返回'
使用
时 SELECT t1.number,max(t2.sync_id),convert(varchar(100),t2.text)
FROM t1,t2
WHERE t1.number = t2.number_id
GROUP BY t1.number,convert(varchar(100),t2.text)
我得到多个条目i而不是从示例表中的第2行和第5行。 (100),t2.text) from GROUP BY t1.number,convert(varchar(100),t2.text)
。如果您只希望按 t1.number
进行分组,您现在按两个条件进行分组。您还可能有兴趣关于如何在单行中获取 t2.text
列中的所有文本。如果是这样看看这里: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
When querying two tables (t1, t2) and using a MAX statement for a column in t2, SQL returns multiple entries.
This seems to be because I also query other info from t2 which consists of unique entries.
Simplified table example
t1.number t2.number_id t2.sync_id t2.text
1 1 1 'My problem is
1 1 2 That
2 2 3 Multiple entries
2 2 1 Are
2 2 2 Returned'
When using
SELECT t1.number, max(t2.sync_id), convert(varchar(100),t2.text)
FROM t1, t2
WHERE t1.number = t2.number_id
GROUP BY t1.number, convert(varchar(100),t2.text)
I get multiple entries instead of just line 2 and 5 from the example table.
You need to remove convert(varchar(100),t2.text)
from GROUP BY t1.number, convert(varchar(100),t2.text)
. You are now grouping by two criteria when you just want grouping by t1.number
.
Also you may be interested on how to get all the text in the t2.text
column in one single row. If so take a look here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
这篇关于SQL Server Max语句返回多个结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!