Goup by子句混淆.... [英] Goup by clause confused....
问题描述
你好NG，
我有以下问题，希望你能帮我（MS-SQL服务器
2000）
想象一下这样的声明：
" select id，firstname，（从testdata中选择前1个id）作为testid，lastname
来自名字顺序by firstname"
我希望将其分组为lastname...我假设我必须
使用分组依据条款，但它一直在抱怨id，firstname等
不在条款中...如果我只是插入按姓氏分组在上面的
声明中。
如何对这些数据进行分组？
-
问候，
Summa
Hi NG,
I have the following problem that I hope you can help me with (MS-SQL server
2000)
Imagine a statement like this:
"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"
I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc
not being in the clause...if I just inserts the "Group by lastname" in the
statement above.
How do I group these data?
--
regards,
Summa
推荐答案
您好
您的目前的陈述没有多大意义！没有DDL（创建表
语句）和示例数据（作为插入语句）和预期输出，它很难知道你真正想要的是什么。
但你可能想尝试
SELECT n.id，n.Firstname，max（t.id）作为TestId，n.lastname
来自nametable n JOIN TestData t on n.id = t.id
GROUP BY n.id，n.Firstname，n.lastname
$ b $约翰
" Summa" <苏*** @ summarium.dk>在消息中写道
news：cb *********** @ news.cybercity.dk ...
Hi
Your current statement does not make much sense! Without DDL (Create table
statements) and example data (as insert statements) and expected output, it
is hard to know what your really want.
But you may want to try
SELECT n.id, n.Firstname, max(t.id) as TestId, n.lastname
from nametable n JOIN TestData t on n.id = t.id
GROUP BY n.id, n.Firstname, n.lastname
John
"Summa" <su***@summarium.dk> wrote in message
news:cb***********@news.cybercity.dk...
嗨NG，
<我有以下问题，希望你能帮我（MS-SQL
server 2000）
想象一下这样的声明：
" ;选择id，firstname，（从testdata中选择前1个id）作为testid，姓名从名字顺序按名字命名
我想将其按lastname分组。我假设我必须使用分组依据。但是它一直抱怨id，firstname，
等不在条款中...如果我只是插入Group by lastname在上面的
声明中。
如何对这些数据进行分组？
-
问候，
Summa
Hi NG,
I have the following problem that I hope you can help me with (MS-SQL server 2000)
Imagine a statement like this:
"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"
I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc not being in the clause...if I just inserts the "Group by lastname" in the
statement above.
How do I group these data?
--
regards,
Summa
On Sun，2004年6月20日14:28:51 +0200，Summa写道：
On Sun, 20 Jun 2004 14:28:51 +0200, Summa wrote:
Hi NG，
我有以下问题，希望你能帮我（MS-SQL服务器2000年）
想象一下这样的声明：
选择id，firstname，（从testdata中选择前1个id）作为testid，姓氏
从名字顺序by firstname
我想将其分组为 ;姓氏" ...我假设我必须使用分组依据但是它一直在抱怨id，firstname等
不在条款中...如果我只是插入Group by lastname在上面的
声明中。
如何对这些数据进行分组？
Hi NG,
I have the following problem that I hope you can help me with (MS-SQL server
2000)
Imagine a statement like this:
"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"
I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc
not being in the clause...if I just inserts the "Group by lastname" in the
statement above.
How do I group these data?
嗨Summa，
如果您使用分组依据，则选择列表中的所有列也必须出现在
group by子句中，或者它们必须是聚合函数。这个
是确保SQL Server能够明确地返回
正确结果的唯一方法。
如果你想要group by lastname，如果nametable中的两行具有相同的姓氏，SQL Server如何呈现它的结果？
结果？由于
组，只有一行可以返回这个姓氏 - 但是哪个id
和firstname应该显示？
我需要更多地了解你的表格结构，数据和期望的结果，以便给予更多具体的帮助。如果您需要更多帮助，请发布以下内容：
*相关表格的DDL（CREATE TABLE语句，包括所有
相关约束），
*样本数据（以INSERT语句的形式），
*和预期输出。
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Hi Summa,
If you use group by, all columns in the select list must either appear in
the group by clause as well, or they must be an aggregation function. This
is the only way to make sure that SQL Server can unambiguously return the
correct results.
If you want to group by lastname, how should SQL Server present it''s
results if two rows in nametable have the same lastname? Because of the
group by, only one row may be returned with this lastname - but which id
and firstname should be displayed?
I need to know more about your table structure, data and desired result to
give more specific aid. If you need more help, post the following:
* DDL for the relevant tables (CREATE TABLE statements, including all
relevant constraints),
* Sample data (in the form of INSERT statements),
* And expected output.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
您好，
约翰贝尔 < JB ************ @ hotmail.com>在消息中写道
新闻：_R ******************* @ news-text.cableinet.net ...
Hi,
"John Bell" <jb************@hotmail.com> wrote in message
news:_R*******************@news-text.cableinet.net...
你目前的陈述没有多大意义！没有DDL（创建表格
语句）和示例数据（作为插入语句）和预期输出，
很难知道你真正想要的是什么。
Your current statement does not make much sense! Without DDL (Create table
statements) and example data (as insert statements) and expected output, it is hard to know what your really want.
好吗？这只是一个例子......但是假设我有这张桌子：
表n：
id int
firstname ntext
lastname ntext
该表中有5条记录（列为id，firstname，lastname）：
1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger
我的选择必须在lastnames上包含一个条款，它给我
机会将它们指定为列表 - 如下所示：
"从n中选择id，firstname，lastname，其中姓氏为
（''Eastwood''，''Scwarzenegger''），按名字命名"
这给了我结果
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger
但是我想要这个：
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger
即：
1：按姓氏分组
2：list-clause中第一个指定的姓氏也是结果中首先列出的
姓氏组。
我在上面的问题：
1.如何分组数据。
2.如何订购分组（例如：Eastwood group come.before
Scwarzenegger）
3.我的数据包含无法分组的字段（ntext）
希望你知道我现在的意思:)
-
问候，
Summa
Ok? It was just example an statement...but suppose I have this table:
Table n:
id int
firstname ntext
lastname ntext
There are 5 records in that table (listed as id,firstname,lastname):
1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger
My select MUST include a clause on the lastnames that gives me the
opportunity to specify them as a list - like this:
"Select id, firstname, lastname from n where lastname in
(''Eastwood'',''Scwarzenegger'') order by firstname"
That gives me the result
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger
But I want this:
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger
That is:
1: Grouped by lastname
2: The lastname specified first in the list-clause is also the
lastname-group that is listed first in the result.
My problems int the above:
1. How to group the data.
2. How to order the groupings (eg: Eastwood group comes.before
Scwarzenegger)
3. My data contains fields that cant be Grouped (ntext)
Hope u know what I mean now :)
--
regards,
Summa
这篇关于Goup by子句混淆....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
