聚合函数:都是先分组后求和,但凡有聚合函数后面必须有group by
1:此处 instr:
引自: 添加链接描述:本人认为讲的最好的
2:此处 case when:
引自: 添加链接描述:本人认为讲的最好的
3:left join 和 inner join 区别:
1. SELECT
C.ITEM_NAME,
SUM(X.GAS_CONS_SCALE)GAS_CONS_SCALE,
SUM(X.CY_DRILL_NUM)CY_DRILL_NUM ,
SUM(X.BY_DRILL_NUM)BY_DRILL_NUM,
SUM(X.CY_END_DRILL_NUM)CY_END_DRILL_NUM,
SUM(X.BY_END_DRILL_NUM)BY_END_DRILL_NUM,
SUM(X.CY_FOOTAGE)CY_FOOTAGE,
SUM(X.BY_FOOTAGE)BY_FOOTAGE,
SUM(X.CY_END_TEST_OIL)CY_END_TEST_OIL,
SUM(X.BY_END_TEST_OIL)BY_END_TEST_OIL,
SUM(X.CY_INDUSTRY_WELL)CY_INDUSTRY_WELL,
SUM(X.BY_INDUSTRY_WELL)BY_INDUSTRY_WELL from PRODUCTION.CD_ITEM C
LEFT JOIN PRODUCTION.PC_PLAN_ITEM M ON C.ITEM_ID=M.ITEM_ID
LEFT JOIN PRODUCTION.PC_YEAR_PLAN Y ON Y.YEAR_PLAN_ID=M.YEAR_PLAN_ID
LEFT JOIN PRODUCTION.PC_PRODUCTIVITY_PLAN_INDEX X ON X.ITEM_ID=C.ITEM_ID where C.ITEM_CLASS=‘2008’ AND Y.PLAN_TYPE=‘2’
GROUP BY C.ITEM_NAME ORDER BY C.ITEM_NAME
left join on :
inner join on :
总结:inner join on它会把 gas_con_scale 等这些为空的的名称(这一行)给去掉 而left join on 会保留。如果是要求把各个名称的 gas_con_scale 等的的东西求和的话,别用inner join on,因为它可能过滤掉。
4:distinct:
对于聚合函数中可以使用distinct关键字来压缩重复值
比如我们想统计总共有多少个部门的话,我们如果写Select count(deptno) from emp;
将会得到错误的结果。因为实际上有很多重复的值也被计算在内。为了找到正确的答案,你应该这样写。
Select count(distinct deptno) from emp;