高手幫忙看下怎么解決，無奈sql學的太爛，試了一天沒結果。-有解無憂

我想查詢一個表，結構如下，
運行了limit 30的結果。

mysql> select * from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' limit 30;
+-----------+---------------------+------------+---------------+---------------+---------------+----------------------------------+-----------------+
| f_matchID | f_history_time      | f_typeData | f_result_home | f_result_draw | f_result_away | f_history_id                     | f_match_nowdata |
+-----------+---------------------+------------+---------------+---------------+---------------+----------------------------------+-----------------+
| 1001559   | 2018-03-26 12:00:00 | okooo      | 0.73          | 0.82          | 0.89          | 4028813e6265355f01626586746e0116 | 180401期        |
| 1001559   | 2018-03-26 13:00:00 | okooo      | 0.77          | 0.82          | 0.89          | 4028813e6265355f01626586746e0117 | 180401期        |
| 1001559   | 2018-03-26 14:00:00 | okooo      | 0.77          | 0.84          | 0.89          | 4028813e6265355f01626586746e0118 | 180401期        |
| 1001559   | 2018-03-26 15:00:00 | okooo      | 0.78          | 0.83          | 0.89          | 4028813e6265355f01626586746e0119 | 180401期        |
| 1001559   | 2018-03-26 16:00:00 | okooo      | 0.75          | 0.84          | 0.89          | 4028813e6265355f01626586746e011a | 180401期        |
| 1001559   | 2018-03-26 17:00:00 | okooo      | 0.72          | 0.81          | 0.85          | 4028813e6265355f01626586746e011b | 180401期        |
| 1001559   | 2018-03-26 18:00:00 | okooo      | 0.72          | 0.8           | 0.86          | 4028813e6265355f01626586746e011c | 180401期        |
| 1001559   | 2018-03-26 19:00:00 | okooo      | 0.72          | 0.81          | 0.86          | 4028813e6265355f01626586746e011d | 180401期        |
| 1001559   | 2018-03-26 20:00:00 | okooo      | 0.69          | 0.83          | 0.87          | 4028813e6265355f01626586746e011e | 180401期        |
| 1001559   | 2018-03-26 21:00:00 | okooo      | 0.66          | 0.77          | 0.85          | 4028813e6265355f01626586746e011f | 180401期        |
| 1001559   | 2018-03-26 22:00:00 | okooo      | 0.67          | 0.75          | 0.85          | 4028813e6265355f01626586746e0120 | 180401期        |
| 1001559   | 2018-03-26 23:00:00 | okooo      | 0.73          | 0.73          | 0.85          | 4028813e6265355f01626586746e0121 | 180401期        |
| 1001559   | 2018-03-27 00:00:00 | okooo      | 0.75          | 0.77          | 0.85          | 4028813e6265355f01626586746e0122 | 180401期        |
| 1001559   | 2018-03-27 01:00:00 | okooo      | 0.72          | 0.78          | 0.86          | 4028813e6265355f01626586746e0123 | 180401期        |
| 1001559   | 2018-03-27 02:00:00 | okooo      | 0.68          | 0.8           | 0.86          | 4028813e6265355f01626586746e0124 | 180401期        |
| 1001559   | 2018-03-27 03:00:00 | okooo      | 0.68          | 0.8           | 0.88          | 4028813e6265355f01626586746e0125 | 180401期        |
| 1001559   | 2018-03-27 04:00:00 | okooo      | 0.71          | 0.8           | 0.86          | 4028813e6265355f01626586746e0126 | 180401期        |
| 1001559   | 2018-03-27 05:00:00 | okooo      | 0.7           | 0.79          | 0.88          | 4028813e6265355f01626586746e0127 | 180401期        |
| 1001559   | 2018-03-27 06:00:00 | okooo      | 0.71          | 0.78          | 0.87          | 4028813e6265355f01626586746e0128 | 180401期        |
| 1001559   | 2018-03-27 07:00:00 | okooo      | 0.65          | 0.78          | 0.86          | 4028813e6265355f01626586746e0129 | 180401期        |
| 1001559   | 2018-03-27 08:00:00 | okooo      | 0.65          | 0.78          | 0.87          | 4028813e6265355f01626586746e012a | 180401期        |
| 1001559   | 2018-03-27 09:00:00 | okooo      | 0.65          | 0.79          | 0.87          | 4028813e6265355f01626586746e012b | 180401期        |
| 1001559   | 2018-03-27 10:00:00 | okooo      | 0.69          | 0.77          | 0.87          | 4028813e6265355f01626586746e012c | 180401期        |
| 1001565   | 2018-03-26 12:00:00 | okooo      | 0.91          | 0.89          | 0.84          | 4028813e6265355f01626586746e0144 | 180401期        |
| 1001565   | 2018-03-26 13:00:00 | okooo      | 0.91          | 0.89          | 0.84          | 4028813e6265355f01626586746e0145 | 180401期        |
| 1001565   | 2018-03-26 14:00:00 | okooo      | 0.91          | 0.89          | 0.84          | 4028813e6265355f01626586746e0146 | 180401期        |
| 1001565   | 2018-03-26 15:00:00 | okooo      | 0.91          | 0.89          | 0.85          | 4028813e6265355f01626586746e0147 | 180401期        |
| 1001565   | 2018-03-26 16:00:00 | okooo      | 0.91          | 0.89          | 0.84          | 4028813e6265355f01626586746e0148 | 180401期        |
| 1001565   | 2018-03-26 17:00:00 | okooo      | 0.91          | 0.86          | 0.79          | 4028813e6265355f01626586746e0149 | 180401期        |
| 1001565   | 2018-03-26 18:00:00 | okooo      | 0.9           | 0.86          | 0.8           | 4028813e6265355f01626586746e014a | 180401期        |
+-----------+---------------------+------------+---------------+---------------+---------------+----------------------------------+-----------------+
30 rows in set

我現在想查詢，f_match_nowdata='https://bbs.csdn.net/topics/180401期'  全部400多條記錄，只查詢每條ID最新更新時間的一條，要一次查詢完當前400多條記錄，這個要怎么寫sql哇，感覺好難。
f_matchID重復很多，原因是每一小時會有1條f_history_time資料增加進來。

where條件  f_typeData=https://bbs.csdn.net/topics/‘okooo’ f_match_nowdata='https://bbs.csdn.net/topics/180401期'

請大神不吝賜教。分不夠可以加。

uj5u.com熱心網友回復：

我結合從資料庫取到的資料，存到java的hashmap，來實作需求

在你的sql陳述句后加上對日期的增序排序
select * from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' order by f_typeData asc;

將獲取的資料，存到hashmap的key= f_matchID ，value=https://bbs.csdn.net/topics/ 物件（ t_history_data_dan）。這樣就會有唯一的key，和最新日期的記錄

uj5u.com熱心網友回復：

那就是說，除了hasmap之外沒有別的好辦法直接查詢了？你說的這個方法，我不太會。我百度了下，好像vb.net沒有hasmap之類的東西。
比較難搞啊。。。

參考 1 樓 weixin_36799819 的回復:

我結合從資料庫取到的資料，存到java的hashmap，來實作需求

在你的sql陳述句后加上對日期的增序排序
select * from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' order by f_typeData asc;

將獲取的資料，存到hashmap的key= f_matchID ，value=https://bbs.csdn.net/topics/ 物件（ t_history_data_dan）。這樣就會有唯一的key，和最新日期的記錄

uj5u.com熱心網友回復：

首先參考：https://zhidao.baidu.com/question/321036621.htm

看自己能否寫出
以下是我寫的：

SELECT 

    *

FROM

    t_history_data_dan X,

    (SELECT 

        f_matchID, MAX(f_history_time) AS f_history_time

    FROM

        t_history_data_dan

    GROUP BY f_matchID) Y

WHERE

    X.f_matchID = Y.f_matchID

        AND X.t_history_data_dan = Y.t_history_data_dan;

uj5u.com熱心網友回復：

思路感覺沒什么問題了，調整了一下
select * from t_history_data_dan,
(select f_matchId,max(f_history_time) as bb from  t_history_data_dan group by f_matchId ) z
where  t_history_data_dan.f_matchId=z.f_matchId and  t_history_data_dan.f_history_time=z.bb

+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
| f_matchID | f_history_time      | f_typeData    | f_result_home | f_result_draw | f_result_away | f_history_id                     | f_match_nowdata | f_matchId | bb                  |
+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
| 1000451   | 2018-01-05 00:00:00 | okooo         | 0.7           | 0.72          | 0.4           | 2c907cb460c1605d0160c247d8b50084 | 180102期        | 1000451   | 2018-01-05 00:00:00 |
| 1000451   | 2018-01-05 00:00:00 | okoooexponent | 18.76         | 6.48          | 8.54          | 2c907cb460c1605d0160c247d8b50085 | 180102期        | 1000451   | 2018-01-05 00:00:00 |
| 1000456   | 2018-01-05 00:00:00 | okooo         | 0.74          | 0.78          | 0.69          | 2c907cb460c1605d0160c247d8b50086 | 180102期        | 1000456   | 2018-01-05 00:00:00 |
| 1000456   | 2018-01-05 00:00:00 | okoooexponent | 5.92          | 3.46          | 7.21          | 2c907cb460c1605d0160c247d8b50087 | 180102期        | 1000456   | 2018-01-05 00:00:00 |
+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
4 rows in set

但是結果中如何篩選 f_typedata 和 f_match_nowdata ?? 我把條件加上去之后直接查不到了。。。

參考 3 樓 weixin_36799819 的回復:

首先參考：https://zhidao.baidu.com/question/321036621.htm

看自己能否寫出
以下是我寫的：

SELECT 

    *

FROM

    t_history_data_dan X,

    (SELECT 

        f_matchID, MAX(f_history_time) AS f_history_time

    FROM

        t_history_data_dan

    GROUP BY f_matchID) Y

WHERE

    X.f_matchID = Y.f_matchID

        AND X.t_history_data_dan = Y.t_history_data_dan;

uj5u.com熱心網友回復：

篩選?

參考 4 樓 jupiterabc 的回復:

思路感覺沒什么問題了，調整了一下
select * from t_history_data_dan,
(select f_matchId,max(f_history_time) as bb from  t_history_data_dan group by f_matchId ) z
where  t_history_data_dan.f_matchId=z.f_matchId and  t_history_data_dan.f_history_time=z.bb

+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
| f_matchID | f_history_time      | f_typeData    | f_result_home | f_result_draw | f_result_away | f_history_id                     | f_match_nowdata | f_matchId | bb                  |
+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
| 1000451   | 2018-01-05 00:00:00 | okooo         | 0.7           | 0.72          | 0.4           | 2c907cb460c1605d0160c247d8b50084 | 180102期        | 1000451   | 2018-01-05 00:00:00 |
| 1000451   | 2018-01-05 00:00:00 | okoooexponent | 18.76         | 6.48          | 8.54          | 2c907cb460c1605d0160c247d8b50085 | 180102期        | 1000451   | 2018-01-05 00:00:00 |
| 1000456   | 2018-01-05 00:00:00 | okooo         | 0.74          | 0.78          | 0.69          | 2c907cb460c1605d0160c247d8b50086 | 180102期        | 1000456   | 2018-01-05 00:00:00 |
| 1000456   | 2018-01-05 00:00:00 | okoooexponent | 5.92          | 3.46          | 7.21          | 2c907cb460c1605d0160c247d8b50087 | 180102期        | 1000456   | 2018-01-05 00:00:00 |
+-----------+---------------------+---------------+---------------+---------------+---------------+----------------------------------+-----------------+-----------+---------------------+
4 rows in set

但是結果中如何篩選 f_typedata 和 f_match_nowdata ?? 我把條件加上去之后直接查不到了。。。
Quote: 參考 3 樓 weixin_36799819 的回復:
首先參考：https://zhidao.baidu.com/question/321036621.htm

看自己能否寫出
以下是我寫的：
SELECT 

    *

FROM

    t_history_data_dan X,

    (SELECT 

        f_matchID, MAX(f_history_time) AS f_history_time

    FROM

        t_history_data_dan

    GROUP BY f_matchID) Y

WHERE

    X.f_matchID = Y.f_matchID

        AND X.t_history_data_dan = Y.t_history_data_dan;

uj5u.com熱心網友回復：

還是有問題

uj5u.com熱心網友回復：

select * from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' and f_history_time = (select max(f_history_time ) from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' ) limit 30;

uj5u.com熱心網友回復：

select max(f_matchID),f_history_time,f_typeData,f_result_home,f_result_draw,f_result_away,f_history_id,f_match_nowdata
from t_history_data_dan where f_match_nowdata='https://bbs.csdn.net/topics/180401期' and f_typeData='https://bbs.csdn.net/topics/okooo' group by f_match_nowdata

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92385.html

標籤：MySQL

上一篇：新手求助資料亂碼問題

下一篇：MySql資料庫設計