表结构：

CREATE TABLE [dbo].[Exam](

    [S_date] [datetime] NOT NULL,

    [Order_Id] [varchar](50) NOT NULL,

    [Product_Id] [varchar](50) NOT NULL,

    [Amt] [numeric](18, 0) NOT NULL

) ON [PRIMARY]

题目一: 写一条Sql语句查询前出100到199的记录

题目二: 写一条Sql语句删除重复[除时间外的所有字段字段相同]的记录,保留重复记录中时间最大的记录

题目三: 一条Sql语句查出年份,1月,2月,3月....12月的订单总数列表

题目四: 一条sql语句查询出年份,本月销量,上月销量,环比%,去年同期销量,同比%列表

第一题比较简单，前面也讲过，就不赘述。

第二题：

delete from Exam where S_date not in (

    select e2.maxdt from

    (

         select Order_Id,Product_Id,Amt,MAX(S_date) as maxdt from Exam

         group by Order_Id,Product_Id,Amt

    ) as e2

)

第三题：分两次组

select y,sum(c1) as m1,sum(c2) as m2,sum(c3) as m3,sum(c4) as m4,sum(c5) as m5,sum(c6) as m6,

sum(c7) as m7,sum(c8) as m8,sum(c9) as m9,sum(c10) as m10,sum(c11) as m11,sum(c12) as m12

from

(

select

  y,

  case m when 1 then c else 0 end as c1,

  case m when 2 then c else 0 end as c2,

  case m when 3 then c else 0 end as c3,

  case m when 4 then c else 0 end as c4,

  case m when 5 then c else 0 end as c5,

  case m when 6 then c else 0 end as c6,

  case m when 7 then c else 0 end as c7,

  case m when 8 then c else 0 end as c8,

  case m when 9 then c else 0 end as c9,

  case m when 10 then c else 0 end as c10,

  case m when 11 then c else 0 end as c11,

  case m when 12 then c else 0 end as c12

from

  (

   select y,m,count(s_date) as c from

   (

    select datepart(year,convert(DateTime,s_date)) as y,

     datepart(month,convert(DateTime,s_date)) as m ,

     s_date from exam

   )  as T1

   group by T1.y,T1.m

  )

as T2

) as T3

group by T3.y

第三题：SQL相当复杂，不过只要理清了思路就很容易上手了，这里主要用到了left join on，语句如下：

select y1 年,m1 月,c1 本月销售额,

       c2 上月销售额,

       case when c2 is null or c2=0 then '无穷大'

       else cast(cast((isnull(c1, 0)-isnull(c2,0))*100/isnull(c2, 0) as decimal(10,2)) as varchar(50))+'%' end as 环比增长 ,

       c3 去年本月销售额,

       case when c3 is null or c3=0 then '无穷大'

       else cast(cast((isnull(c1, 0)-isnull(c3,0))*100/isnull(c3, 0) as decimal(10,2)) as varchar(50))+'%' end as 同比增长

from

(

select y1,m1,c1,c2,c3

from

(

   select y1,m1,c1,c2 from

   (

    select y1,m1,sum(Amt) as c1 from

     (

          select datepart(year,convert(DateTime,s_date)) as y1,

                   datepart(month,convert(DateTime,s_date)) as m1 ,

                  Amt

         from orders

     )  as T1

     group by T1.y1,T1.m1

    ) o2  

left join

   (

     select y2,m2,sum(Amt) as c2 from

     (

         select datepart(year,convert(DateTime,s_date)) as y2,

                  datepart(month,convert(DateTime,s_date)) as m2 ,

                  Amt from orders

       )  as T1

       group by T1.y2,T1.m2

     ) o3

   on o2.y1 = o3.y2 and o2.m1 = o3.m2 - 1

  ) as o4

  left join

  (

     select y3,m3,sum(Amt) as c3 from

     (

         select datepart(year,convert(DateTime,s_date)) as y3,

                  datepart(month,convert(DateTime,s_date)) as m3,

                  Amt from orders

      )  as T1

      group by T1.y3,T1.m3

   ) as o5

   on o4.y1 = o5.y3 - 1 and o4.m1 = o5.m3

) as o6

 

     年   月    本月销售额           上月销售额   环比        去年同期   同比

    2012 3     230                         420   -45.24%         NULL    无穷大

    2010 4     270                         NULL   无穷大          300      -10.00%

    2011 4     300                         353   -15.01%         420      -28.57%

    2012 4     420                         463   -9.29%           NULL     无穷大

    2011 5     353                         NULL   无穷大          463       -23.76%

    2012 5     463                         NULL   无穷大          NULL     无穷大

    2011 11    492                        240    105.00%        NULL    无穷大

    2011 12    240                        NULL    无穷大          NULL     无穷大