开发者

MySQL query; if greater than a value 'yes' else 'no'; grouping

I have this data in a table:

id  account status
1      8       1
2      8       4
4      8       3
5      8       1
6      1       4
7      1       3

I want a single query to return me the account number, and if any of the status' for that account is <3 then return 'Yes' else 'No'. So, these results:

account pending
8          'Yes'
1          'No'

I had:

SELECT accou开发者_如何学Pythonnt, IF(status>2,'No','Yes') as pending FROM table GROUP BY account;

But it only seems to take into account the first row for each account. (ex. id 1's status=1 so even if id 4's status is changed so status=1, it'll still think all is greater than 2.)

I really appreciate any help. I normally can do decent query design, but this is giving me a brain cramp. :)


    SELECT account, IF(max(status)>2,'No','Yes') as pending 
FROM table 
GROUP BY account


Try using an aggregate function, for example BIT_OR:

SELECT account, IF(BIT_OR(status>2),'No','Yes') as pending FROM table GROUP BY account; 
0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜