0

## sql查詢問題

``````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 &lt;= '07:00') and (b.time >= '05:00' and b.time &lt;= '07:00');
``````

1.分析需求
2.設計UI
3.設計資料表
4.寫程式

Beginner iT邦新手 3 級 ‧ 2013-12-19 20:59:39 檢舉

### 2 個回答

12

iT邦高手 1 級 ‧ 2013-12-18 21:39:58

``````&lt;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 | 假日    |
+----------+-------+------+----------+------+----------+---------+

``````

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

a部分：SELECT 增加no WHERE限制條件加上時間範圍，ORDER BY no
b部分：SELECT 增加no WHERE限制條件加上時間範圍，ORDER BY no

Table不得不而作的.只要輸入不是按照順序,就無法正確匹配.

http://ithelp.ithome.com.tw/question/10136053

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

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

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

xrdairlg提到：