sql 2

表的字段非常简单,创建表的sql语句如下:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Exam]') AND type in (N'U'))
BEGIN
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]
END

题目一: 写一条Sql语句查询前出100到199的记录
题目二: 写一条Sql语句删除重复[除时间外的所有字段字段相同]的记录,保留重复记录中时间最大的记录
题目三: 一条Sql语句查出年份,1月,2月,3月....12月的列表
题目四: 一条sql语句查询出年份,本月销量,上月销量,环比%,去年同期销量,同比%列表
--  题一  一条Sql语句 查询前100到199的记录

select top 100 * from exam 
where s_date<
(
select min(T.s_date) from ( select top 99 s_date from exam order by s_date desc ) as T
)
order by s_date desc

--



-- 题二 一条Sql语句 删除重复的记录[时间不重复,其它字段重复]

delete from exam 
where s_date not in 
(
 select T.dt from 
  (
   select order_id,product_id,amt,max(s_date) as dt from exam group by order_id,product_id,amt
  ) as T
)

--


--题三   一条Sql语句 查年份,1月,2月....12月

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实现一个功能： 

如果不足8条纪录，添加至8条纪录。 

超过8条纪录，剪切至8条纪录。 
假设a表有列c1,  c2,  c3 

 

select  *  from  

(select  c1,  c2,  c3  from  a  

union  all  

  select  null,  null,  null  from  all_objects  where  rownum<9  

)  

where  rownum  <  9; 

------------------------------------------------------------