MySQL 查询 SELECT GREATEST int row VALUE between limits

Question

我需要获取表中的记录数，其中字段值:(ps1,ps2,ps3,ps4,ps5,ps6,ps7,ps8,ps9,ps10) min max limits 是最大的。并将此值与其他字段值 d_max 进行比较。

例如:

SELECT count(*) FROM table WHERE d_max < 
{GREATEST one of (ps1,ps2,ps3,ps4,ps5,ps6,ps7,ps8,ps9,ps10) BEETWEEN 10 AND 20}

请帮忙。

Answer 1

不漂亮，但应该可以:

SELECT COUNT(*) FROM (
  SELECT 
    t0.d_max
    , IFNULL(t1.ps1,0) as ps1, IFNULL(t2.ps2,0) as ps2
    , IFNULL(t3.ps3,0) as ps3, IFNULL(t4.ps4,0) as ps4
    , IFNULL(t5.ps5,0) as ps5, IFNULL(t6.ps6,0) as ps6
    , IFNULL(t7.ps7,0) as ps7, IFNULL(t8.ps8,0) as ps8
    , IFNULL(t9.ps9,0) as ps9, IFNULL(t10.ps10,0) as ps10
  FROM `table` t0
  LEFT JOIN `table` t1 ON (t0.id = t1.id AND t1.ps1 BETWEEN 10 AND 20)
  LEFT JOIN `table` t2 ON (t0.id = t2.id AND t2.ps2 BETWEEN 10 AND 20)
  LEFT JOIN `table` t3 ON (t0.id = t3.id AND t3.ps3 BETWEEN 10 AND 20)
  LEFT JOIN `table` t4 ON (t0.id = t4.id AND t4.ps4 BETWEEN 10 AND 20)
  LEFT JOIN `table` t5 ON (t0.id = t5.id AND t5.ps5 BETWEEN 10 AND 20)
  LEFT JOIN `table` t6 ON (t0.id = t6.id AND t6.ps6 BETWEEN 10 AND 20)
  LEFT JOIN `table` t7 ON (t0.id = t7.id AND t7.ps7 BETWEEN 10 AND 20)
  LEFT JOIN `table` t8 ON (t0.id = t8.id AND t8.ps8 BETWEEN 10 AND 20)
  LEFT JOIN `table` t9 ON (t0.id = t9.id AND t9.ps9 BETWEEN 10 AND 20)
  LEFT JOIN `table` t10 ON (t0.id = t10.id AND t10.ps10 BETWEEN 10 AND 20)
) s
WHERE s.d_max < GREATEST(s.ps1,s.ps2,s.ps3,s.ps4,s.ps5,s.ps6,s.ps7,s.ps8,s.ps9,s.ps10)

请注意，这确实需要为此表设置显式主键，但是如果您使用的是 InnoDB，则应始终使用显式主键，否则会遇到严重的性能问题。

编辑
看看下面的构造是否会更快....

SELECT COUNT(*) FROM (
  SELECT 
    d_max
    ,CASE WHEN ps1 BETWEEN 10 AND 20 THEN ps1 ELSE 0 END AS ps1
    ,...
    ,CASE WHEN ps10 BETWEEN ..                           AS ps10
) s
WHERE s.d_max < GREATEST(s.ps1,s.ps2,s.ps3,s.ps4,s.ps5,s.ps6,s.ps7,s.ps8,s.ps9,s.ps10)