mysql联合/混合两个表

Question

我有一个查询如下:

SELECT ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score1,
    scoreanalysis.sectionName, 
    scoreanalysis.sectionID, 
    scoreanalysis.wave, 
    scoreanalysis.waveID

    FROM shopcategories
    INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
    INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID                      
    WHERE shopcategories.formatID = 52 AND locationcategories.isClient =  0 
    and scoreanalysis.waveID = 160
    GROUP BY scoreanalysis.sectionID 
    ORDER BY scoreanalysis.sectionID 

输出为

我还有其他查询，与上面相同，但有一点不同。

SELECT ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score2
FROM shopcategories
INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID                      
WHERE shopcategories.formatID = 52 AND locationcategories.isClient =  1 
and scoreanalysis.waveID = 160
GROUP BY scoreanalysis.sectionID 
ORDER BY scoreanalysis.sectionID 

我想要的是混合表格显示如下:

有什么可能的解决方案吗？任何帮助谢谢

Answer 1

您可以在单个查询中多次连接/引用同一个表，例如:

SELECT
scoreanalysis.sectionID, 
ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score1,

ROUND(SUM(CASE WHEN score2.waveID = 160 THEN score2.achievedScore ELSE 0 END)/SUM(CASE WHEN score2.waveID = 160 THEN score2.applicableScore ELSE 0 END)*100) AS score2

FROM shopcategories
INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID

-- Join a second time:
INNER JOIN locationcategories AS location2 ON shopcategories.ID = location2.shopcategoryID                          
INNER JOIN scoreanalysis AS score2 ON scoreanalysis.sectionID = score2.sectionID AND location2.territoryID = score2.territoryID           

WHERE shopcategories.formatID = 52
AND locationcategories.isClient =  0 
AND location2.isClient = 1 -- Reference to alias
AND scoreanalysis.waveID = 160
AND score2.waveID = 160 -- Reference to alias
GROUP BY scoreanalysis.sectionID 
ORDER BY scoreanalysis.sectionID