合理简单的查询问题 [英] Reasonably simple query question
问题描述
我是一个SQL初学者，这让我疯狂，因为它看起来很简单
但我无法理解。
我的桌子看起来像：
ID：int
MajorVersion：int
MinorVersion：int
内容：ntext
ID不是表键 - 不同的行可以具有相同的ID。
MajorVersion和MinorVersion列一起构成版本号。
假设一行表示该ID的1.8版，MajorVersion为1，
MinorVersion 8.
我想要做的就是查询每个ID的最高版本
的ID和内容。我尝试使用GROUP BY，但我不能这样做，因为我不能在SELECT中包含
内容。
我将不得不查询对于ID，然后加入以获得
内容？
感谢您的帮助
Chris
I''m an SQL beginner and this is driving me nuts as it seems simple enough
but I can''t figure it out.
I have a table that looks like:
ID: int
MajorVersion: int
MinorVersion: int
Content: ntext
The ID is not the table key - different rows can have the same ID.
The MajorVersion and MinorVersion columns together make up a version number.
Say if a row represented version 1.8 of that ID, MajorVersion would be 1 and
MinorVersion 8.
All I want to do is query for ID and Content for the highest versions of
each ID. I tried using GROUP BY but I can''t do that because I can''t include
Content in the SELECT then.
Am I going to have to just query for the ID and then do a join to get the
Content?
Thanks for any help
Chris
推荐答案
" Chris Vinall" < CV ***** @ nospam.myrealbox.com>在消息中写道
新闻：3f ****** @ duster.adelaide.on.net ...
"Chris Vinall" <cv*****@nospam.myrealbox.com> wrote in message
news:3f******@duster.adelaide.on.net...
我是一个SQL初学者，这是开车我很坚定，因为它看起来很简单
但我无法理解它。
我有一张看起来像的桌子：
ID：int
MajorVersion：int
MinorVersion：int
内容：ntext
ID不是表键 - 不同的行可以具有相同的ID。
MajorVersion和MinorVersion列一起组成一个版本号。
如果一行代表该ID的1.8版本，MajorVersion将为1和
MinorVersion 8.
所有我想做的是查询每个ID的最高版本的ID和内容。我尝试使用GROUP BY，但我不能这样做，因为我不能在SELECT中包含
内容。
我是否只需要查询ID然后进行连接获得
内容？
感谢您的帮助
Chris
I''m an SQL beginner and this is driving me nuts as it seems simple enough
but I can''t figure it out.
I have a table that looks like:
ID: int
MajorVersion: int
MinorVersion: int
Content: ntext
The ID is not the table key - different rows can have the same ID.
The MajorVersion and MinorVersion columns together make up a version number.
Say if a row represented version 1.8 of that ID, MajorVersion would be 1 and
MinorVersion 8.
All I want to do is query for ID and Content for the highest versions of
each ID. I tried using GROUP BY but I can''t do that because I can''t include
Content in the SELECT then.
Am I going to have to just query for the ID and then do a join to get the
Content?
Thanks for any help
Chris
假设表是T 。
SELECT T1.ID，T1。内容
来自T as T1
LEFT OUTER JOIN
T AS T2
ON T1.ID = T2.ID AND
（T2.MajorVersion> T1.MajorVersion OR
（T2 .MajorVersion = T1.MajorVersion AND
T2.MinorVersion> T1.MinorVersion））
WHERE T2.ID为空
问候，
jag
Assume table is T.
SELECT T1.ID, T1.Content
FROM T AS T1
LEFT OUTER JOIN
T AS T2
ON T1.ID = T2.ID AND
(T2.MajorVersion > T1.MajorVersion OR
(T2.MajorVersion = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion))
WHERE T2.ID IS NULL
Regards,
jag
非常感谢:)
我永远不会想到这样做就像那样
" John Gilson" < ja*@acm.org>在消息中写道
news：eU ******************* @ twister.nyc.rr.com ...
Thanks muchly :)
I would never have thought of doing it like that
"John Gilson" <ja*@acm.org> wrote in message
news:eU*******************@twister.nyc.rr.com...
假设表格为T.
SELECT T1.ID，T1。内容
FROM T AS T1
LEFT OUTER JOIN
T AS T2 < ON T1.ID = T2.ID AND
（T2.MajorVersion> T1.MajorVersion OR
（T2.MajorVersion = T1.MajorVersion AND
T2.MinorVersion> T1。 MinorVersion））
WHERE T2.ID为空
问候，
jag
Assume table is T.
SELECT T1.ID, T1.Content
FROM T AS T1
LEFT OUTER JOIN
T AS T2
ON T1.ID = T2.ID AND
(T2.MajorVersion > T1.MajorVersion OR
(T2.MajorVersion = T1.MajorVersion AND
T2.MinorVersion > T1.MinorVersion))
WHERE T2.ID IS NULL
Regards,
jag
>>我有一个看起来像的表：<<
请发帖DDL，这样人们就不用猜猜键是什么了，
约束，声明引用完整性，数据类型等在你的模式中是
。样本数据也是一个很好的想法，以及明确的
规范。这是你的意思，如果你按照
新闻组的网络礼节进行了吗？
CREATE TABLE Foobar
（product_id INTEGER NOT NULL，
major_version_nbr INTEGER NOT NULL，
minor_version_nbr INTEGER NOT NULL，
content NTEXT NOT NULL，
PRIMARY KEY（product_id，major_version_nbr，minor_version_nbr））;
你还需要阅读ISO-11179，这样你就会停止使用模糊的，
无意义的数据元素名称如id （什么??）。接下来，
之一的数据建模规则是你不要将属性分成多个列的
;列是从一个且只有一个
域中提取的属性，它代表一个完整的测量或值本身。
这就是为什么你使用日期而不是三个单独的年份列，
月和日。
同样，表中的一行是完整的事实，但那是另一个
主题。让我们来修复你的烂摊子：
CREATE TABLE Foobar - 做对了
（product_id INTEGER NOT NULL，
version_nbr DECIMAL（8,4）NOT NULL，
content NTEXT NOT NULL，
PRIMARY KEY（product_id，version_nbr））;
>> I have a table that looks like: <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications. Is this what you meant, if you had followed the
newsgroup''s netiquette?
CREATE TABLE Foobar
(product_id INTEGER NOT NULL,
major_version_nbr INTEGER NOT NULL,
minor_version_nbr INTEGER NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, major_version_nbr, minor_version_nbr));
You also need to read ISO-11179 so that you will stop using vague,
meaningless data element names like "id" (of what??). Next, one of
the rules of data modeling is that you do not split an attribute over
multiple columns; a column is an attribute drawn from one and only one
domain and it represents a complete measurment or value in itself.
That is why you use a date and not three separate columns for year,
month and day.
Likewise, a row in a table is a complete fact, but that is another
topic. Let''s fix your mess:
CREATE TABLE Foobar -- done right
(product_id INTEGER NOT NULL,
version_nbr DECIMAL (8,4) NOT NULL,
content NTEXT NOT NULL,
PRIMARY KEY (product_id, version_nbr));
我想要做的就是查询每个ID的最高
All I want to do is query for ID and Content for the highest
版本的ID和内容。 <<
SELECT F1.product_id，F1.content
来自Foobar AS F1
WHERE version_nbr
=（SELECT MAX（version_nbr）
来自Foobar AS F2
WHERE F1.product_id = F2.product_id）;
良好的经验法则：对简单事物的复杂查询通常是由于架构设计不佳造成的。这对你的
架构来说是一回事。
SELECT F1.product_id，F1.content
来自Foobar AS F1
WHERE F1.major_version_nbr
=（SELECT MAX（F2.major_version_nbr）
来自Foobar AS F2
在哪里F1 .product_id = F2.product_id）
AND F1.minor_version_nbr
=（SELECT MAX（F3.minor_version_nbr）
来自Foobar AS F3
WHERE F1.product_id = F3.product_id
AND F3.major_version_nbr
=（SELECT MAX（F4.major_version_nbr）
来自Foobar AS F4
WHERE F1.product_id = F2.product_id））;
versions of each ID. <<
SELECT F1.product_id, F1.content
FROM Foobar AS F1
WHERE version_nbr
= (SELECT MAX(version_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id);
Good rule of thumb: complex queries for simple things are most often
the result of poor schema design. Here is the same thing for your
schema.
SELECT F1.product_id, F1.content
FROM Foobar AS F1
WHERE F1.major_version_nbr
= (SELECT MAX(F2.major_version_nbr)
FROM Foobar AS F2
WHERE F1.product_id = F2.product_id)
AND F1.minor_version_nbr
= (SELECT MAX(F3.minor_version_nbr)
FROM Foobar AS F3
WHERE F1.product_id = F3.product_id
AND F3.major_version_nbr
= (SELECT MAX(F4.major_version_nbr)
FROM Foobar AS F4
WHERE F1.product_id = F2.product_id));
这篇关于合理简单的查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
