SQL语句复习--group by 的用法

今天自己在写需求的时候，运行自己写过的sql，总是报不是group by 分组函数，自己都搞了两个小时了，实在没有办法，最后请教林哥，记得上次请教林哥的也是一个关于group by函数的使用方法，并且，人家已经叮嘱自己下来之后上网上多看看group by函数的使用方法，看来自己真的应该长点记性了，不要再不会用了，因为这可是工作呀！就得要有认真的态度。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

select lb.contno,         decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),         decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),         to_date(lb.create_time),         to_char(lb.sum_total, '9999999990.99'),         to_char(lb.sum_price, '9999999990.99'),         to_char(lb.sum_tax, '9999999990.99'),         max(lb.sid),         lb.managecom    from LIS_BUSI_TRANSACTIONS lb, lccont cont   where '1581260514000' = '1581260514000'     and cont.contno = lb.contno     and lb.invoiceflag in ('00')     and lb.successflag = '1'     and not exists   (select 1 from lcgrpcont c where c.grpcontno = lb.contno)     and lb.sum_total > 0     and (cont.currency = '01' or cont.currency is null)     and not exists (select 1            from ljagetendorse a, LIS_BUSI_TRANSACTIONS b           where a.actugetno = b.sourceid             and a.getflag = '1'             and b.ruleid in ('3', '6', '10', '12')             and a.actugetno = lb.sourceid)     and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno) /*    and lb.contno = '1'     and to_date(lb.create_time) >= '2019-02-10'     and to_date(lb.create_time) <= '2020-02-14'     and lb.managecom like '86%'     and lb.managecom like '86%'*/     group by lb.contno,         decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),         decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),         to_date(lb.create_time),         to_char(lb.sum_total, '9999999990.99'),         to_char(lb.sum_price, '9999999990.99'),         to_char(lb.sum_tax, '9999999990.99'),         lb.sid,         lb.managecom

　　现在上面的函数是能够运行的，group by 函数就是要将查询结果中的每一列都要放到group by的后面。自己当时在写这个sql的时候，也不是怎么搞的，就是报错，可能是因为当时group by后面少放了字段的原因，看来遇到问题不要慌张，要冷静下来，仔细思考，结果一问林哥，人家也没有说啥，自己又尝试着改了一遍就好使了。

不过林哥说：你见哪个开发这样写sql了。

 

 下面这一段，确实，自己都看着恶心。改造一下呗！

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

select lb.contno,         decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),         decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),         to_date(lb.create_time),         to_char(lb.sum_total, '9999999990.99'),         to_char(lb.sum_price, '9999999990.99'),         to_char(lb.sum_tax, '9999999990.99'),         max(lb.sid),         lb.managecom    from LIS_BUSI_TRANSACTIONS lb, lccont cont   where '1581260514000' = '1581260514000'     and cont.contno = lb.contno     and lb.invoiceflag in ('00')     and lb.successflag = '1'     and not exists   (select 1 from lcgrpcont c where c.grpcontno = lb.contno)     and lb.sum_total > 0     and (cont.currency = '01' or cont.currency is null)     and not exists (select 1            from ljagetendorse a, LIS_BUSI_TRANSACTIONS b           where a.actugetno = b.sourceid             and a.getflag = '1'             and b.ruleid in ('3', '6', '10', '12')             and a.actugetno = lb.sourceid)     and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno) /*    and lb.contno = '1'     and to_date(lb.create_time) >= '2019-02-10'     and to_date(lb.create_time) <= '2020-02-14'     and lb.managecom like '86%'     and lb.managecom like '86%'*/     group by lb.contno,         cont.conttype, cont.appntno, cont.insuredno, cont.appntname,  cont.insuredname,         lb.create_time,         lb.sum_total,         lb.sum_price,         lb.sum_tax,         lb.sid,         lb.managecom

　　这样就好看多了，哈哈！

值得注意的是，像查询结果中使用到的decode函数，等等，一定要将函数中使用到的本表中的字段，一个不落的写在group by的后面才行。

假如我给要查询的结果列起别名，行不行呢？

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

select lb.contno a,         decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno) b,         decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname) c,         to_date(lb.create_time) d,         to_char(lb.sum_total, '9999999990.99') e,         to_char(lb.sum_price, '9999999990.99') f,         to_char(lb.sum_tax, '9999999990.99') g,         max(lb.sid) h,         lb.managecom j    from LIS_BUSI_TRANSACTIONS lb, lccont cont   where '1581260514000' = '1581260514000'     and cont.contno = lb.contno     and lb.invoiceflag in ('00')     and lb.successflag = '1'     and not exists   (select 1 from lcgrpcont c where c.grpcontno = lb.contno)     and lb.sum_total > 0     and (cont.currency = '01' or cont.currency is null)     and not exists (select 1            from ljagetendorse a, LIS_BUSI_TRANSACTIONS b           where a.actugetno = b.sourceid             and a.getflag = '1'             and b.ruleid in ('3', '6', '10', '12')             and a.actugetno = lb.sourceid)     and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno) /*    and lb.contno = '1'     and to_date(lb.create_time) >= '2019-02-10'     and to_date(lb.create_time) <= '2020-02-14'     and lb.managecom like '86%'     and lb.managecom like '86%'*/     group by a,b,c,d,e,f,g,h,j

　　答案是不行的，会报J标识符无效。

哦！对了，这个是oracle数据库的。