如何在MYSQL中获得第二个MAXIMUM DATE [英] How to get second MAXIMUM DATE in MYSQL

问题描述

我想从mysql数据库中获取记录.我想从记录中获取第二个最大日期.但是我失败了

I want to fetch my record from mysql db. I want to fetch second maximum date from the record. But i failed

这是我的代码

    <?php
include ("connection.php");
$q_opinion="SELECT r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
    LEFT join
(
  select max(notification_date) notification_date,
    client_id
  from og_ratings
  WHERE notification_date NOT IN (select max(notification_date) FROM og_ratings )
   ) r2
  on r.notification_date = r2.notification_date
  and r.client_id = r2.client_id
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id  IN (SELECT opinion_id FROM pacra_client_opinion_relations WHERE client_id = 50)

";
$result = mysql_query($q_opinion) or die;
$rating = array();
while($row = mysql_fetch_assoc($result))
{
  $rating[] = $row['client_id'];
  $action[] = $row['atitle'];
  $opinion[] = $row['opinion'];
  $date[] = $row['notification_date'];
  $lrating[] = $row['ltitle'];
  $srating[] = $row['stitle'];
}
for ($i=0; $i<count($rating); $i++) {
    if ($rating[$i] == "")continue;
     ?>
    <table border="1">
    <tr>
          <td><?= $rating[$i] ?> </td>
           <td><?= $date[$i] ?> </td>
          <td><?= $opinion[$i] ?> </td>
         <td><?= $action[$i] ?> </td>
          <td><?= $lrating[$i] ?> </td>
           <td><?= $srating[$i] ?> </td>
    </tr>
    </table>
<?php   
}
?>

这是此代码的输出

在输出图像中，您可以看到它从db获取所有记录.但是我只想获取具有第二个最大日期的数据.

In Output image you can see that it fetch all records from db. But i want to fetch only that data which have second maximum date.

我该怎么做?

推荐答案

阅读查询并不有趣，但我认为问题出在这里:

It wasn't fun to read your query, but I think the problem is here:

LEFT JOIN (
  SELECT max(notification_date) notification_date, client_id
  FROM og_ratings
  WHERE notification_date NOT IN (
    SELECT max(notification_date)
    FROM og_ratings
)

如果您想为每个客户提供最长时间，则需要GROUP BY client_id:

if you want the maximum date for every client you need to GROUP BY client_id:

SELECT client_id, max(notification_date) notification_date
FROM og_ratings
GROUP BY client_id

如果您想要第二个最大值，则有很少的选择，我使用的是一个更容易理解的选择，但不一定是性能最高的选择:

if you want the second maximum there are few options, I'm using this one which is easier to understand but it's not necessarily the most performant:

SELECT client_id, max(notification_date) notification_date
FROM og_ratings
WHERE
  (client_id, notification_date) NOT IN (
    SELECT client_id, max(notification_date)
    FROM og_ratings GROUP BY client_id
  )
GROUP BY client_id

第三个问题，您使用的是LEFT JOIN，这意味着您将返回og_ratings中的所有值，无论它们是否是第二个最大值.在这种情况下使用INNER JOIN:

third problem, you're using a LEFT JOIN which means that you will return all values from og_ratings regardless if they are the second maximum or not. Use INNER JOIN on this context:

SELECT
  r.client_id,
  c.id,
  t.id,
  ..etc...
FROM
  og_ratings r INNER JOIN (
    SELECT client_id, max(notification_date) notification_2nd_date
    FROM og_ratings
    WHERE
      (client_id, notification_date) NOT IN (
        SELECT client_id, max(notification_date)
        FROM og_ratings GROUP BY client_id
      )
    GROUP BY client_id
   ) r2
  ON r.notification_date = r2.notification_2nd_date
     AND r.client_id = r2.client_id
  LEFT JOIN og_companies c ON r.client_id = c.id
  LEFT JOIN og_rating_types t ON r.rating_type_id = t.id
  LEFT JOIN og_actions a ON r.pacra_action = a.id
  LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id
  LEFT JOIN og_lterms l ON r.pacra_lterm = l.id
  LEFT JOIN og_sterms s ON r.pacra_sterm = s.id
  LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id
  LEFT JOIN pacra_clients pc ON pc.id = pr.client_id
  LEFT JOIN city ON city.id = pc.head_office_id
WHERE
  r.client_id IN (
    SELECT opinion_id FROM pacra_client_opinion_relations
    WHERE client_id = 50
  )

这篇关于如何在MYSQL中获得第二个MAXIMUM DATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！