mysql 查询重写_MySQL：在FROM子句中用相关子查询重写MSSQL？

这是我用来解决这个问题的PHP。理想情况下，我希望这一切都由MySQL完成(如果可以更快完成)。我只发布这个作为进一步澄清任务：

function getUniqueUsage($field = 'page_id', $since = 90){

//we need to add 90 days onto our date range for the 90-day sum

$sinceSeconds = mktime(0, 0, 0, $m , $d, $y) - (($sinceDays + 90) * (60 * 60 * 24));

//==> omitting mySQL connection details<==

$sql = "SELECT DISTINCT From_unixtime(time,'%Y-%m-%d') AS date, $field FROM perflog WHERE time > $sinceSeconds ORDER BY date" ;

$sql_results = mysql_query($sql);

$results = array();

//all page ids per date (ending-up with only unique date keys)

while ($row = mysql_fetch_assoc($sql_results))

{

$results[$row['date']][] = $row[$field];

}

$sums = array();

//initialize sum array, with only unique dates (days)

foreach (array_keys($results) as $date){

$sums[$date] = array(0,0,0);

}

//calculate the 30/60/90 day unique pages for each day

foreach (array_keys($sums) as $ref_date){

$merges30 = array();

$merges60 = array();

$merges90 = array();

$ref_time = strtotime($ref_date);

$ref_minus_30 = strtotime("-30 Days",$ref_time);

$ref_minus_60 = strtotime("-60 Days",$ref_time);

$ref_minus_90 = strtotime("-90 Days",$ref_time);

foreach ($results as $result_date => $pages){

$compare_time = strtotime($result_date);

if ($compare_time >= $ref_minus_30 && $compare_time <= $ref_time){

$merges30 = array_merge($merges30, $pages);

}

if ($compare_time >= $ref_minus_60 && $compare_time <= $ref_time){

$merges60 = array_merge($merges60, $pages);

}

if ($compare_time >= $ref_minus_90 && $compare_time <= $ref_time){

$merges90 = array_merge($merges90, $pages);

}

}

$sums[$ref_date] = array(count(array_unique($merges30)),count(array_unique($merges60)),count(array_unique($merges90)));

}

//truncate to only specified number of days

return array_slice($sums,-$since, $since, true);

}正如您所看到的，很多不幸的数组合并和数组唯一性。