I'm having a strange problem with MySQL and would like to see if the community开发者_Python百科 has any thoughts:
I have a table 'tbl' that contains
____________
| id | sdate |
And I'm trying to execute this query:
select id, max(sdate) as sd from tbl where id in(123) group by id;
This returns no results. However, this query:
select id, sdate from tbl where id in(123);
Returns many results with id's and dates.
Why would the top query fail to produce results?
So IDs in this table aren't distinct, right? For example, it could be a list of questions here on StackOverflow with a viewed date, and each question ID could appear multiple times in the results. Otherwise, if the IDs are always unique then there's no point in doing a GROUP BY on them. When you're restricting the results to a single ID you don't technically need the GROUP BY clause since MAX() is an aggregate function that will return a single row.
What's the datatype of sdate? int/datetime?
It's perfectly fine to supply a single ID to an IN() clause; it just can't be blank: IN().
Is it possible to provide the output of "DESCRIBE tbl;" and a few example rows?
Turns out the index was corrupt. Running the following solved the issue:
REPAIR TABLE tbl;
精彩评论