我在mysql数据库有2个表。
)公司cid company_name
===================
1 AstraZeneca
2 Emirates
3 Development Bank of Singapore
4 Royal Copenhagen
5 xxx
6 xxx
hid user_id view_id is_save mark_as view date
==============================================================
1 2 2 0 3 2016-08-25 22:06:12
2 3 3 1 3 2016-08-25 22:07:12
3 3 3 0 1 2016-08-25 22:08:12
4 3 2 0 1 2016-08-25 22:09:12
5 2 4 0 1 2016-08-25 22:10:12
6 4 5 0 1 2016-08-25 22:11:12
7 4 6 0 1 2016-08-25 22:12:12
此view_id包含cid值。
现在，我总是想显示company表中最近的5个company_name，以升序基于history表view_id。
为此，我正在执行以下查询。但是company_name没有显示ASC或DESC的顺序
下面是查询:
$getViewID3 = mysqli_query($link, "SELECT view_id, hid, is_save FROM history WHERE user_id = '$user_id' AND mark_as = 3 GROUP BY view_id ORDER BY view_date DESC LIMIT 5 ");
if(mysqli_num_rows($getViewID3) > 0 ) {
while( $fetchViewId3 = mysqli_fetch_array($getViewID3) ) {
$viewid3 = (int) $fetchViewId3['view_id'];
$hid3 = (int) $fetchViewId3['hid'];
$is_save3 = (int) $fetchViewId3['is_save'];
$getCompany = mysqli_query($link, "SELECT company_name FROM company WHERE cid = '$viewid3' ORDER BY company_name DESC");
if(mysqli_num_rows($getCompany) > 0 ) {
while ($fetchCompany2 = mysqli_fetch_array($getCompany)) {
$cName = htmlspecialchars($fetchCompany2['company_name']);
$url_link = "{$url}company.php?cid=$viewid";
if($is_save3 == 1) {
$checked = 'checked = "checked"';
} else {
$checked = '';
}
echo "<li><a onClick='window.document.location='"$url_link'"'> $cName </a> <input type='checkbox' class='data_save' $checked data-hid='$hid' data-saveid='$viewid3' name='save_history'></li>";
}
}
}
例如:结果显示:A, E, D, R, L字母顺序。它应该显示:A, D, E, L, R字母顺序从company_name列。
如果我没有误解的话:
SELECT
C.company_name
FROM company C
INNER JOIN
(
SELECT
view_id,
MAX(view_date) max_view_date
FROM history
WHERE is_save IN (0,1) AND mark_as = 3
GROUP BY view_id
ORDER BY max_view_date DESC
LIMIT 5
) AS t
ON C.cid = t.view_id
ORDER BY C.company_name ASC;
注意:
由于您想要最新的5家公司，下面的查询将把最后一个view_date放在view_id旁边。
max_view_date的降序顺序对这些行进行排序，然后将结果限制为5，那么您将从内部查询中获得最多5个view_id。稍后，在此结果集和公司表之间使用一个简单的INNER JOIN将完成这项工作。
对不起，将最终结果按公司名称升序排序将完成工作。
编辑:为了从history表中获取所有列，从company表中获取company_name列:
SELECT
C.company_name,
t.*
FROM company C
INNER JOIN
(
SELECT
history.*
FROM history
INNER JOIN
(
SELECT
view_id,
MAX(view_date) max_view_date
FROM history
WHERE is_save IN (0,1) AND mark_as = 3
GROUP BY view_id
ORDER BY max_view_date DESC
LIMIT 5
) AS latestHistory
ON history.view_id = latestHistory.view_id AND history.view_date = latestHistory.max_view_date
) AS t
ON C.cid = t.view_id
ORDER BY C.company_name ASC;