mysql - SQL如何将每一行列除以它的最大值最小值？

Question

我的表“datakota”有 9 个字段，然后我进行查询以选择所有字段的最大值、最小值。但结果显示所有行的值均为“1”。如果我错了，请帮助我。

|id| |kota_kab| |ipm| |pddmiskin| |ginirasio| |rasio_k| |indks_pend| |indks_kes| |ppk| |tpt| |tpak|

SELECT ipm/MAX(ipm) AS maxipm,
       pddmiskin/MIN(pddmiskin) AS minpddmiskin, 
       ginirasio/MIN(ginirasio) AS minginirasio, 
       rasio_k/MIN(rasio_k) AS minrasio_k, 
       indks_pend/MIN(indks_pend) AS minpend, 
       indks_kes/MAX(indks_kes) AS maxkes,
       ppk/MAX(ppk) AS maxppk,
       tpt/MIN(tpt) AS mintpt, 
       tpak/MAX(tpak) AS maxtpak 
FROM datakota

Answer 1

在 MySQL 5.7 及更低版本中，您需要在单独的子查询中进行所有聚合，然后逐行进行划分:

SELECT ipm/maxipm,
       pddmiskin/minpddmiskin, 
       ginirasio/minginirasio, 
       rasio_k/minrasio_k, 
       indks_pend/minpend, 
       indks_kes/maxkes,
       ppk/maxppk,
       tpt/mintpt, 
       tpak/maxtpak 
FROM datakota d
JOIN (SELECT MAX(ipm) AS maxipm,
             MIN(pddmiskin) AS minpddmiskin, 
             MIN(ginirasio) AS minginirasio, 
             MIN(rasio_k) AS minrasio_k, 
             MIN(indks_pend) AS minpend, 
             MAX(indks_kes) AS maxkes,
             MAX(ppk) AS maxppk,
             MIN(tpt) AS mintpt, 
             MAX(tpak) AS maxtpak 
      FROM datakota) minmax

在 MySQL 8.0+ 中，您可以使用窗口函数来简化查询:

SELECT ipm/(MAX(ipm) OVER ()) AS maxipm,
       pddmiskin/(MIN(pddmiskin) OVER ()) AS minpddmiskin, 
       ginirasio/(MIN(ginirasio) OVER ()) AS minginirasio, 
       rasio_k/(MIN(rasio_k) OVER ()) AS minrasio_k, 
       indks_pend/(MIN(indks_pend) OVER ()) AS minpend, 
       indks_kes/(MAX(indks_kes) OVER ()) AS maxkes,
       ppk/(MAX(ppk) OVER ()) AS maxppk,
       tpt/(MIN(tpt) OVER ()) AS mintpt, 
       tpak/(MAX(tpak) OVER ()) AS maxtpak 
FROM datakota