表staff
id fenshu xiuxi bumen
1 80 0 2
2 80 1 2
3 90 2 2
4 60 0 3要求:1.同一部门(bumen)的只显示一条数据
2.取值顺序,如果休息(xiuxi)日大于0的,取分数(fenshu)高的
3.如果休息(xiuxi)日小于0,取分数(fenshu)低的如果只能以上数据,结果会是这样id fenshu xiuxi bumen
3 90 2 2求SQL代码或者PHP代码都可以。
id fenshu xiuxi bumen
1 80 0 2
2 80 1 2
3 90 2 2
4 60 0 3要求:1.同一部门(bumen)的只显示一条数据
2.取值顺序,如果休息(xiuxi)日大于0的,取分数(fenshu)高的
3.如果休息(xiuxi)日小于0,取分数(fenshu)低的如果只能以上数据,结果会是这样id fenshu xiuxi bumen
3 90 2 2求SQL代码或者PHP代码都可以。
(
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)/*
要得到这样的报表:
id fenshu xiuxi bumen
3 90 2 2
4 60 0 3*/
(select max(fenshu) as fenshu, bumen from staff group by bumen) t
where staff.bumen = t.bumen and staff.fenshu=t.fenshu
就你的数据,这样就可以了不太明白“如果休息(xiuxi)日小于0,取分数(fenshu)低的”的含义,数据中也没有提现出来
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"); //查出所有部门对应的xiuxi
if($data){
$res = array();
foreach($data as $each){
if($each['xx']>0){
$col = getdata("select * from t3 where bumen = '{$each['bumen']}' order by fenshu desc"); //如果xiuxi大于0,则倒序排列
}else{
$col = getdata("select * from t3 where bumen = '{$each['bumen']}' order by fenshu asc"); //反之则正序(xiuxi不会小于0吧?
}
if($col) $res[]=$col[0];
}
}
print_r($res);
结果:
Array
(
[0] => Array
(
[id] => 3
[fenshu] => 90
[xiuxi] => 2
[bumen] => 2
) [1] => Array
(
[id] => 4
[fenshu] => 60
[xiuxi] => 1
[bumen] => 3
))
(
[0] => Array
(
[id] => 3
[fenshu] => 90
[xiuxi] => 2
[bumen] => 2
) [1] => Array
(
[id] => 4
[fenshu] => 60
[xiuxi] => 0
[bumen] => 3
))写错了溢出
如果休息(xiuxi)日小于0,取分数(fenshu)低的
这个可以去掉,因为不太可能存在这情况。