sql group by的巧用

group by 是标准sql提供的基本功能，利用该功能可以实现很多有用的功能，比如去除表中的重复记录或者合并记录等。

   1. 去除重复记录。

       完全重复记录： 即表中存在完全一样的记录，这一般出现在表的设计不合理的情况下。目前能想到的解决办法就是采用临时表来解决。 select  unique * into des_table  from test_table ; delete from test_table:  select  * into test_table  from des_table ; 

     部分字段重复的记录：

     delete from vitae a
where (a.peopleid,a.seq) in (select peopleid,seq from vitae group by peopleid,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleid,seq having count(*)>1) ;

 

2. sql关联表行列倒置

   create table students(no varchar(10),name varchar(10),sex nchar(1))
insert students select 'S001' , 'AA'  ,  '男'
insert students select 'S002' , 'BB'  ,  '女'
insert students select 'S003' , 'CC'  ,  '男'
create table results(examno varchar(10),no varchar(10),subject varchar(10),score int)
insert results select 'E001'  ,  'S001' , 'C++'   ,   60
insert results select 'E002'  ,  'S002' , 'Java'  ,   99
insert results select 'E003'  ,  'S001' , 'VB'    ,   80

--静态的
select a.name,max(case when b.subject='C++' then b.score else null end) [C++]
       ,max(case when b.subject='Java' then b.score else null end) [Java]
       ,max(case when b.subject='VB' then b.score else null end) [VB]
from students a
  left join results b
on a.no=b.no
group by a.name

--动态
declare @sql varchar(8000)
set @sql='select a.name'
select @sql=@sql+',max(case when b.subject='''+subject+''' then b.score else null end) ['+subject+']'
from results group by subject

exec (@sql+' from students a left join results b on a.no=b.no group by a.name')

 

 

 

 

 

3.单表行列倒置

   select * from test;

         A          B          C          D
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
         4          4          4          4

SQL>  select sum(decode(a,1,1,'')) a,
  2          sum(decode(a,2,2,'')) b,
  3          sum(decode(a,3,3,'')) c,
  4          sum(decode(a,4,4,'')) d
  5   from test
  6   union all
  7   select sum(decode(b,1,1,'')) a,
  8          sum(decode(b,2,2,'')) b,
  9          sum(decode(b,3,3,'')) c,
 10          sum(decode(b,4,4,'')) d
 11   from test
 12   union all
 13   select sum(decode(c,1,1,'')) a,
 14          sum(decode(c,2,2,'')) b,
 15          sum(decode(c,3,3,'')) c,
 16          sum(decode(c,4,4,'')) d
 17   from test
 18   union all
 19   select sum(decode(d,1,1,'')) a,
 20          sum(decode(d,2,2,'')) b,
 21          sum(decode(d,3,3,'')) c,
 22          sum(decode(d,4,4,'')) d
 23   from test;

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4
         1          2          3          4
         1          2          3          4
         1          2          3          4

 

 

 

decode(a,1,1,'')，表示判断c字段，如果是1，那么赋值1，其他情况赋值空，那么对应的case是

a=case when a=1 then 1 else null end

 

 

 

 

4. 合并行

 

   合并前：

 mysql> select *  from  score  ;
+------+---------+------------+----------+
| name | C_Score | Java_Score | VB_Score |
+------+---------+------------+----------+
| AA   |      60 |       NULL |     NULL |
| AA   |    NULL |       NULL |       80 |
| BB   |    NULL |         99 |     NULL |
| CC   |    NULL |       NULL |     NULL |
+------+---------+------------+----------+

 

 

合并语句：

mysql> select  name,max(C_Score) C_Score,max(Java_Score) Java_Score ,max(VB_Score) VB_Score  from  score  goup by name;

 

 

合并后：
+------+---------+------------+----------+
| name | C_Score | Java_Score | VB_Score |
+------+---------+------------+----------+
| AA   |      60 |       NULL |       80 |
| BB   |    NULL |         99 |     NULL |
| CC   |    NULL |       NULL |     NULL |
+------+---------+------------+----------+