Compare data, only one that meets the requirements

Compare the data. only one table that meets the requirements is taken.
Id fenshu xiuxi bumen
1 80 0 2
2 80 1 2
3 90 2 2
4 60 0 3

Requirements: 1. only one data entry is displayed for the same department (bumen ).
2. Value order. if the rest day (xiuxi) is greater than 0, the score (fenshu) is higher.
3. if The xiuxi day is less than 0

If only the above data is available, the result is as follows:

Id fenshu xiuxi bumen
3 90 2 2

SQL code or PHP code.

Reply to discussion (solution)

4 60 0 3
Why is this one useless?

4 60 0 3
Why is this one useless?

It should also be displayed. I ignored it,

Create table staff
(
Id int (10 ),
Fenshu int (10 ),
Xiuxi int (10 ),
Bumen int (10 ),
)
Insert into staff (id, fenshu, xiuxi, bumen) values)
Insert into staff (id, fenshu, xiuxi, bumen) values)
Insert into staff (id, fenshu, xiuxi, bumen) values (3,90, 2,2)
Insert into staff (id, fenshu, xiuxi, bumen) values)

/*
To get a report like this:
Id fenshu xiuxi bumen
3 90 2 2
4 60 0 3

*/

If you cannot directly obtain the maximum value, you still need to judge that the rest day (xiuxi) is greater than 0 and only SQL is not supported.

select staff.* from staff, (select max(fenshu) as fenshu, bumen from staff group by bumen) t where staff.bumen = t.bumen and staff.fenshu=t.fenshu 

It's just your data.

I don't quite understand the meaning of "if the xiuxi day is less than 0 and the score (fenshu) is low". The data has not been extracted.

Can two xiuxi = 0 records appear in the same bumen of your data?

A bad method is to check the entire table and generate an array of uncontrollable volumes for PHP processing, as well as a loop of unknown times after the loop ....

Function getdata ($ SQL) {$ result = mysql_query ($ SQL); if ($ result) $ count = mysql_num_rows ($ result); for ($ I = 0; $ I <$ count; $ I ++) {mysql_data_seek ($ result, $ I); $ data [$ I] = mysql_fetch_assoc ($ result);} return $ data ;} $ data = getdata ("select sum (xiuxi) as xx, bumen from t3 group by bumen"); // find the xiuxiif ($ data) corresponding to all departments) {$ res = array (); foreach ($ data as $ each) {if ($ each ['XX']> 0) {$ col = getdata ("select * from t3 where bumen = '{$ Each ['bumen']}' order by fenshu desc "); // If xiuxi is greater than 0, in reverse order} else {$ col = getdata ("select * from t3 where bumen = '{$ each ['bumen']}' order by fenshu asc "); // vice versa, the forward order (xiuxi is not smaller than 0, right ?} If ($ col) $ res [] = $ col [0] ;}} print_r ($ res );

Result:
Array
(
[0] => Array
(
[Id] => 3
[Fenshu] => 90
[Xiuxi] => 2
[Bumen] => 2
)

[1] => Array
(
[Id] => 4
[Fenshu] => 60
[Xiuxi] => 1
[Bumen] => 3
)

)

Array
(
[0] => Array
(
[Id] => 3
[Fenshu] => 90
[Xiuxi] => 2
[Bumen] => 2
)

[1] => Array
(
[Id] => 4
[Fenshu] => 60
[Xiuxi] => 0
[Bumen] => 3
)

)

Write error overflow

One... sorry ..

To xuzuning,
If the xiuxi day is less than 0
This can be removed, because it is unlikely that this situation exists.