一个
INNER JOIN就足够了您的需求。MySQL没有
PIVOT功能，您仍然可以使用
CASE和
MAX()功能对其进行仿真。
SELECt a.ID, a.NAME, MAX(CASE WHEN b.Race_Number = 1 THEN b.Place ELSE NULL END) Race1, MAX(CASE WHEN b.Race_Number = 2 THEN b.Place ELSE NULL END) Race2, MAX(CASE WHEN b.Race_Number = 3 THEN b.Place ELSE NULL END) Race3FROM Table1 a INNER JOIN Table2 b ON a.ID = b.IDGROUP BY a.ID, a.Name
但是，如果您不知道的数量
RACE，则
DYNAMIC SQL首选a。
SET @sql = NULL;SELECt GROUP_CONCAt(DISTINCT CONCAt('MAX(CASE WHEN b.Race_Number = ', Race_Number, ' THEN b.Place END) AS ', CONCAt('`Race', Race_Number, '`')) ) INTO @sqlFROM Table2;SET @sql = CONCAt('SELECt s.Student_name, ', @sql, ' FROM Table1 a LEFT JOIN Table2 b ON ON a.ID = b.ID GROUP BY a.ID, a.Name');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
