--1。得到当前月的天数
select datepart(dd,
dateadd(dd,-1, --2011-04-30 00:00:00.000
dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-' --2011-04-01 00:00:00.000
+cast(month(getdate()) as varchar)+'-01' as datetime) --2011-05-01 00:00:00.000
)))
--2.应用case划分 等级
select productid,productName,unitPrice,'价格级别'=case when unitprice is null then 'unknow'
when unitPrice between 10 and 20 then '10 to 20'
when unitPrice between 21 and 40 then '21 to 40'
when unitPrice between 41 and 60 then '41 to 60'
else '未划分级别'
end
from products
--3 得到相同姓 的人数，应用pubs中的authors表
select au_lname, number_duplicate=count(au_lname)
from authors group by au_lname
having count(au_lname) >1
--4 对pubs库 stores,sales 表按季度统计1993年的销售情况
select stor_name ,a.stor_id ,sum(qty) as 'total',
sum(case datepart(quarter,ord_date) when 1 then qty else 0 end) as 'qtr1',
sum(case datepart(quarter,ord_date) when 2 then qty else 0 end) as 'qtr2',
sum(case datepart(quarter,ord_date) when 3 then qty else 0 end) as 'qtr3',
sum(case datepart(quarter,ord_date) when 4 then qty else 0 end) as 'qtr4'
from stores a join sales b
on a.stor_id=b.stor_id
where convert(char(4),ord_date,121)='1993' --时间限制
group by stor_name ,a.stor_id --分组
order by total
--5 为结果集手动添加序号
--sql2005中方法
select row_number() as line_no ,title_id from titles
--sql2000中方法 ,但是不能用表变量 ，临时表可以
declare @tabletemp table (line_no int, title varchar(80))
select line_no=identity(int,1,1) ,title into @tabletemp
from titles
select * from @tabletemp
select line_no= identity(int,1,1),title_id into #t from titles
select * from #t
drop table #t
select convert(char(4),ord_date,121),ord_date ,datepart(quarter,ord_date) from sales
select convert(smalldatetime,'1993-01-01',121)
--9
---or语法是不正确的
select a.stor_id,a.stor_name ,c.type
from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business'
or c.type='mod_cook'
order by a.stor_id
--and语法不正确
select distinct a.stor_id, a.stor_name,c.type from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=a.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
order by a.stor_id
--方法1
select * from
(select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' ) a ,
(select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='mod_cook' ) b
where a.stor_id=b.stor_id
--方法2
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
--2比1的效率高很多， 1进行了两次相关查询，而2虽然也是两次但是明显的第二次缩小了范围
--方法一
select stor_id,stor_name from stores where stor_id in
(
select stor_id from sales a join titles b on a.title_id=b.title_id where type in('business','mod_cook') --business mod_cook是个或的运算判断
group by stor_id having count(distinct type)=2 --从数量上成为了且的判断
)
--方法二
select stor_id,stor_name from stores where stor_id in
(
select stor_id from sales a join titles b on a.title_id=b.title_id where type in('business')
and stor_id in(select stor_id from sales a join titles b on a.title_id=b.title_id where type in('mod_cook'))
)
--思路：两次in的选择，而且是嵌套的
--方法三
select stor_id,stor_name from stores where stor_id in
(
select stor_id from sales a join titles b on a.title_id=b.title_id where type in('business')
and exists (select stor_id from sales aa join titles bb on aa.title_id=bb.title_id where type in('mod_cook') and a.stor_id=stor_id)
)
--同方法二 in 相当于exist
--方法四
select stor_id,stor_name from stores where stor_id in
(
select stor_id from
(
select distinct stor_id from sales a join titles b on a.title_id=b.title_id where type in('business')
union all
select distinct stor_id from sales a join titles b on a.title_id=b.title_id where type in('mod_cook')
) a group by stor_id having count(*)=2 --同方法一
)
-- 10 创建如下表，得到不连接的值以及它后面的一值
create table test
( id int primary key )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go
--结果如下：
--6 8
--9 11
--14 18
select 'Missing before' =id,'Missing after' = (
select min(id)
from test a
where id>aa.id and id-1 not in(
select id
from test)) --select作为字段，得到大于id的最小值min, 并且前面值连续（id-1 not in )
from test aa
where id+1 not in( select id from test) --后面不连续， id+1不存在
and id<(select max(id) from test) --把最后一个值除去
--11 得到价格比平均价格高的 同类产品，
select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price
--思路：形成基表 b (得到avg), 然后连接查询
-----------------------------------------------------------
