如何使用SQL查询获取表中的最后一行？ [英] How to get last row in a table with SQL query?

问题描述

我不明白这一点。我想和两张桌子做关系，但我想得到第二张桌子的最后一行。



我写道：

I don't understand this. I want to do relatıon wıth two tables, but I want to get the last row in the second table.

I write:

select customer.name,Last(person.number) as X from customer,person where customer.id=person.id

这里有什么问题？最后不是工作。

What's wrong here? The LAST is not workıng.

推荐答案

调整威廉的答案，我会建议这样的东西：

Adjusting William's answer, I would suggest something of the sort:

DECLARE @intLastNumber int
 
SELECT @intLastNumber = MAX(person.number) FROM person
 
SELECT
  [C].[name],
  [P].[number]
FROM
  customer [C]
  INNER JOIN person [P]
    ON [C].[id] = [P].[id]
WHERE
  [P].[number] = @intLastNumber

如果你必须在一个地方执行操作查询，只需执行另一个连接：

If you have to perform the operation in one query, just perform another join like so:

SELECT
  [C].[name],
  [P].[number]
FROM
  customer [C]
  INNER JOIN person [P]
    ON [C].[id] = [P].[id]
  INNER JOIN
  (
    SELECT MAX(person.number) AS [LastNumber] FROM person
  ) [SQ]
    ON [P].[number] = [SQ].[LastNumber]

上述内容也可以。我没有执行任何这些，但我相信它们应该可以工作。

Something like the above would work as well. I haven't executed any of these, but I believe they should work.

我建​​议更多地学习SQL并尝试更详细地理解SELECT语句的每个部分，因为你显然不明白它发生了什么。



我会尽快为你打破SELECT。

这是基本的结构

I would suggest studying SQL a little more and trying to understand each part of the SELECT statment in more detail because you clearly don't understand what it happening.

I'll try to break down the SELECT for you quickly.
This is the basic structure

SELECT [columns] FROM [tables] WHERE [whereclause]

所以，[columns]是告诉查询将哪些信息放入每一行。 [tables]告诉查询从何处获取数据。 [whereclause]告诉查询从哪个数据获取信息。



因此，这是SELECT语句实际执行的操作。你说，每一行都有两列，customer.name和Last（person.number）。这意味着对于它返回的任何数据，每行将显示客户名称和person.number列中的最后一个值。这将在返回的每一行中进行。



然后，您告诉它从客户和个人那里获取数据，并在每次id匹配时返回一行。 br />


您需要做的是指定您只想返回一行，而不是每行。



Henry的建议不起作用，因为你不能在where子句中使用聚合函数，但是如果可以修改为：

So, [columns] is telling the query what information to put into each row. [tables] is telling the query where to get the data. [whereclause] is telling the query what data to get the information from.

So, here's what the SELECT statement is actually doing. You're saying, that each row will have two columns, customer.name and Last(person.number). This means that for any of the data it returns, each row will show the customer name and the last value in the person.number column. That will go in every row returned.

Then, you tell it to get the data from customer and person and return a row for each time that the id's match.

What you need to do is specify that you only want to return the one row, not every row.

Henry's suggestion won't work because you can't have aggregate functions in the where clause but if could be modified to something like:

SELECT customer.name, person.number
FROM customer, person
WHERE (customer.id=person.id AND
       person.number=(SELECT Last(person.number) FROM person))

Hows about about something喜欢：

Hows about something like:

SELECT customer.name, person.number as X
 FROM customer, person
 WHERE customer.id = person.id AND
   person.id = Max(person.id)

请注意：这不在我的头顶，可能无效。

Please note: this is off the top of my head, may not work.

这篇关于如何使用SQL查询获取表中的最后一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！