SQL学习技巧

今天去csdn 数据库开发区看看，居然有很多值得学习的东西。

1  如何由一个8位的日期得到他的生肖
declare @dt as datetime
set @dt = '1753 -01-01'
select case when year(@dt)%12 = 0 then '猴'
            when year(@dt)%12 = 1 then '鸡'
            when year(@dt)%12 = 2 then '狗'
            when year(@dt)%12 = 3 then '猪'
            when year(@dt)%12 = 4 then '鼠'
            when year(@dt)%12 = 5 then '牛'
            when year(@dt)%12 = 6 then '虎'
            when year(@dt)%12 = 7 then '兔'
            when year(@dt)%12 = 8 then '龙'
            when year(@dt)%12 = 9 then '蛇'
            when year(@dt)%12 = 10 then '马'
            when year(@dt)%12 = 11 then '羊'
       end as 生肖

2 查询逗留时间超过3天和5天的人：
select A.人员,
sum(case when datediff(dd,到达时间,离开时间)>3 then 1 else 0 end ) as 超过3天,
sum(case when datediff(dd,到达时间,离开时间)>5 then 1 else 0 end ) as 超过5天
from A
where  部门='技术部'
group by 人员

3  不足的记录都用null填充：
SELECT TOP 10 B.* FROM
(SELECT TOP 10 * FROM A
UNION ALL SELECT TOP 10 '','',NULL,NULL,NULL  FROM SYSOBJECTS)B

 

 

4 使用GROUP BY需要注意：

如果一条SELECT语句包含了GROUP BY，则在SELECT后面列出的字段，
除了由聚合函数（如SUM,MIN,MAX等）计算的结果字段可以不出现在
GROUP BY中外，其他的所有字段必须也同时出现在GROUP BY中，举例如下
select a,b,c,sum(d),max(date) from table group by a,b,c

5 怎样通过一条sql语句计算出一张表中某列的平均值不能使用avg()和sum()函数

感叹牛人真多：

exec('declare @sum float,@count int set @sum=0 set @count=0 select @sum=@sum+field,@count=@count+1 from testtable print @sum/@count')

6 指定时间的基础上 再加减一段时间：

select getdate()

select dateadd(day,-5,getdate())

select dateadd(year,-5,getdate())

select dateadd(Week,-5,getdate())

select dateadd(hour,-5,getdate())

select dateadd(second,-5,getdate())

7 isnull      nullif

ISNULL ( A, B)

 A 必须与 B 具有相同的类型。

如果 A 不为 NULL，那么返回该表达式的值；否则返回 B

NULLIF ( A , B )       

    AB为常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组合。

返回类型与第一个 expression 相同。

如果两个表达式不相等，NULLIF 返回第一个 A 的值。如果相等，NULLIF 返回第一个 B类型的空值。

如果两个表达式相等且结果表达式为 NULL，NULLIF 等价于 CASE 的搜索函数。

 

8查指定行到指点行的数据：如200-300行

select * from city where CityId in

(     select top 100 CityId from city where CityId in

     (select top 300 CityId from city order by CityId) order by CityId desc

) order by CityId

9 查询应用：

学生：S(Sno,Sname,Age,Sex,SD)
课程：C(Cno,Cname,Teacher)
选课：SC(Sno,Cno,Grade)

1。检索至少选修了课程号为“C1”“C3”的学生号
2。检索选修了“操作系统”或“数据库”课程的学生姓名和成绩
3。检索选修全部课程的学生姓名
4。检索至少选修了学生号“1042”选修的全部课程的学生号

再来复习几个sql语句，大学时代的东西，呵呵
1。检索至少选修了课程号为“C1”“C3”的学生号
select Sno from SC
where Cno='C1' or Cno='C2'
group by Sno
having count(*)>1

2。检索选修了“操作系统”或“数据库”课程的学生姓名和成绩
1.

select s.sname , t.grade,t.cno from s,
   (
   select * from sc where cno in
   (select cno from c where cname = '操作系统' or cname = '数据库')
   ) t
   where s.sno = t.sno

2.

select * from c
inner join S on SC.Sno=S.Sno
inner join C on SC.Cno=C.Cno
where Cname='操作系统' or Cname='数据库'

3.

select sname,sc.grade from c,s,sc where s.sno=sc.sno and c.cno=sc.cno and (c.cname='操作系统' or c.cname='数据库')

3。检索选修全部课程的学生姓名
select * from s where sno in
   (select sno from sc group by sno having count(*) = (select count(*) from c))

 

 o r

select Sname
from S
where not exists
  (selece *
    from C
      where not exists
         (
           select *
            from SC
             where Sno=S.Sno and Cno=C.Cno));

4。检索至少选修了学生号“1042”选修的全部课程的学生号

 

9 repalce

 数据库的TEST字段的值为345，567，789

要求以逗号分割，然后前面要加上M，

显示如下：

select TEST FROM Table

TEST

M345,M567,M789

: select   replace(‘M’+ test, ‘，’， ‘，M’ ) from tb   用‘，M’填充‘，’

10   （CSDN学习）

有一张表Table：ID    姓名      收款日期     收款额

我需要按每两周合计“收款额”，从2006年1月8日开始。得到的结果应该是：

ID    姓名     结算起始日期    结算终止日期  结算金额

“结算起始日期”就是每两周的开始日期；“结算终止日期”就是每两周的结束日期；“结算金额”就是两周之内的合计。


select id,姓名,
dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期) as 结算起始日期,
dateadd(day,13,dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期)) as 结算终止日期,
sum(收款额) as 结算金额
from table1
where 收款日期 > '2006-01-08'
group by id,姓名,datepart(week,收款日期)/2

 

declare @i datetime
declare @j numeric(15,4)
 set @i=getdate()-14
 set @j=0
while @i<getdate()
begin
set @j=@j+(select 收款额 from table1 where 收款日期=@i )
set @i=@i+1
end
select ID，姓名，getdate()-14 as'结算起始日期'，getdate() as'结算终止日期',@j as'结算金额'
from table1
group by ID

 

11

现想求各个地区(areano)，分三个价格档次，对物料出现次数进行汇总。
列出次数最多的前2名，及余下的作为其它一起合计。

declare @t1 table (itemno varchar (10),price decimal(6,2))
declare @t2 table (itemno varchar (10),areano varchar(10))

insert @t1 select '2801',3.1
union all select '2802',4.1
union all select '2803',5.1

union all select '5201',7.1
union all select '5202',8.1
union all select '5203',9.1
union all select '5204',9.5

union all select '5601',15.1
union all select '5602',16.1
union all select '5603',17.1

insert @t2 select '2801','0001'
union all select '2802','0001'
union all select '2802','0001'
union all select '2803','0001'
union all select '2803','0001'

union all select '5201','0001'
union all select '5201','0001'
union all select '5202','0001'
union all select '5202','0001'
union all select '5203','0001'
union all select '5204','0001'

union all select '5601','0002'
union all select '5601','0002'
union all select '5602','0002'
union all select '5602','0002'
union all select '5602','0002'

select * from @t1 a inner join @t2 b on  a.itemno=b.itemno

declare @t table (
价格档次 varchar(30),
areano   varchar(10),
itemno   varchar(10),
次数 int,
[比率(百分比)] int,
flag int
)

insert @t
select
case when a.price<6 then '小于6'
  when a.price>=6 and a.price<10 then '6至10'
  when a.price>=10 then '大于10'
  end as 价格档次,
b.areano,
a.itemno,
count(*) as 次数,0,0
from @t1 a,@t2 b
where a.itemno=b.itemno
group by
case when a.price<6 then '小于6'
  when a.price>=6 and a.price<10 then '6至10'
  when a.price>=10 then '大于10'
  end ,
b.areano,
a.itemno

select * from @t

update t
set flag=1
from @t t
where itemno in (select top 2 itemno from @t where 价格档次=t.价格档次 order by 次数 desc,itemno)

update t
set [比率(百分比)]=100*次数/(select sum(次数) from @t where 价格档次=t.价格档次)
from @t t

select * from @t

 

 

 

 

 

 

select
价格档次,
min(areano) as areano,  
case when flag=1 then itemno else '其它' end as itemno,   
sum(次数) as 次数,
sum([比率(百分比)]) as [比率(百分比)]
from @t t
group by 价格档次,case when flag=1 then itemno else '其它' end
order by 价格档次,sum(次数) desc