我有一个表结构
id,trackid,table_name,操作,oldvalue,newvalue,字段,onetime
现在,如果我为相同的“ trackid”,相同的“ field”有3行,那么我该如何从三行中选择最新的?
即用于:
id = 100 trackid = 152 table_name
= jos_menu操作=更新旧值= IPL新值= IPLcccc
字段=名称live = 0已更改一次=
2010-04-30 17:54:39
和
id = 101 trackid = 152 table_name =
jos_menu操作=更新旧值
= IPLcccc新值= IPL2222字段=名称有效= 0更改一次=
2010-04-30 18:54:39
如您所见,secind条目是最新的更改,
现在我应该使用哪种查询从许多这样的行中获取唯一的和最新的行...
更新
$distupdqry = "select DISTINCT trackid,table_name from jos_audittrail where live = 0 AND operation = 'UPDATE'";
$disupdsel = mysql_query($distupdqry);
$t_ids = array();
$t_table = array();
while($row3 = mysql_fetch_array($disupdsel))
{
$t_ids[] = $row3['trackid'];
$t_table[] = $row3['table_name'];
//$t_table[] = $row3['table_name'];
}
//echo "<pre>";print_r($t_table);echo "<pre>";
//exit;
for($n=0;$n<count($t_ids);$n++)
{
$qupd = "SELECT * FROM jos_audittrail WHERE operation = 'UPDATE' AND trackid=$t_ids[$n] order by changedone DESC ";
$seletupdaudit = mysql_query($qupd);
$row4 = array();
$audit3 = array();
while($row4 = mysql_fetch_array($seletupdaudit))
{
$audit3[] = $row4;
}
$updatefield = '';
for($j=0;$j<count($audit3);$j++)
{
if($j == 0)
{
if($audit3[$j]['operation'] == "UPDATE")
{
//$insqry .= $audit2[$i]['operation']." ";
//echo "<br>";
$updatefield .= "UPDATE `".$audit3[$j]['table_name']."` SET ";
}
}
if($audit3[$j]['operation'] == "UPDATE")
{
$updatefield .= $audit3[$j]['field']." = '".$audit3[$j]['newvalue']."', ";
}
}
/*echo "<pre>";
print_r($audit3);
exit;*/
$primarykey = "SHOW INDEXES FROM `".$t_table[$n]."` WHERE Key_name = 'PRIMARY'";
$prime = mysql_query($primarykey);
$pkey = mysql_fetch_array($prime);
$updatefield .= "]";
echo $updatefield = str_replace(", ]"," WHERE ".$pkey['Column_name']." = '".$t_ids[$n]."'",$updatefield);
}
在这里,我通过从最初描述的表(这里称为auditrail表)中获取记录来创建Update查询。
最佳答案
select * from TableName order by id DESC LIMIT 1
已编辑。是的,如果您想通过“ changedonetime” USE订购,可以。
select * from TableName order by changedonetime DESC LIMIT 1