可以防止多个sql sum查询吗？

Question

我想防止这种情况的发生：

  $florissql = "SELECT SUM(hours) FROM logs WHERE executors LIKE '%floris%'";
  $rosasql = "SELECT SUM(hours) FROM logs WHERE executors LIKE '%rosa%'";
  $lottesql = "SELECT SUM(hours) FROM logs WHERE executors LIKE '%lotte%'";

是否有可能在一个查询中做到这一点？

Answer 1

试试这个吧？

 SELECT SUM(hours) AS Hours, 'floris' AS Executor FROM logs WHERE executors LIKE '%floris%'
 UNION ALL
 SELECT SUM(hours) AS Hours, 'rosa' AS Executor FROM logs WHERE executors LIKE '%rosa%'
 UNION ALL 
 SELECT SUM(hours) AS Hours, 'lotte' AS Executor FROM logs WHERE executors LIKE '%lotte%'

这将导致：

  Hours | Executor
  ----------------
  123   | floris
  456   | rosa
  789   | lotte

Answer 2

此question向您展示如何在LIKE中使用GROUP BY ...然后你可以这样做：

SELECT
  CASE
    WHEN executors LIKE '%floris%'   THEN 'floris'
    WHEN executors LIKE '%rosa%' THEN 'rosa'
    WHEN executors LIKE '%lotte%' THEN 'lotte'
  END AS exec
, SUM(hours) AS hrs
FROM logs
GROUP BY 
  CASE
    WHEN executors LIKE '%floris%'   THEN 'floris'
    WHEN executors LIKE '%rosa%' THEN 'rosa'
    WHEN executors LIKE '%lotte%' THEN 'lotte'
  END

然后，您可以在不同的行上使用exec匹配小时数的总和。

我链接的那个问题向你展示了更高级的方法，以防你需要做更多的事情……

Answer 3

如果需要每个执行者的结果，则应满足以下条件：

SELECT executors, SUM(hours)
FROM logs
WHERE executors LIKE '%floris%'
OR executors LIKE '%rosa%'
OR executors LIKE '%lotte%'
GROUP BY executors