Created
August 4, 2024 09:00
-
-
Save okumurakengo/367bce11e11d88873824ae27f16cf0f6 to your computer and use it in GitHub Desktop.
mysql 5.7 と php で複数カラムから RANK() 相当の select
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE foo ( | |
| id INT AUTO_INCREMENT PRIMARY KEY, | |
| name VARCHAR(100), | |
| point INT, | |
| cnt INT, | |
| num INT | |
| ); | |
| INSERT INTO foo (name, point, cnt, num) VALUES | |
| ('Alice',30,50,20), | |
| ('Bob',40,60,10), | |
| ('Charlie',50,70,30), | |
| ('Dave',20,30,40), | |
| ('Eve',60,80,50), | |
| ('Frank',70,90,60), | |
| ('Grace',80,10,70), | |
| ('Hank',90,20,80), | |
| ('Ivy',40,50,20), | |
| ('Jack',50,70,30), | |
| ('Kara',60,70,40), | |
| ('Leo',70,80,50), | |
| ('Mona',80,70,60), | |
| ('Nate',90,10,70), | |
| ('Oscar',30,20,80), | |
| ('Paul',20,30,10), | |
| ('Quinn',50,40,20), | |
| ('Rita',60,50,30), | |
| ('Sam',70,60,40), | |
| ('Tom',80,70,50), | |
| ('Uma',90,80,60), | |
| ('Vera',30,90,70), | |
| ('Will',40,10,80), | |
| ('Xena',50,20,10), | |
| ('Yara',60,30,20), | |
| ('Zach',70,40,30); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| declare(strict_types=1); | |
| require __DIR__ . 'sample/path/vendor/autoload.php'; | |
| use App\DB\DBAccess; | |
| $db = DBAccess::singleton(); | |
| $order_list = [ | |
| 'point' => 'desc', | |
| 'cnt' => 'asc', | |
| 'num' => 'desc', | |
| ]; | |
| $sqlColumns = []; | |
| $sameRankSql = ''; | |
| foreach ($order_list as $column => $sort) { | |
| if (strtoupper($sort) === 'ASC') { | |
| $sqlColumns[] = "({$sameRankSql} f2.{$column} < f1.{$column})"; | |
| } else { | |
| $sqlColumns[] = "({$sameRankSql} f2.{$column} > f1.{$column})"; | |
| } | |
| $sameRankSql .= "f2.{$column} = f1.{$column} AND "; | |
| } | |
| $subQuery = implode(' OR ', $sqlColumns); | |
| $res = $db->executeSqlAndFetchAll( | |
| $sql = "SELECT | |
| f1.id, | |
| f1.name, | |
| f1.point, | |
| f1.cnt, | |
| f1.num, | |
| ( | |
| SELECT COUNT(*) + 1 | |
| FROM foo f2 | |
| WHERE {$subQuery} | |
| ) AS tmp_rank | |
| FROM foo f1 | |
| ORDER BY tmp_rank" | |
| ); | |
| var_dump($res); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment