经典SQL语句汇总

来源：互联网发布：沙钢大数据编辑：程序博客网时间：2024/05/23 19:19

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()