Compare the data and take only the one that meets the requirements

Table Staff
ID Fenshu xiuxi bumen
1 80 0 2
2 80 1 2
3 90 2 2
4 60 0 3

Requirements: 1. Only one piece of data is displayed in the same department (Bumen)
2. Take the order of values, if the rest (Xiuxi) day is greater than 0, take the score (Fenshu) high
3. If the rest (Xiuxi) day is less than 0, take the fraction (Fenshu) low

If only the above data, the result will be this

ID Fenshu xiuxi bumen
3 90 2 2

Either SQL code or PHP code is available.

Reply to discussion (solution)

4 60 0 3
Why is this article useless?

4 60 0 3
Why is this article useless?

should also show that I have neglected,

CREATE TABLE Staff
(
ID Int (10),
Fenshu Int (10),
Xiuxi Int (10),
Bumen Int (10),
)
INSERT into staff (id,fenshu,xiuxi,bumen) VALUES (1,80,0,2)
INSERT into staff (id,fenshu,xiuxi,bumen) VALUES (2,80,1,2)
INSERT into staff (id,fenshu,xiuxi,bumen) VALUES (3,90,2,2)
INSERT into staff (id,fenshu,xiuxi,bumen) VALUES (4,60,0,3)

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

*/

Directly take the biggest not, feel also to judge Rest (Xiuxi) day more than 0 only with SQL can not achieve it.

Just your data, that's it.

Not quite understand "if the rest (Xiuxi) day is less than 0, take the fraction (Fenshu) Low" meaning, the data also did not withdraw out

Does your data have two Xiuxi =0 data in the same bumen?

A bad way, the bad thing is to check the entire table to generate an uncontrolled volume of the array to PHP processing, and the loop after the unknown number of cycles ....

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 all departments corresponding XIUXIIF ($data) {$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, the reverse arrangement}else{$col = GetData (" select * from t3 where bumen = ' {$each [' bumen ']} ' ORDER by Fenshu ASC ');//Reverse positive order (Xiuxi not less than 0?} if ($col) $res []= $col [0];}} Print_r ($res);

Results:
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 the wrong overflow

A place where ......... I'm sorry about the old spelling lately.

To Xuzuning,
If the rest (Xiuxi) day is less than 0, take the fraction (Fenshu) low
This can be removed because it is unlikely to exist.

