仅当添加了ORDER BY时查询才返回任何结果
我有一个具有以下模式的表格：
people_stages
id | person_id | stage_id | created
1 | 1 | 1 | 2013-09-01 00:00:00
2 | 1 | 2 | 2013-09-02 00:00:00
3 | 1 | 3 | 2013-09-03 00:00:00
我创建了以下查询来选择按人员分组的最新阶段：
SELECT *
FROM people Person
LEFT JOIN people_stages PersonStage ON PersonStage.person_id = Person.id
WHERE PersonStage.created = (SELECT MAX(people_stages.created) FROM people_stages GROUP BY person_id HAVING person_id = PersonStage.person_id);
它工作正常，但是，如果我尝试对Person表中的字段进行排序：
SELECT *
FROM people Person
LEFT JOIN people_stages PersonStage ON PersonStage.person_id = Person.id
WHERE PersonStage.created = (SELECT MAX(people_stages.created) FROM people_stages GROUP BY person_id HAVING person_id = PersonStage.person_id)
ORDER BY Person.last_name;
它返回0结果。
有谁能提供一些见解吗？
谢谢！
编辑： people结构
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| internal_id | varchar(50) | NO | MUL | NULL | |
| public_id | varchar(30) | NO | | NULL | |
| counselor_id | bigint(20) | NO | | NULL | |
| term_id | int(11) | NO | MUL | NULL | |
| program_id | int(11) | NO | | NULL | |
| person_type_id | int(11) | NO | MUL | NULL | |
| first_name | varchar(100) | NO | | NULL | |
| middle_name | varchar(100) | NO | | NULL | |
| last_name | varchar(100) | NO | | NULL | |
| photo_url | varchar(255) | NO | | NULL | |
| gender | enum('m','f','u') | NO | | NULL | |
| date_of_birth | date | NO | | NULL | |
| address | varchar(255) | NO | | NULL | |
| address_apt | varchar(100) | NO | | NULL | |
| address_city | varchar(100) | NO | | NULL | |
| address_state | varchar(100) | NO | | NULL | |
| address_state_intl | varchar(255) | NO | | NULL | |
| address_zip | varchar(25) | NO | | NULL | |
| address_country | varchar(100) | NO | | NULL | |
| address_verified | tinyint(1) | NO | | NULL | |
| address_latitude | varchar(100) | NO | | NULL | |
| address_longitude | varchar(100) | NO | | NULL | |
| address_position | point | NO | MUL | NULL | |
| address_distance | smallint(6) | NO | | NULL | |
| social_facebook | mediumtext | NO | | NULL | |
| social_twitter | varchar(255) | NO | | NULL | |
| social_instagram | varchar(255) | NO | | NULL | |
| phone_cell | varchar(25) | NO | | NULL | |
| phone_cell_clean | varchar(25) | YES | | NULL | |
| phone_work | varchar(25) | NO | | NULL | |
| phone_work_clean | varchar(25) | NO | | NULL | |
| permission_to_text | tinyint(1) | NO | | NULL | |
| permission_to_text_confirm | tinyint(1) | NO | | NULL | |
| phone_home | varchar(25) | NO | | NULL | |
| phone_home_clean | varchar(25) | YES | | NULL | |
| email_address | varchar(255) | NO | | NULL | |
| permission_to_email | tinyint(1) | NO | | NULL | |
| preferred_contact | enum('phone_home','phone_cell','text_cell','email','postal') | NO | | NULL | |
| parent_first_name | varchar(100) | NO | | NULL | |
| parent_last_name | varchar(100) | NO | | NULL | |
| parent_email | varchar(255) | NO | | NULL | |
| hs_name | varchar(255) | NO | | NULL | |
| hs_homeschooled | tinyint(1) | NO | | NULL | |
| hs_ceeb_id | varchar(100) | NO | | NULL | |
| hs_grad_year | varchar(4) | NO | | NULL | |
| coll_name | varchar(255) | NO | | NULL | |
| coll_ceeb_id | varchar(100) | NO | | NULL | |
| coll_major | varchar(255) | NO | | NULL | |
| coll_year | varchar(20) | NO | | NULL | |
| counselor_read | tinyint(1) | NO | | NULL | |
| source | varchar(100) | NO | | NULL | |
| entry_method | varchar(100) | NO | | NULL | |
| erp_processed | tinyint(1) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| modified | datetime | NO | | NULL | |
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
这似乎是MySQL中的一个错误，我已经提交了一份报告。 我已经缩小到以下测试用例，哪一个会期望返回单个记录（但它不会）：
CREATE TABLE t (x INT NULL); -- table with nullable column
INSERT INTO t VALUES (0); -- but non null data
SELECT a.x -- select our nullable column
FROM t a, (SELECT NULL) b -- joining it with anything at all
WHERE EXISTS ( -- but filter on a subquery
SELECT *
FROM (SELECT NULL) c -- doesn't really matter what
HAVING a.x IS NOT NULL -- provided there is some correlated condition
-- on our nullable column in the HAVING clause
)
ORDER BY RAND() -- then perform a filesort on the outer query
在sqlfiddle上查看。
在你的情况下，你可以做很多事情来解决这个问题：
通过重写为联接来避免相关的子查询：
SELECT *
FROM people AS p LEFT JOIN (people_stages AS s NATURAL JOIN (
SELECT person_id, MAX(created) created
FROM people_stages
GROUP BY person_id
) t) ON s.person_id = p.id
ORDER BY p.last_name
如果要保留相关的子查询（通常可能会导致性能较差但通常易于理解），请使用WHERE而不是HAVING ：
SELECT *
FROM people AS p LEFT JOIN people_stages AS s ON s.person_id = p.id
WHERE s.created = (
SELECT MAX(created)
FROM people_stages
WHERE person_id = s.person_id
)
ORDER BY p.last_name
如果您无法更改查询，则应该发现将people_stages.person_id列people_stages.person_id非空值将解决此问题：
ALTER TABLE people_stages MODIFY person_id BIGINT UNSIGNED NOT NULL
看起来，在该列上有一个索引（这将需要实现一个外键约束）也可能有所帮助：
ALTER TABLE people_stages ADD FOREIGN KEY (person_id) REFERENCES people (id)
或者，可以从选择列表中删除people_stages.person_id ，或者调整数据模型/索引/查询策略以避免文件（在这种情况下可能不实际，但为了完整性，我在此提及它们）。
检查你的服务器没有空间不足......是的，听起来很奇怪，但是所描述的行为可能是由于
链接地址: http://www.djcxy.com/p/74715.html上一篇: Query Returns No Results Only When ORDER BY Added
下一篇: What is the difference between $.ajax with type: post and $.post