Mariadb子查询中LIMIT的行为
这是我的问题。Mariadb子查询中LIMIT的行为
我不擅长SQL查询,所以我希望能够对LIMIT子句的不同行为有所了解。我有两个疑问。可以说我的表有3列,name
,date
和date_modified
。我每小时修改一次表格,并使用date_modified
列对其进行修改。我试图获取针对特定日期2017-12-12
修改日期的最后一条记录(最近修改过)。
SELECT * from
(SELECT * from table where name in ('name1','name2','name3')
and date in ('2017-12-12') order by date_modified desc)
as tmp_table group by name
SELECT * from
(SELECT * from table where name in ('name1','name2','name3')
and date in ('2017-12-12') order by date_modified desc LIMIT 100)
as tmp_table group by name
第一个返回一个表格,其中修改日期是当天最早的记录。第二个是我想要的,它返回最新修改的数据。如果我有一个更大的表,其中name3
是第101条记录,则查询将无法正常工作。所以对LIMIT进行硬编码是不可行的,也是一种不好的做法。
为什么会有差异?按顺序通过在子查询中不工作?
*我只是在我自己想到解决方案时添加更多信息。
加上:上面的子查询具有相同的行为;即数据按照修改日期的降序排序。
date_modified
和date
都是日期时间格式。
表是一组无序的数据。对于派生表(即子查询)也是如此。
首先查询
您从表中选择,责令某些记录。 DBMS可以完全忽略这个ORDER BY
子句,因为您只将数据用作子查询。然后按名称分组。现在我们必须区分两种情况:
-
name
+date
在表中是唯一的。然后你返回你找到的行。GROUP BY
条款将是多余的。 -
name
+date
在表中并不是唯一的。但你select *
。这是无效的SQL,因为如果对于一个名称和日期有多个行,则不会告诉DBMS要选择哪个值。如果MariaDB让这个漏洞出现,这是DBMS的一个缺陷。
从您的描述看来,第二种情况适用。您的查询无效。
第二个查询
您以应用LIMIT
子句从表中选择,责令某些记录。这限制了结果,但DBMS可以随意以任何顺序输出行,因为您将数据用作子查询。当您订购date_modified
时,您可能会丢弃结果中的某些名称(例如,最后的100个记录中的所有名称1和名称2,然后您会忽略名称3。
至于GROUP BY name
:我所说的关于第一个查询也适用于此处。您将数据限制为100行没有区别。
谢谢。这回答了我的问题。对于我的查询,它会工作,如果我创建一个临时表,然后选择*从它?所以它会像'select * from(创建临时表tmp(...))' –
。您必须找到一种方法来获取每个名称的最后一个条目。但你还没有找到这样的方式。我在其他答案中已经展示了典型的方法。另一个将是'select ... where(name,date_modified)in(select name,max(max_date_modified)from ... group by name)'。 –
好的。最后一个问题;所以我的查询2得到的结果,但查询1 doesnt。这不是由于限制条款?根据你所说的DBMS以任何顺序排列输出,我得到的“正确”顺序是“偶然”,并且查询不可靠吗? –
这不是你的问题的真正答案。查看我的其他答案,了解您的查询有何错误以及您对其行为的假设。
我猜你想这是什么,每个名字的最新条目2017年12月12日:
select *
from
(
select
t.*,
max(date_modified) over (partition by name) as max_date_modified
from t
where name in ('name1', 'name2', 'name3')
and date in (date '2017-12-12')
) numbered
where date_modified = max_date_modified
order by name;
UPDATE:看来MariaDB不支持MAX OVER
())。因此请使用ROW_NUMBER
代替:
select *
from
(
select
t.*,
row_number() over (partition by Name order by date_modified desc) as rn
from t
where name in ('name1', 'name2', 'name3')
and date in (date '2017-12-12')
) numbered
where rn = 1
order by name;
查看内部查询中返回的内容。在'name3'是第101条记录的情况下,它是否表现得像预期的那样? –
yes内部查询按date_modified正确排序表。对不起,在代码中有一个错字。我只是修复它。我希望它现在更清晰 –
'SELECT * GROUP BY'是无效的SQL。您希望得到什么值不会出现在GROUP BY条款中?他们是不确定的。 – axiac