UNION，LIMIT和ORDER BY [英] UNION, LIMIT and ORDER BY
问题描述
我试图编写一个看起来应该很简单的查询，但由于某种原因，我的尝试无效。
。这实际上是一般的
SQL问题并且与MySQL没有关系，但我找不到一个通用的
数据库讨论组，除了在进步和理论上这个
实际上是一个基本的查询构造问题。
只要说我有一个包含三列的表，名称，日期，分数和
这些代表了不同人的考试成绩。每个人都可以根据自己的喜好多次参加考试，但我只想回到最后的结果。
我的第一个想法似乎不对，但我想我还是会尝试的：
选择名称，日期，分数来自测试结果
GROUP BY name
ORDER BY日期DESC
这是错的，并且没有返回任何有用的东西，因为返回的
分数似乎是基于分组开始的位置在
某种程度上。
我的第二个想法是，人员名单很小而且知道所以我
只会将其姓名硬编码到查询中：
SELECT名称，日期，分数FROM testresults WHERE name =''bob''ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT名称，日期，分数FROM testresults名称='''mary''ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name，date，score FROM testresults WHERE name =''jim''ORDER BY
date DESC LIMIT 1
UNION ALL
这在语法上是不正确的。
任何人都可以帮我解决这个问题吗？
I''ve trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn''t pertain to MySQL but I couldn''t find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.
Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.
My first thought didn''t seem right but I figured I''d try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.
My second thought was that the list of people is small and known so I
was going to just hardcode their names into the query:
SELECT name, date, score FROM testresults WHERE name = ''bob'' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = ''mary'' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = ''jim'' ORDER BY
date DESC LIMIT 1
UNION ALL
This is syntactically incorrect.
Can anyone help me with this query?
推荐答案
5月25日下午3:23，以色列< ; israeldip ... @ hotmail.comwrote：
On May 25, 3:23 pm, Israel <israeldip...@hotmail.comwrote:
我试图写一个看起来应该很简单的查询但是
由于某种原因，我的尝试不起作用。这实际上是一般的
SQL问题并且与MySQL没有关系，但我找不到一个通用的
数据库讨论组，除了在进步和理论上这个
实际上是一个基本的查询构造问题。
只要说我有一个包含三列的表，名称，日期，分数和
这些代表了不同人的考试成绩。每个人都可以根据自己的喜好多次参加考试，但我只想回到最后的结果。
我的第一个想法似乎不对，但我想我还是会尝试的：
选择名称，日期，分数来自测试结果
GROUP BY name
ORDER BY日期DESC
这是错的，并且没有返回任何有用的东西，因为返回的
分数似乎是基于分组开始的位置在
某种程度上。
我的第二个想法是，人员名单很小而且知道所以我
只会将其姓名硬编码到查询中：
SELECT名称，日期，分数FROM testresults WHERE name =''bob''ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT名称，日期，分数FROM testresults名称='''mary''ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name，date，score FROM testresults WHERE na me =''jim''ORDER BY
date DESC LIMIT 1
UNION ALL
这在语法上是不正确的。
任何人都可以帮我解决这个问题吗？
I''ve trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn''t pertain to MySQL but I couldn''t find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.
Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.
My first thought didn''t seem right but I figured I''d try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.
My second thought was that the list of people is small and known so I
was going to just hardcode their names into the query:
SELECT name, date, score FROM testresults WHERE name = ''bob'' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = ''mary'' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = ''jim'' ORDER BY
date DESC LIMIT 1
UNION ALL
This is syntactically incorrect.
Can anyone help me with this query?
SELECT t1。* FROM测试t1
LEFT JOIN测试t2 ON t1.name = t2.name
AND t1.date t2.date
WHERE t2.date IS NULL;
SELECT t1.* FROM tests t1
LEFT JOIN tests t2 ON t1.name = t2.name
AND t1.date t2.date
WHERE t2.date IS NULL;
以色列<是**** ****** @ hotmail.com写新闻：1180102988.273271.53270
@ p77g2000hsh.googlegroups.com：
Israel <is**********@hotmail.comwrote in news:1180102988.273271.53270
@p77g2000hsh.googlegroups.com:
我''我试图写一个看起来应该很简单的查询，但由于某些原因，我的尝试不起作用。
这实际上是一般的
SQL问题并且与MySQL没有关系，但我找不到一个通用的
数据库讨论组，除了在进步和理论上这个
实际上是一个基本的查询构造问题。
只要说我有一个包含三列的表，名称，日期，分数和
这些代表了不同人的考试成绩。每个人都可以根据自己的喜好多次参加考试，但我只想回到最后的结果。
我的第一个想法似乎不对，但我想我还是会尝试的：
选择名称，日期，分数来自测试结果
GROUP BY name
ORDER BY日期DESC
这是错的，并且没有返回任何有用的东西，因为返回的
分数似乎是基于分组开始的位置在
。
I''ve trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn''t pertain to MySQL but I couldn''t find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.
Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.
My first thought didn''t seem right but I figured I''d try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.
理想情况下，这里有两个表：一个带有人名（auto-
增加用户ID，用户名），另一个带有测试结果（自动增加
tableID，ID引用人，testdate，testresult）。这将是
进行此查询，以及之后可能创建的其他人，
非常容易（虽然这是未经测试的）：
SELECT t.date，t.score，u.name
FROM testresults t
JOIN users u ON t.userID = u.userID
GROUP BY t.userID
ORDER BY t.date DESC
实现有更好的数据库设计方法的关键是
任何数据复制：即在您的表中，名称为Bob。在参加考试时多次输入
。如果他打电话给你并说'Id
更喜欢我的名字被存储为罗伯特·怎么办？你将不得不在这张桌子和其他任何其他地方拖网搜索，当真的没有需要时改变数据
这样做 - 改变一个条目"用户"表格会这样做。
如果你不想改变你的桌面设计，我会坚持你的
查询（减去GROUP BY）并使用编程语言（即：PHP）
正确显示结果。
Ideally, you would have two tables here: one with people names (auto-
increment user id, user name) and one with test results (auto-increment
tableID, ID referencing the person, testdate, testresult). This would
make this query, and others you are likely to create afterwards,
incredibly easier (though this is untested):
SELECT t.date,t.score,u.name
FROM testresults t
JOIN users u ON t.userID=u.userID
GROUP BY t.userID
ORDER BY t.date DESC
The key to realizing there is a better way to design your database is
any replication of data: ie in your table, the name "Bob" is entered as
many times as he took the test. What if he called you up and said "Id
prefer my name be stored as Robert"? You''d have to go trawling through
this table, and any others, changing data when there is really no need
to do so - changing a single entry in the "users" table would do it.
If you don''t want to change your table design, I would stick with your
query (minus the GROUP BY) and use a programming language (ie: PHP) to
display the results correctly.
5月28日下午1:35，Good Man < h ... @ letsgo.comwrote：
On May 28, 1:35 pm, Good Man <h...@letsgo.comwrote:
实现有更好的方法设计数据库的关键是
any数据复制：即在您的表中，名称为Bob。在参加考试时多次输入
。如果他打电话给你并说'Id
更喜欢我的名字被存储为罗伯特·怎么办？你将不得不在这张桌子和其他任何其他地方拖网搜索，当真的没有需要时改变数据
这样做 - 改变一个条目"用户"表会做的。
The key to realizing there is a better way to design your database is
any replication of data: ie in your table, the name "Bob" is entered as
many times as he took the test. What if he called you up and said "Id
prefer my name be stored as Robert"? You''d have to go trawling through
this table, and any others, changing data when there is really no need
to do so - changing a single entry in the "users" table would do it.
为简洁起见，我对表的解释进行了修改。实际上有两个表（由FK链接）实际上有两个表（数据实际上是参数
）一台机器上某些硬件组件状态的数据
定期记录，但我想创建一个查询，它将在任意时间向我显示所有组件的最新信息。
这个查询只会列出一些参数类型，但是在
上有100个参数类型的顺序，参数log
table保留了所有的历史记录后期分析，并且可以在
的100k-200k记录订单上进行，因此将表格重新连接到自身不是
考虑到它的速度有多慢。
除非我错过了关于加入这个
方式的表现，否则我可能不得不求助于多个独立查询 -
因此我最初的想法是使用UNION ALL，我似乎无法获得
得到语法co我正试图避免编写任何代码，因为我们已经在制作一个允许的前端应用程序中使用了
计划。
用户提取各种数据所以我不想浪费时间
制作一个hodge podge小应用程序或php代码的集合
重复这项工作。在我们完成最终申请之前，我想要通过查询来做所有事情，否则就像拉动
牙齿让用户停止使用所有的临时应用程序。应用程序
但在临时人员中仍然可以获得所需的数据。
My explanation of the tables was modified for brevity. There''s
actually two tables (linked by FK) and the data is actually parameter
data for the state of some hardware components on a machine that get
logged at a regular interval but I wanted to create a query that would
just show me the latest for all of the components at arbitrary times.
This query would only list a handful of parameter types but there''s on
the order of 100 parameter types all together and the parameter log
table keeps all of the history for post-analysis and can have on the
order of 100k-200k records so joining a table back onto itself is not
really option considering how slow it is.
Unless I''m missing something about the performance of joining in this
way I''ll probably have to resort to multiple independent queries -
hence my original thought of using UNION ALL which I couldn''t seem to
get the syntax correct for.
I was trying to avoid having to write any code because we already have
plans in the works for making a front end application that will allow
users to extract all sorts of data so I don''t want to waste time
making a collection of hodge podge little apps or php code that
duplicates this work. Until we get the final application completed I
wanted to do everything via queries otherwise it will be like pulling
teeth to get users to stop using all of the "temporary" applications
but in the interim people can still get the data they need.
这篇关于UNION，LIMIT和ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
