iT邦幫忙

0

sql查詢問題

各位IT前輩們好!請教Mysql查詢語法的問題:
自己利用假日時間寫簡易公車查詢網站,目前有遇到一個狀況
起點站:甲站,終點站:乙站,時間5:00至7:00之間,所有甲站到乙站的班次,並計算甲站到乙站的公車行駛時間
行駛時間,直接計算乙站發車時間 減 甲站發車時間
資料表,如下:

這樣寫的原因是為了可以在一個資料表,在同一個欄位去做時間相減

select timediff(b.time, a.time) as sub, a.class, a.stop, a.time, b.stop, b.time
from
(select * from sq = '1') as a,
(select * from sq = '5') as b
where (a.time >= '05:00' and a.time <= '07:00') and (b.time >= '05:00' and b.time <= '07:00');

自己用簡單的sql語法,先把table分成 a, b 兩個資料表,資料表 a 作為 甲站發車時間 資料來源; 資料表 b 作為 乙站發車時間 資料來源
把 a 和 b 所查詢的資料再作篩選,再顯示其最後查詢結果。當然,裡面條件設定就有些問題。

時間相減有做到,查詢結果是照我寫的跑出來一團亂,時間會出現負值,甲站和乙站都會重複相減,但是我內心不是這樣想啊

希望是這樣的結果:

自己有嘗試加入distinct 和 group by , 結果還是一樣會有重複的,而且也會出現負值。另外,讓沒有值的欄位顯示NULL,知道可以用JOIN LEFT,但是因為複雜sql還不會寫,所以針對我自己寫的sql不知道如改起。

請問上面自己寫的sql語法,可以用什麼方式或修改什麼部分,才能解決?(是否有建議相關學習網站,除了官方以外),感謝!

相對於sql查詢指令
我建議你先打好基礎比較重要
1.分析需求
2.設計UI
3.設計資料表
4.寫程式
你的1,2,3就我看起來還沒完全
就算寫出程式來
意義其實不大
Beginner iT邦新手 3 級 ‧ 2013-12-19 20:59:39 檢舉
基本上做這個是為練功用的,希望從中打基礎和學習,感謝 antijava的建議
要練功
也可以參考 5284

2 個回答

12
一級屠豬士
iT邦高手 1 級 ‧ 2013-12-18 21:39:58
最佳解答
<pre class="c" name="code">CREATE TABLE ithelp1218(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
class TINYINT UNSIGNED NOT NULL,
time TIME NOT NULL,
sq CHAR(1) NOT NULL,
stop CHAR(2) NOT NULL,
comment CHAR(4) NOT NULL
);
INSERT INTO ithelp1218(class, time, sq, stop, comment) VALUES
(10, '05:40', '1', '甲', '平日'),
(10, '06:10', '5', '乙', '平日'),
(10, '06:20', '1', '甲', '平日'),
(10, '06:50', '5', '乙', '平日'),
(10, '07:00', '1', '甲', '平日'),
(10, '07:30', '5', '乙', '平日'),
(10, '06:00', '1', '甲', '假日'),
(10, '06:30', '5', '乙', '假日'),
(10, '07:00', '1', '甲', '假日'),
(10, '07:30', '5', '乙', '假日');

SELECT TIMEDIFF(b.time, a.time) AS sub
     , a.class
     , a.stop
     , a.time
     , b.stop
     , b.time
     , comment
  FROM (SELECT @anum := @anum + 1 AS anum
             , class
             , stop
             , time
             , comment
          FROM ithelp1218
             , (SELECT @anum := 0) x
         WHERE sq = '1' 
         ORDER BY id ) a,
       (SELECT @bnum := @bnum + 1 AS bnum
             , stop
             , time
          FROM ithelp1218
             , (SELECT @bnum := 0) y
         WHERE sq = '5' 
         ORDER BY id ) b
 WHERE a.anum = b.bnum;

+----------+-------+------+----------+------+----------+---------+
| sub      | class | stop | time     | stop | time     | comment |
+----------+-------+------+----------+------+----------+---------+
| 00:30:00 |    10 | 甲   | 05:40:00 | 乙   | 06:10:00 | 平日    |
| 00:30:00 |    10 | 甲   | 06:20:00 | 乙   | 06:50:00 | 平日    |
| 00:30:00 |    10 | 甲   | 07:00:00 | 乙   | 07:30:00 | 平日    |
| 00:30:00 |    10 | 甲   | 06:00:00 | 乙   | 06:30:00 | 假日    |
| 00:30:00 |    10 | 甲   | 07:00:00 | 乙   | 07:30:00 | 假日    |
+----------+-------+------+----------+------+----------+---------+

第三組應該是有資料才對,不會NULL,你再確認看看.

這類問題解法都類似,像是人員進出,網路登錄時間等等.
但是樓主沒有設定正確的關係,sq 跟 stop 的功用好像重複了.
還是先不要急著寫系統吧,先把基本功練一練.

Beginner iT邦新手 3 級 ‧ 2013-12-19 21:36:07 檢舉

感謝 hitomitanaka 貼上完整sql , 拿去mysql執行,某些班次和時間無法匹配,所以時間差有負值,因為就如同slime所說少一項資料。在資料表加上一個欄位no,代表相同班次都代表一樣的數字。

修改你提供sql語法的子查詢SELECT
a部分:SELECT 增加no WHERE限制條件加上時間範圍,ORDER BY no
b部分:SELECT 增加no WHERE限制條件加上時間範圍,ORDER BY no
最後WHERE 再加上 a.no = b.no ORDER BY a.time

這樣列出的查詢時間會依a.time時間排序,並且對相同班次相減,查詢結果就正確時間差也沒有負值 (資料表還再看看哪裡有問題...),感謝

註:請問 SELECT @anum := @anum + 1 AS anum ... (SELECT @anum := 0) x 是類似迴圈,對每筆資料去找 WHERE制限條件 並且 依 id 去排序,因為這樣的寫法是第一次看到,google後大概知道是這樣,不知道是否正確?

你還是先把基本的學一學吧.
我寫的那個是因為你沒安排關聯,
所以我設法產生虛擬資料,依照輸入順序編號,只是為了你那個
Table不得不而作的.只要輸入不是按照順序,就無法正確匹配.

那是產生編號用的,可以參考我之前發表的:
http://ithelp.ithome.com.tw/question/10136053

6
slime
iT邦大師 1 級 ‧ 2013-12-18 20:48:59

看起來少了一項資料: 哪些甲到乙是同一個"班次"
比如: 5:40 在甲的車, 到乙是 6:10 或 6:50 呢?

這樣是同一個班次的車, 才能進行相減.

Beginner iT邦新手 3 級 ‧ 2013-12-19 21:07:38 檢舉

當初資料表設計有問題,沒有考慮到同一個"班次"的狀況,目前已加上,查詢結果似乎沒有問題(資料表還要再看看哪裡有問題,不然又會有狀況),感謝

pantc328 iT邦研究生 1 級 ‧ 2013-12-22 10:05:42 檢舉

xrdairlg提到:
行駛時間,直接計算乙站發車時間 減 甲站發車時間

行駛時間=到站時間-發車時間

如果所有的因素相同
就全部的時間Sum起來除以班次答案就出來

我要發表回答

立即登入回答