日期:2014-05-18  浏览次数:20430 次

这两个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 后面