前端之家

  1. 首页
  2. 问答

分组的SQL查询执行顺序问题，然后选择

• 问答

表结构：

create table t_hr_ship (
shipment_id int,shipper_id int,date_time date,pickup_state varchar(20),dropoff_state varchar(20));

此表中有一些数据：

insert into t_hr_ship values
(1,1,"2018-01-01","WA","OR"),(2,"2018-01-02",(3,"2018-01-03",(4,"2018-01-04",(5,2,"2018-01-05",(6,3,"2018-01-06",(7,"2018-02-01","OR","WA"),(8,4,"2018-02-02",(9,"2018-02-03","CA"),(10,5,"2018-02-04","CA",(11,"2018-03-05","TX"),(12,"CA");

问题是要获得1月和2月的前3条最繁忙的路线。请注意，“ WA”到“ OR”和“ OR”到“ WA”的路线是相同的（两个端点的顺序无关紧要（只要它们的两个末端端口相同）。 解决方法如下：

select case when s.pickup_state < s.dropoff_state then s.pickup_state else s.dropoff_state end as pickup,case when s.pickup_state > s.dropoff_state then s.pickup_state else s.dropoff_state end as dropoff,count(s.shipment_id) as no_of_shipment
from t_hr_ship s
where month(s.date_time) in ("01","02")
group by pickup,dropoff
order by no_of_shipment desc
limit 3;

这确实达到了我的期望。我的问题是：我从在线资源中得知，SQL查询的执行顺序是from -> where -> group -> having -> select -> order by -> limit",if this is true than this solution should not work because the提取and下降defined in选择can't be used in分组依据。我错过了什么吗？

xinchengsoft 回答：分组的SQL查询执行顺序问题，然后选择

您可以使用least()和greatest()进行分组：

select 
    least(pickup_state,dropoff_state) pickup,greatest(pickup_state,dropoff_state) dropoff,count(*) as no_of_shipment
from t_hr_ship s
where month(date_time) in (1,2)
group by pickup,dropoff
order by no_of_shipment desc
limit 3;

请注意，与其他RDBMS不同，MySQL允许在GROUP BY子句（以及ORDER BY子句中使用列别名，但这在大多数RDMS中很常见）。

Demo on DB Fiddle ：

pickup | dropoff | no_of_shipment
:----- | :------ | -------------:
OR     | WA      |              8
CA     | OR      |              2
CA     | WA      |              1
,

执行顺序不是由查询确定的。 SQL不是一种过程语言，它是一种声明性语言。

SELECT语句描述了结果集。实际上，最终执行路径可能与实际查询不太相似-尽管MySQL不如其他数据库复杂。

指定的 是解释查询中列别名含义的顺序。这就是您所指的。

某些数据库（例如MySQL）放宽了标准，并允许GROUP BY中使用列别名。就这么简单。

mysqlsql
本文链接：https://www.f2er.com/3159199.html

大家都在问