DROP TABLE IF EXISTS `test`.`myMatch`;
CREATE TABLE `test`.`myMatch` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`tournament_id` int(10) null,
`rounded` int(10) null,
`sequence` int(10) null ,
`player` varchar(100) null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=tis620;
insert into myMatch( `tournament_id` ,`rounded` ,`sequence` ,`player`)
VALUES
(1,1,1,'A') ,
(1,1,1,'B') ,
(1,1,2,'C') ,
(1,1,2,'D') ,
(1,1,3,'E') ,
(1,1,3,'F') ,
(1,2,1,'A') ,
(1,2,1,'C') ,
(1,2,2,'B') ,
(1,2,2,'E') ,
(1,2,3,'D') ,
(1,2,3,'F') ;
select * from `myMatch` ;
-- PKEY REF BY ALL KEY IN TABLE
select Q2.* ,'\n'
FROM
(
SELECT * from `myMatch`
WHERE `Player`='A'
) AS Q1
LEFT JOIN `myMatch` AS Q2
ON (Q1.`tournament_id`=q2.`tournament_id`)
AND (q1.`rounded` = q2.`rounded`)
AND (q1.`sequence` = q2.`sequence`)
AND (q1.`player` <> q2.`player`)
ORDER BY q2.`tournament_id` ,q2.`rounded` ,q2.`sequence`
INTO OUTFILE 'd:\mymatch.output..txt';
-- ที่เอาท์พุทออกไฟล์เพราะ ไม่อยาก capscreen ให้ดูค่ะ ตอนนี้ run สารพัดเดวเครื่องจะ hang
ผลลัพธ์
0000000042 1 1 1 B
0000000048 1 2 1 C
และชุดใหญ่
Code (PHP)
SELECT qPlayer.player
,qR1comp.Opponent AS `O1`
,qR2comp.Opponent AS `O2`
FROM
(
SELECT DISTINCT `player` from `mymatch`
) AS qPlayer
LEFT JOIN
(
-- **************************************
-- FIND PLAYER VS OPPENET WITH ROUNDED 01
-- **************************************
SELECT qR1Player.player AS player
,QOpponentR1.player AS `Opponent`
FROM
(
SELECT DISTINCT player from mymatch
) AS qR1Player
LEFT JOIN
(
SELECT * FROM `myMatch`
WHERE `rounded`= 1
) AS QR1
ON (qR1Player.player = QR1.player)
LEFT JOIN
(
SELECT * FROM `myMatch`
WHERE `rounded`= 1
) AS QOpponentR1
ON (qR1Player.player <> QOpponentR1.player)
AND (QR1.player <> QOpponentR1.player)
AND (QR1.sequence = QOpponentR1.sequence)
-- **************************************
) AS qR1Comp
ON (qPlayer.Player = qR1Comp.player)
LEFT JOIN
(
-- **************************************
-- FIND PLAYER VS OPPENET WITH ROUNDED 02
-- **************************************
SELECT qR2Player.player AS player
,QOpponentR2.player AS `Opponent`
FROM
(
SELECT DISTINCT player from mymatch
) AS qR2Player
LEFT JOIN
(
SELECT * FROM `myMatch`
WHERE `rounded`= 2
) AS QR2
ON (qR2Player.player = QR2.player)
LEFT JOIN
(
SELECT * FROM `myMatch`
WHERE `rounded`= 2
) AS QOpponentR2
ON (qR2Player.player <> QOpponentR2.player)
AND (QR2.player <> QOpponentR2.player)
AND (QR2.sequence = QOpponentR2.sequence)
-- **************************************
) AS qR2Comp
ON (qPlayer.Player = qR2Comp.player)
INTO OUTFILE 'd:\player and opponent.txt' ;