SQL查询

连接查询

• inner join ：AB交集 （等同于join,,）
  • 显式：select [cols_list] from a inner join b on [condition] where [condition]
  • 隐式：select [cols_list] from a,b where [condition]
• outer join
  • left join：A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代 （等同于left outer join）
  • right join：B的完全集，而A表中匹配的则有值，没有匹配的则以null值取代 （等同于right outer join）
  • full join：A和B的并集。对于没有匹配的记录，则会以null做为值
• cross join：表A和表B的数据进行一个N*M的组合，即笛卡尔积，不带ON子句
  • select [cols_list] from a cross join b where [condition]
• full join: AB并集
  • select [cols_list] from a full join b where [condition]
• 注意：
  • left Join on后面的条件对Left的表没有作用，只对Right的表有过滤作用
  • inner join on后面的限制条件将全部起作用，这与where的执行结果是一样的

示例：

table a

table b

1. 使用a left join b+and a

    select a.*,b.* from a left join b on a.id=b.id and a.type='a';
   

   a.id	a.type	b.id	b.class
   1	a	1	aa
   2	a	2	bb
   3	b	/	/

2. 使用a left join b+and b

    select a.*,b.* from a left join b on a.id=b.id and b.class='aa'
   

   a.id	a.type	b.id	b.class
   1	a	1	aa
   2	a	/	/
   3	b	/	/

3. 使用left join+where

    select a.*,b.* from a left join b on a.id=b.id where a.type='a';
   

   a.id	a.type	b.id	b.class
   1	a	1	aa
   2	a	2	bb

4. 使用inner join

    select a.*, b.* from a inner join b on a.id = b.id and a.type = 'a';
    select a.*, b.* from a inner join b on a.id = b.id where a.type = 'a';
    select a.*, b.* from a, b where a.id = b.id and a.type = 'a';
   

   a.id	a.type	b.id	b.class
   1	a	1	aa
   2	a	2	bb

rownum,rowid

（MySQL无rownum和rowid）

注意：

• 都是伪列（可以理解成表中的一个列，但并不是你创建的）
• 如果想让rownum 和order by一样的顺序 那么可以使用子查询：

  select rownum,t.* from (select * from table1 order by col1) t
  

row_number,dense_rank,rank

• row_number() over : 排名顺序递增（相同记录排名不同）
• dense_rank() : 排名顺序递增 （相同记录排名相同）
• rank()：排名跳跃递增（相同记录排名相同， 在下一条不同记录间空出排名）
• 注意：都是函数，返回一个值

row_number() over 使用示例：

SELECT 
t.*, 
Row_Number() OVER (partition by deptid ORDER BY salary desc) rownumber 
FROM employee t

重复记录

1. 查找表中重复记录 （使用count(?)>1）

   select * from employee
   where deptid in (
    select deptid
    from employee
    group by deptid
    having count(deptid) > 1
   )
   

2. 找表中非重复记录（使用count(?)=1或distinct）

   select * from employee
   where deptid in (
    select deptid
    from employee
    group by deptid
    having count(deptid) =1
   )
   

   select * from employee
   where deptid in (
    select distinct deptid
    from employee
   )
   

3. 让表中存在的重复记录只留一条
   • 使用group by

     SELECT deptid,max(salary) FROM employee group by deptid
     

   • 使用row_number() over

     select * 
     from ( 
       select 
       Row_Number() over(partition by deptid order by salary) rank,
       t.*
       from employee t
     )
     where rank = 1
     

   • 使用rownum （效率低）

     select * 
     from employee t
     where rownum in  (
       select max(rownum) from employee b where t.id = b.id group by deptid
     )
     

行列转换

示例1：

=>

1. 使用distinct +left join

   select A.date0, B.win, C.lost from
    (select distinct date as date0 from Table1) A
    left join  
    (select date as date0,count(*) as win from Table1 where rst='win' group by date) B
    on A.date0=B.date0
    left join  
    (select date as date0,count(*) as lost from Table1 where rst='lost' group by date) C
    on A.date0=C.date0
   

2. 使用sum+case when+count

   select 日期 ,sum(结果1) as '胜',sum(结果2) as '负'
   from (
    select 日期,
     case when 结果='胜' then count(结果) end AS 结果1, 
     case when 结果='负' then count(结果)  end AS 结果2 
     from table_1 
     group by 日期,结果
   ) a
   group by 日期
   order by 日期 desc
   

3. 使用sum+case when

   select 日期,
   sum(case 结果 when '胜' then 1 else 0 end) as '胜',
   sum(case 结果 when '负' then 1 else 0 end) as '负' 
   from t 
   group by 日期
   

示例2：

=>

select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理,
  cast(avg(result*1.0) as decimal(18,2)) 平均分,
  sum(result) 总分
from tb
group by name

分页

1. 使用top+not in :

   SELECT TOP 页大小 *
   FROM table1
   WHERE id NOT IN (
    SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id 
   )
   ORDER BY id
   

2. 使用top+>+max(id)：

   SELECT TOP 页大小 *
   FROM table1
   WHERE id > (
   SELECT 
   ISNULL(MAX(id),0) 
   FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A
   )
   ORDER BY id
   

3. 使用row_number() over：

   SELECT TOP 页大小 *
   FROM (  
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownumber,* FROM table1) A
   WHERE rownumber > 页大小*(页数-1)
   

4. 使用rownum

   SELECT * 
   FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT * FROM t_employees ORDER BY first_name) a
    WHERE ROWNUM <= 500
   )
   WHERE rn > 480
   

PS： Mysql分页

select * from tableName where 条件 limit (当前页码-1)*页面容量 , 页面容量

合并多行记录

=>

1. 创建一个合并的函数:

   create function f_hb(@a int)
   returns varchar(8000)
   as
   begin
   declare @str varchar(8000)
   set @str = ''
   select @str = @str + ',' + cast(b as varchar) from tb where a = @a 
   set @str = right(@str , len(@str) - 1)
   return(@str)
   End
   go
   

2. 调用自定义函数:

   select distinct a ,f_hb(a) as b from tb
   

扩展：

• Oracle wmsys.wm_concat函数 （10g引入）
• SQL Server pivot行转列函数，unpivot列转行函数，xml方式（2005引入）