这两个sql语句有什么不同？解决办法

这两个sql语句有什么不同？

SQL code

第一种SELECT GSLRID AS "路段名称",CASE WHEN (GSLVCL = '1' OR GSLVCL = '11') THEN COUNT(GSLREF)  END AS "一型流量",CASE WHEN (GSLVCL = '1' OR GSLVCL = '11') THEN SUM(TRANAMT)   END AS "一型收入",CASE WHEN (GSLVCL = '2' OR GSLVCL = '12') THEN COUNT(GSLREF)  END AS "二型流量",CASE WHEN (GSLVCL = '2' OR GSLVCL = '12') THEN SUM(TRANAMT)   END AS "二型收入",CASE WHEN (GSLVCL = '3' OR GSLVCL = '13') THEN COUNT(GSLREF)  END AS "三型流量",CASE WHEN (GSLVCL = '3' OR GSLVCL = '13') THEN SUM(TRANAMT)   END AS "三型收入",CASE WHEN (GSLVCL = '4' OR GSLVCL = '14') THEN COUNT(GSLREF)  END AS "四型流量",CASE WHEN (GSLVCL = '4' OR GSLVCL = '14') THEN SUM(TRANAMT)   END AS "四型收入",CASE WHEN (GSLVCL = '5' OR GSLVCL = '15') THEN COUNT(GSLREF)  END AS "五型流量",CASE WHEN (GSLVCL = '5' OR GSLVCL = '15') THEN SUM(TRANAMT)   END AS "五型收入"FROM POSONLTRANRECHIS GROUP BY GSLRID;第二种SELECT GSLRID AS "路段名称",count(CASE WHEN (GSLVCL = '1' OR GSLVCL = '11') THEN 1  END )AS "一型流量",sum(CASE WHEN (GSLVCL = '1' OR GSLVCL = '11') THEN TRANAMT   END) AS "一型收入",count(CASE WHEN (GSLVCL = '2' OR GSLVCL = '12') THEN 1  END )AS "二型流量",sum(CASE WHEN (GSLVCL = '2' OR GSLVCL = '12') THEN TRANAMT   END) AS "二型收入",count(CASE WHEN (GSLVCL = '3' OR GSLVCL = '13') THEN 1  END) AS "三型流量",sum(CASE WHEN (GSLVCL = '3' OR GSLVCL = '13') THEN TRANAMT   END) AS "三型收入",count(CASE WHEN (GSLVCL = '4' OR GSLVCL = '14') THEN 1  END) AS "四型流量",sum(CASE WHEN (GSLVCL = '4' OR GSLVCL = '14') THEN TRANAMT   END) AS "四型收入",count(CASE WHEN (GSLVCL = '5' OR GSLVCL = '15') THEN 1  END) AS "五型流量",sum(CASE WHEN (GSLVCL = '5' OR GSLVCL = '15') THEN TRANAMT   END) AS "五型收入"FROM POSONLTRANRECHIS GROUP BY GSLRID;请问第一种和第二种有什么区别？为什么第二种可以 第一种不可以？

[解决办法]
GSLVCL = '1' OR GSLVCL = '11'

这两个列名没有放在group by 后面

group by 的使用：
select 列表中的列明，如果没有出现在group by 后面，那么就一定放在聚合函数当中
[解决办法]
聚合函数应该和group by 对应的。
[解决办法]
select 语句里出现的字段，除聚合函数当中的，都要出现在group by 后面
[解决办法]

探讨

select 语句里出现的字段，除聚合函数当中的，都要出现在group by 后面

查看更多 下一篇