经典SQL语句汇总

1、  存在这样一个简单的表Test (ID,Name,Code),其中ID是主键PK，写一条SQL 语句实现 : 获得一个记录集， 
        该记录集包括Test中全部的字段，按记录的ID排序，并且要生成一个连续的记录序号字段 RowNO 字段？

       select *,’序号’+ cast((select count(*) from Test where id<=a.id ) as varchar(50)) as RowNO from Test a order by id

2、竖表转横表

     CREATE TABLE #table(name VARCHAR(10), object VARCHAR(10), score INT)

      INSERT #table SELECT 'a',      'EN'  ,   89
      UNION ALL SELECT 'a',     'CH'  ,   78
      UNION ALL SELECT 'a' ,    'HO'  ,   99
      UNION ALL SELECT 'b'  ,   'EN'  ,   34
      UNION ALL SELECT 'b'   ,  'CH'  ,   88
      UNION ALL SELECT 'b'    , 'HO'  ,   66

      SELECT * FROM #table

      declare @sql varchar(4000)
      set @sql = 'select Name'
      select @sql = @sql + ',sum(case object when '''+object+''' then score end) ['+object+']'
      from (select distinct object from #table) as a
      select @sql = @sql+' from #table group by name'
      exec(@sql)

      DROP TABLE #table

3、企业部门员工人数逐级汇总

    select deptName,deptcode,
             (select  sum(PeopleSum) as PeopleSum from
                (select b.dept_id,b.deptcode,a.PeopleSum
                   from
                         (select dept_id,count(*) as PeopleSum 
                               from hr_empl
                               group by dept_id)a 
                         right join dept b on a.dept_id = b.dept_id
               order by b.deptcode) c  where c.deptcode like a.deptcode||'%' ) as  PeopleSum
    from dept a
    order by a.deptcode

4、用取模的方式巧妙分组统计
   select YMDHM,SUM(L0) as SumL0 from Tb_Qry_Sal_FactPower where ymdhm='2008-01-01' group by YMDHM,mm/15

5、把字段数据组合成一个字符串输出

      Create Function GetVALUE() 
             RETURNS Varchar(8000) 
      AS 
      BEGIN 
         DECLARE @s Varchar(8000) 
         set @s='' 
         select @s=@s+','+cast(id as varchar(10))+':'+cast(datavalue as varchar(10)) 
                      from dbo.SSSJ 
         return @s 
      END
print dbo.getvalue()