开发者

MySQL group by issue

开发者 https://www.devze.com 2023-01-17 08:45 出处:网络
I\'m having a strange problem with MySQL and would like to see if the community开发者_Python百科 has any thoughts:

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;
0

精彩评论

暂无评论...
验证码 换一张
取 消