关于group by

1.错误的sql语句：此sql语句报错信息是：group by 后面的标识符无效（字段名无效）

错误原因：因为使用了子查询，而group by后面是子查询的别名，所以无效。

先后顺序：1.where条件 , 2分组，3.集合函数(或者子查询)，4.having筛选

反推理解：由于用了sum集合函数，所以子查询报错了，因此说明集合函数和子查询是同一个档次的(同时执行)

注意：查询中的执行步骤：先分组，在聚合，最后筛选。。。(where ,分组，集合函数，筛选)

 

select
t1.fund_id,
(select name from T_PUBMOFDEPT where id=t.zg_dept_id) DEPT_NAME,
(select name from t_pubagency where id=t.agency_id) AGENCY_NAME,
t.annual,
t.item_name,
(select name from T_PUBBDGSOURCE where id=t1.bdgsrc_id) BDGSRC_NAME,
(select name from T_PUBBGTSOURCE where id=t1.bgtsrc_id) BGTSRC_NAME,
(select name from T_PUBFUNDTYPE where id=t1.fundtype_id) FUNDTYPE_NAME,
(select name from t_pubexpfunc where id=t.expfunc_id) EXPFUNC_NAME,
sum(t1.amount) amount,
(select  BGT_FILE_NO from TB_BGT_DEPT_BGTFILE where BGT_FILE_ID=t.bgt_file) BGT_FILE,
t.super_rev_file,
t.rev_file,
(select  name  from T_PUBFUNDCLASS where id=(select FUNDCLASS_ID from tb_bgt_item where ITEM_ID=t.item_id)) FUNDCLASS_NAME,
(select name from T_PUBEXPECONORMIC where id=(select eco_id from tb_bgt_fund where id=t1.fund_id)) ECO_NAME,
(select  name  from T_PUBPROTYPE where id=(select PROTYPE_ID from tb_bgt_item where ITEM_ID=t.item_id)) PROTYPE_NAME,
t.sn,
(select name from T_PUB_COUNTY_DISP where id=t.county_disp_id) COUNTY_DISP_NAME,
(select name from T_PUBBASESVR where id=t.basesvr_id) BASESVR_NAME,

t.ANNOTATION
from tb_bgt_item_info t
left join TB_BGT_ITEM_AMOUNT_SUB t1
on t.ITEM_ID=t1.item_ls_id
where 1=1
and t.annual=2016
group by fund_id,DEPT_NAME,AGENCY_NAME,item_name, BDGSRC_NAME,BGTSRC_NAME,FUNDTYPE_NAME,EXPFUNC_NAME,
BGT_FILE,super_rev_file,rev_file,FUNDCLASS_NAME,ECO_NAME, PROTYPE_NAME,sn,COUNTY_DISP_NAME,BASESVR_NAME,ANNOTATION

 

2.正确的sql语句：

修改过程：现在group by后面不是子查询的别名了，而是子查询里面的表的某个字段

注意：还添加了CKLW字段，这个字段没写在group by后面竟然没报错

没报错的原因：因为这一行里面有REV_FILE 字段

select
case when t.REV_FILE is not null then '允许' else '不允许' end IS_CKLW,
t1.fund_id,
(select name from T_PUBMOFDEPT where id=t.zg_dept_id) DEPT_NAME,
(select name from t_pubagency where id=t.agency_id) AGENCY_NAME,
t.annual,
t.item_name,
(select name from T_PUBBDGSOURCE where id=t1.bdgsrc_id) BDGSRC_NAME,
(select name from T_PUBBGTSOURCE where id=t1.bgtsrc_id) BGTSRC_NAME,
(select name from T_PUBFUNDTYPE where id=t1.fundtype_id) FUNDTYPE_NAME,
(select name from t_pubexpfunc where id=t.expfunc_id) EXPFUNC_NAME,
sum(t1.amount) amount,
(select  BGT_FILE_NO from TB_BGT_DEPT_BGTFILE where BGT_FILE_ID=t.bgt_file) BGT_FILE,
t.super_rev_file,
t.rev_file,
(select  name  from T_PUBFUNDCLASS where id=(select FUNDCLASS_ID from tb_bgt_item where ITEM_ID=t.item_id)) FUNDCLASS_NAME,
(select name from T_PUBEXPECONORMIC where id=(select eco_id from tb_bgt_fund where id=t1.fund_id)) ECO_NAME,
(select  name  from T_PUBPROTYPE where id=(select PROTYPE_ID from tb_bgt_item where ITEM_ID=t.item_id)) PROTYPE_NAME,
t.sn,
(select name from T_PUB_COUNTY_DISP where id=t.county_disp_id) COUNTY_DISP_NAME,
(select name from T_PUBBASESVR where id=t.basesvr_id) BASESVR_NAME,

t.ANNOTATION
from tb_bgt_item_info t
left join TB_BGT_ITEM_AMOUNT_SUB t1
on t.ITEM_ID=t1.item_ls_id
where 1=1
and t.annual=2016
group by fund_id,t.zg_dept_id,t.agency_id,annual,item_name,t1.bdgsrc_id,t1.bgtsrc_id,t1.fundtype_id,t.expfunc_id,
t.bgt_file,super_rev_file,rev_file,t.item_id,t1.fund_id,sn,t.county_disp_id,t.basesvr_id,ANNOTATION