mysql - 在存在子查询中按字段对 MySql 查询进行排序
问题描述
我正在寻找一种方法来按存在查询中存在的字段对查询进行排序。
尝试使用存在子查询中的字段时出现“未知列”错误。
示例查询;
select
*
from
`table_a`
where
`a_field` = 'foo'
and exists (
select
*
from
`table_b`
where
`table_a`.`an_id` = `table_b`.`another_id` and `table_b`.`another_field` = 'bar'
)
order by
`table_a`.`created_at` asc,
`table_b`.`another_id` desc;
是使用内部联接查询的唯一解决方案,例如;
select
`t1`.*
from
`table_a` as `t1`
inner join `table_2` as `t2` on `t2`.`another_id` = `t1`.`an_id`
where
`t1`.`a_field` = 'foo'
and `t2`.`another_field` = 'bar'
order by
`t1`.`created_at` asc,
`t2`.`another_id` desc;
解决方案
您的示例查询是 order by another_id
,它在相关子句中使用。所以,你可以这样做:
select a.*
from table_a a
where a.a_field = 'foo' and
exists (select 1
from table_b b
where a.an_id = b.another_id and
b.another_field = 'bar'
)
order by a.created_at asc,
a.an_id desc;
假设您实际上想要一个不同的列,您可以使用JOIN
. 问题是不止一行可能匹配。因此,您需要删除子查询中的重复项:
select a.*
from table_a a join
(select b.another_id, max(b.another_col) as another_col
from table_b b
where another_field = 'bar'
group by b.another_id
) b
on a.an_id = b.another_id
where a.a_field = 'foo'
order by a.created_at asc, b.another_id desc;
JOIN
如果您知道最多有一行匹配,则只能使用您的表单。
推荐阅读
- mongodb - 即使在使用安全组完成网络分区后,MongoDB 节点(AWS EC2 实例)仍然响应
- java - 如何实现 10 折交叉验证?
- javascript - IE 11(Webpack、VueJS)上可能出现传播错误?
- office365 - How long does it take for an office add in to be available in the store after being accepted?
- android - 如何解析空名称json数组(仅限字符串)
- java - 原始字符串文字 - 删除前导缩进
- elisp - 在 elisp 中通过 `re-search-forward` 使用正则表达式
- filesystems - Checking if a folder exists
- python - Python classes/functions & self paramter
- php - 如何在 PHP 中删除空格、%20、双破折号和其他字符