|
/*
mysql> Drop table Transactions;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE Transactions (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> OrderID INT NOT NULL,
-> DVDID SMALLINT NOT NULL,
-> DateOut DATE NOT NULL,
-> DateDue DATE NOT NULL,
-> DateIn DATE NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
-> VALUES (1, 1, CURDATE(), CURDATE()+3),
-> (1, 4, CURDATE(), CURDATE()+1),
-> (1, 8, CURDATE(), CURDATE()+2),
-> (2, 3, CURDATE(), CURDATE()+3),
-> (3, 4, CURDATE(), CURDATE()+4),
-> (3, 1, CURDATE(), CURDATE()+5),
-> (3, 7, CURDATE(), CURDATE()+6),
-> (4, 4, CURDATE(), CURDATE()+7),
-> (5, 3, CURDATE(), CURDATE()+8),
-> (6, 2, CURDATE(), CURDATE()+7),
-> (6, 1, CURDATE(), CURDATE()+6),
-> (7, 4, CURDATE(), CURDATE()+6),
-> (8, 2, CURDATE(), CURDATE()+5),
-> (8, 1, CURDATE(), CURDATE()+4),
-> (8, 3, CURDATE(), CURDATE()+4),
-> (9, 7, CURDATE(), CURDATE()+3),
-> (9, 1, CURDATE(), CURDATE()+3),
-> (10, 5, CURDATE(), CURDATE()+1),
-> (11, 6, CURDATE(), CURDATE()+2),
-> (11, 2, CURDATE(), CURDATE()+6),
-> (11, 8, CURDATE(), CURDATE()+5),
-> (12, 5, CURDATE(), CURDATE()+4),
-> (13, 7, CURDATE(), CURDATE()+3);
Query OK, 23 rows affected (0.06 sec)
Records: 23 Duplicates: 0 Warnings: 0
mysql> select * from Transactions;
+----+---------+-------+------------+------------+------------+
| ID | OrderID | DVDID | DateOut | DateDue | DateIn |
+----+---------+-------+------------+------------+------------+
| 1 | 1 | 1 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 2 | 1 | 4 | 2005-10-08 | 2005-10-09 | 0000-00-00 |
| 3 | 1 | 8 | 2005-10-08 | 2005-10-10 | 0000-00-00 |
| 4 | 2 | 3 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 5 | 3 | 4 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 6 | 3 | 1 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
| 7 | 3 | 7 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 8 | 4 | 4 | 2005-10-08 | 2005-10-15 | 0000-00-00 |
| 9 | 5 | 3 | 2005-10-08 | 2005-10-16 | 0000-00-00 |
| 10 | 6 | 2 | 2005-10-08 | 2005-10-15 | 0000-00-00 |
| 11 | 6 | 1 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 12 | 7 | 4 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 13 | 8 | 2 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
| 14 | 8 | 1 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 15 | 8 | 3 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 16 | 9 | 7 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 17 | 9 | 1 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 18 | 10 | 5 | 2005-10-08 | 2005-10-09 | 0000-00-00 |
| 19 | 11 | 6 | 2005-10-08 | 2005-10-10 | 0000-00-00 |
| 20 | 11 | 2 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 21 | 11 | 8 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
| 22 | 12 | 5 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 23 | 13 | 7 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
+----+---------+-------+------------+------------+------------+
23 rows in set (0.01 sec)
mysql> SELECT OrderID, ID, DVDID,
-> CONVERT(DateOut, UNSIGNED) AS DateOut_INT
-> FROM Transactions
-> WHERE DVDID=4 OR DVDID=5 OR DVDID=7
-> ORDER BY OrderID, ID, DVDID;
+---------+----+-------+-------------+
| OrderID | ID | DVDID | DateOut_INT |
+---------+----+-------+-------------+
| 1 | 2 | 4 | 20051008 |
| 3 | 5 | 4 | 20051008 |
| 3 | 7 | 7 | 20051008 |
| 4 | 8 | 4 | 20051008 |
| 7 | 12 | 4 | 20051008 |
| 9 | 16 | 7 | 20051008 |
| 10 | 18 | 5 | 20051008 |
| 12 | 22 | 5 | 20051008 |
| 13 | 23 | 7 | 20051008 |
+---------+----+-------+-------------+
9 rows in set (0.00 sec)
*/
Drop table Transactions;
CREATE TABLE Transactions (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
OrderID INT NOT NULL,
DVDID SMALLINT NOT NULL,
DateOut DATE NOT NULL,
DateDue DATE NOT NULL,
DateIn DATE NOT NULL
)
ENGINE=INNODB;
INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
VALUES (1, 1, CURDATE(), CURDATE()+3),
(1, 4, CURDATE(), CURDATE()+1),
(1, 8, CURDATE(), CURDATE()+2),
(2, 3, CURDATE(), CURDATE()+3),
(3, 4, CURDATE(), CURDATE()+4),
(3, 1, CURDATE(), CURDATE()+5),
(3, 7, CURDATE(), CURDATE()+6),
(4, 4, CURDATE(), CURDATE()+7),
(5, 3, CURDATE(), CURDATE()+8),
(6, 2, CURDATE(), CURDATE()+7),
(6, 1, CURDATE(), CURDATE()+6),
(7, 4, CURDATE(), CURDATE()+6),
(8, 2, CURDATE(), CURDATE()+5),
(8, 1, CURDATE(), CURDATE()+4),
(8, 3, CURDATE(), CURDATE()+4),
(9, 7, CURDATE(), CURDATE()+3),
(9, 1, CURDATE(), CURDATE()+3),
(10, 5, CURDATE(), CURDATE()+1),
(11, 6, CURDATE(), CURDATE()+2),
(11, 2, CURDATE(), CURDATE()+6),
(11, 8, CURDATE(), CURDATE()+5),
(12, 5, CURDATE(), CURDATE()+4),
(13, 7, CURDATE(), CURDATE()+3);
select * from Transactions;
SELECT OrderID, ID, DVDID,
CONVERT(DateOut, UNSIGNED) AS DateOut_INT
FROM Transactions
WHERE DVDID=4 OR DVDID=5 OR DVDID=7
ORDER BY OrderID, ID, DVDID;
|
