Question

在转换为联合考试委员会的过程中,有I m的遗产LAMP应用。

问题涉及一个与“250K”记录相加的图表。目前,“核心类型”一栏是一个连续体,1 = 目标,2 = 援助1,3 = 援助2。

为了达到每个参与者的目标/目标/目标/目标/目标2,询问会发现:

SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

工作做得够好,但我很想知道,在总计划设计方面,如果问询环境会带来特别大集团、COUNT等。

In separate columns the query then becomes:

SUM(goal) AS goal, SUM(assist1) AS assist1, SUM(assist2) AS assist2

这是一种性能赢利(如果(cond,a,b)对等),其成本是略微增加的储存(3栏)。页: 1

At the application layer one potential big win is moving from ORM non-supported SUM(if()) to column.Sum(); otherwise, I ll need to keep non-statically typed string SQL queries with the all-in-one-column approach

想法? 如何对待、请假或将非行和适用守则移至三栏办法?

Thanks for feedback!

Answer 1

SELECT scoreType, COUNT(scoreType)
FROM ...
GROUP BY scoreType

难道没有吗?

a. 比较替代品的诚实时机,使用更精确的平均数;

SELECT COUNT(CASE WHEN scoreType = 1 THEN id ELSE NULL END) AS goals,
       ...

友情链接