Question
1
Can you use a batch SQL or store procedure to calculating the Number of Days in a Month ?
I am trying to find a way to calculate the total number of days in each month .
I am trying to avoid using a case statement, as it fails for February on leap years.
Is there a way to do it using the date functions in SQL Server?
*/
-- (1)
create proc sp_DayOfthisMonth ( @year int , @month int )
as
begin
declare @startDate char ( 10 ), @endDate char ( 10 )
set @startDate = cast ( @year as char ( 4 )) + ' - ' + cast ( @month as char ( 2 )) + ' -1 '
set @endDate = cast ( @year as char ( 4 )) + ' - ' + cast ( @month + 1 as char ( 2 )) + ' -1 '
select datediff ( day , cast ( @startDate as datetime ), cast ( @endDate as datetime ))
end
理解题意,也想到了使用DateDiff这样的日期处理函数。
但是由于传入的参数是整数而非日期类型,当传入参数错误时缺乏对异常的捕获和控制处理。
例如:
exec sp_DayOfthisMonth 2006 , 13
exec sp_DayOfthisMonth 2006 , - 1 将会发生数据类型转换错误。
并且由于在计算下一月份时没有使用DateAdd函数,而是简单的让 @month加1 ,
当传入月份参数为12时将会出错,也即该存储过程无法计算12月份的天数。
exec sp_DayOfthisMonth 2006 , 12
-- (2)
create procedure pro_monthcalucate( @month int , @daysnum int output)
as
if (( @month = 1 ) or ( @month = 3 ) or ( @month = 5 ) or ( @month = 7 ) or ( @month = 8 ) or ( @month = 10 ) or ( @month = 12 ))
set @daysnum = 31
else
if ( @month = 2 )
set @daysnum = 28
else
if (( @month = 2 ) or ( @month = 4 ) or ( @month = 6 ) or ( @month = 9 ) or ( @month = 11 ))
set @daysnum = 30
else
begin
print ' the wrong month '
set @daysnum = 0
end
declare @numofmonth int
exec pro_monthcalucate - 2 , @numofmonth output
print @numofmonth
思维不够严谨,只简单的想到月份,没有考虑到闰年的情况。
2004年的2月份是29天,而2006年的2月份只有28天。
对SQL Server的内置日期函数不熟悉,没有考虑到可以使用DateDiff函数求日期的差值。
编程缺乏技巧,长串的(( @month = 1 ) or ( @month = 3 ) or ( @month = 5 ) or ( @month = 7 ) or ( @month = 8 ) or ( @month = 10 ) or ( @month = 12 ))
可以写成 @month in ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ),代码简洁易读。
-- 穷举法
create proc sp_getMonthDays @year int
as
select datediff ( day , ' January 1,2006 ' , ' February 1,2006 ' ) as ' January ' ,
datediff ( day , ' February 1, ' + convert ( varchar , @year ), ' March 1, ' + convert ( varchar , @year )) as ' February ' ,
datediff ( day , ' March 1,2006 ' , ' April 1,2006 ' ) as ' March ' ,
datediff ( day , ' April 1,2006 ' , ' May 1,2006 ' ) as ' April ' ,
datediff ( day , ' May 1,2006 ' , ' June 1,2006 ' ) as ' May ' ,
datediff ( day , ' June 1,2006 ' , ' July 1,2006 ' ) as ' June ' ,
datediff ( day , ' July 1,2006 ' , ' August 1,2006 ' ) as ' July ' ,
datediff ( day , ' August 1,2006 ' , ' September 1,2006 ' ) as ' August ' ,
datediff ( day , ' September 1,2006 ' , ' October 1,2006 ' ) as ' September ' ,
datediff ( day , ' October 1,2006 ' , ' November 1,2006 ' ) as ' October ' ,
datediff ( day , ' November 1,2006 ' , ' December 1,2006 ' ) as ' November ' ,
datediff ( day , ' December 1,2006 ' , ' January 1,2007 ' ) as ' December '
-- (3)
CREATE FUNCTION fun_daysofmonth ( @DATE datetime )
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @daysofmonth int
set @daysofmonth = datediff ( day , @Date , dateadd ( month , 1 , @Date ))
RETURN ( @daysofmonth )
END ;
想到了使用DateDiff和DateAdd函数解题,但思维还不够严谨细致。
当下月天数比上月少时,会出错。
例如: select dbo.fun_daysofmonth( ' 2006-1-31 ' )
参考答案:
create proc sp_getDaysOfMonth
@dateInput datetime
as
declare @dateFirstDay datetime
set @dateFirstDay = convert ( varchar , year ( @dateInput )) + ' - ' + convert ( varchar , month ( @dateInput )) + ' -01 '
select month ( @dateInput ) as ' Month ' , datediff ( day , @dateFirstDay , dateadd ( month , 1 , @dateFirstDay )) as ' Days '
go
exec sp_getDaysOfMonth ' 2006-4-6 '
/**/ /**/ /**/ /* Question 2
Can you use a SQL statement to calculating it?
How can I print "10 to 20" for books that sell for between $10 and $20,
"unknown" for books whose price is null, and "other" for all other prices?
You can use the table titles in database pubs.
*/
-- (1)
select title_id,title,
price = case
when price < 20 and price > 10 then ' 10 to 20 '
when price is null then ' unknown '
else ' other '
end
from dbo.titles
大多数的人都忽略了 <= 和 >= 。
参考答案:
select title, ' Price Category ' =
case
when price between 10 and 20 then ' 10 to 20 '
when price is null then ' unknown '
else ' other '
end
from titles
/**/ /**/ /**/ /* Question 3
Can you use a SQL statement to finding duplicate values?
How can I find authors with the same last name?
You can use the table authors in database pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2
(1 row(s) affected)
*/
-- (1)
select au_lname, count ( * ) number_dups
from authors
group by au_lname
大多数人都只是取出au_lname出现的次数,而没有使用having子句选择出重复的au_lname。
-- (2)
select au_lname,number_dups
from ( select au_lname,number_dups = count ( 1 ) from authors group by au_lname) as tmp
where number_dups >= 2
虽然答案是正确的,但明显没有掌握having子句的用法。
参考答案:
select au_lname, count ( * ) as ' number_dups '
from authors
group by au_lname
having count ( * ) > 1
/**/ /**/ /**/ /* Question 4
Can you create a cross-tab report in my SQL Server?
How can I get the report about sale quantity for each store and each quarter
and the total sale quantity for each quarter at year 1993?
You can use the table sales and stores in database pubs.
Table Sales record all sale detail item for each store, column store_id is the id of each store,
ord_date is the order date of each sale item, and column qty is the sale quantity.
Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
---------------------------------------- ----------- ----------- -------------------------------
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0
Fricative Bookshop 60 35 0 0 25
Total 250 60 165 0 25
*/
-- (1)
drop table #stor_qty
select isnull (stores.stor_name, ' totle ' ) as stor_name, isnull ( datename (qq,sales.ord_date), 0 ) as quater, sum (sales.qty) as qty
into #stor_qty
from stores
join sales on stores.stor_id = sales.stor_id
where year (sales.ord_date) = 1993
group by (stores.stor_name), datename (qq,sales.ord_date) with cube
select stor_name, isnull ( [ 0 ] , 0 ) as ' totle ' , isnull ( [ 1 ] , 0 ) as ' Qtr1 ' , isnull ( [ 2 ] , 0 ) as ' Qtr2 ' , isnull ( [ 3 ] , 0 ) as ' Qtr3 ' , isnull ( [ 4 ] , 0 ) as ' Qtr4 ' ,( isnull ( [ 1 ] , 0 ) + isnull ( [ 2 ] , 0 ) + isnull ( [ 3 ] , 0 ) + isnull ( [ 4 ] , 0 )) as ' totle '
from
( select stor_name,qty,quater from #stor_qty
) as pro
pivot
( sum (qty) for quater in ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )) as pvt
基本掌握了with cube和pivot的用法,并会使用,但技巧还有待提升,结果集多了一列total。
在这个查询中并不需要使用临时表来完成,用子查询就可以了。
参考答案:
-- SQL Server 2000生成交叉表
SELECT stor_name,
SUM (qty) AS ' Total ' ,
SUM ( CASE datepart (qq, ord_date) WHEN 1 THEN qty ELSE 0 END ) AS Qtr1,
SUM ( CASE datepart (qq, ord_date) WHEN 2 THEN qty ELSE 0 END ) AS Qtr2,
SUM ( CASE datepart (qq, ord_date) WHEN 3 THEN qty ELSE 0 END ) AS Qtr3,
SUM ( CASE datepart (qq, ord_date) WHEN 4 THEN qty ELSE 0 END ) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year (ord_date) = 1993
GROUP BY stor_name
Union
SELECT ' Total ' ,
SUM (qty) AS ' Total ' ,
SUM ( CASE datepart (qq, ord_date) WHEN 1 THEN qty ELSE 0 END ) AS Qtr1,
SUM ( CASE datepart (qq, ord_date) WHEN 2 THEN qty ELSE 0 END ) AS Qtr2,
SUM ( CASE datepart (qq, ord_date) WHEN 3 THEN qty ELSE 0 END ) AS Qtr3,
SUM ( CASE datepart (qq, ord_date) WHEN 4 THEN qty ELSE 0 END ) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year (ord_date) = 1993
-- SQL Server 2005生成交叉表
select stor_name, isnull ( [ 0 ] , 0 ) as ' Total ' ,
isnull ( [ 1 ] , 0 ) as ' Qtr1 ' , isnull ( [ 2 ] , 0 ) as ' Qtr2 ' ,
isnull ( [ 3 ] , 0 ) as ' Qtr3 ' , isnull ( [ 4 ] , 0 ) as ' Qtr4 '
from
(
select isnull (t.stor_name, ' Total ' ) as ' stor_name ' ,
isnull ( datepart (qq, ord_date), 0 ) as ' Qtr ' , sum (qty) as ' qty '
from sales s
join stores t on s.stor_id = t.stor_id
where year (s.ord_date) = 1993
group by datepart (qq, ord_date), t.stor_name with cube
) as tmp
pivot
(
sum (qty) for Qtr in ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
) as pvt
/**/ /**/ /**/ /* Question 5
How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,
each row have a row number, how can you do that?
Result:
line-no title_id
----------- --------
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
8 PC1035
9 PC8888
10 PC9999
11 PS1372
12 PS2091
13 PS2106
14 PS3333
15 PS7777
16 TC3218
17 TC4203
18 TC7777
*/
-- (1)
declare @tmp table
(
line_no int identity ,
title_id varchar ( 6 )
)
insert into @tmp
select title_id from titles
select * from @tmp
drop table #tmp
select identity ( int , 1 , 1 ) as ' line-no ' ,title_id into #tmp from titles
order by title_id
select * from #tmp
参考答案:
-- SQL Server 2000
select count ( * ) as ' line-no ' ,a.title_id
from titles a join titles b on a.title_id > b.title_id
group by a.title_id
-- SQL Server 2005
select row_number() over ( order by title_id asc ) as ' line-no ' , title_id
from titles
/**/ /**/ /**/ /* Question 6
How can I list all California authors regardless of whether they have written a book?
In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each
author written.
CA behalf of california in table authors.
*/
-- (1)
select au_lname,au_fname,titau. *
from authors au
join titleauthor titau on au.au_id = titau.au_id and au.state = ' CA '
如果使用内连接,那么就只有在titleauthor表中有记录的作者才会被取出。
-- (2)
select au_id,au_lname,au_fname from authors a
where state = ' CA '
and exists ( select au_id from titleauthor t
where a.au_id = t.au_id)
select au_lname,au_fname,state
from dbo.authors
where state = ' CA ' and au_id in
(
select au_id from dbo.titleauthor
)
与内连接类似。
参考答案:
select a.au_fname ,a.au_lname, b.title_id
from authors a
left outer join titleauthor as b on a.au_id = b.au_id
where a.state = ' CA '
/**/ /**/ /**/ /* Question 7
How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
In database pubs, use three table stores,sales and titles to implement this requestment.
Now I want to get the result as below:
stor_id stor_name
------- ----------------------------------------
...
7896 Fricative Bookshop
...
...
...
*/
-- score : 0
select st.stor_id,st.stor_name
from dbo.stores st
join dbo.sales sl on st.stor_id = sl.stor_id
join dbo.titles tl on sl.title_id = tl.title_id and (tl.type = ' business ' and tl.type = ' trad_cook ' )
-- score : 0
select distinct a.stor_id , a.stor_name
from stores a
inner join sales b on (a.stor_id = b.stor_id)
inner join titles c on (b.title_id = c.title_id and c.type = ' bussiness ' or c.type = ' mod_cook ' )
select sal.stor_id, stor_name
from sales sal
join stores sto on sal.stor_id = sto.stor_id
join titles til on sal.title_id = til.title_id
where til. [ type ] = ' bussiness ' or til. [ type ] = ' mod_cook '
select distinct stores.stor_id,stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
where type in ( ' business ' , ' mod_cook ' )
-- another one , score: 0
begin tran Question7
select stor_id, stor_name
from stores
where stor_id in (
select stor_id
from sales
where title_id in (
select title_id
from titles
where type = ' business ' or type = ' mod_cook '
)
)
commit tran Question7
-- score: 6
with Stor_TilteType(stor_id,stor_name,title_type) as
( select st.stor_id,st.stor_name,ti.type
from stores st
join sales sa on st.stor_id = sa.stor_id
join titles ti on sa.title_id = ti.title_id
)
select distinct stor_id,stor_name
from Stor_TilteType
where title_type = ' business '
and stor_id in ( select stor_id
from Stor_TilteType
where title_type = ' mod_cook '
)
-- score: 8
select distinct stores.stor_id, stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
and titles.type = ' business '
intersect
select distinct stores.stor_id, stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
and titles.type = ' mod_cook '
-- score: 10
select distinct st.stor_id,st.stor_name
from stores st
inner join sales ss1 on ss1.stor_id = st.stor_id
inner join titles tit1 on ss1.title_id = tit1.title_id
where tit1. [ type ] = ' business ' and exists
(
select *
from sales ss2
inner join titles tit2 on ss2.title_id = tit2.title_id
where st.stor_id = ss2.stor_id and tit2. [ type ] = ' mod_cook '
)
-- 参考答案
select s.stor_id, st.stor_name
from sales s
join titles t on s.title_id = t.title_id and t.type in ( ' mod_cook ' , ' business ' )
join stores st on s.stor_id = st.stor_id
group by s.stor_id, st.stor_name
having count ( distinct t.type) = 2
select stor_id, stor_name from
stores
where
exists
(
select * from
sales join titles on sales.title_id = titles.title_id
where titles. [ type ] = ' business '
and stores.stor_id = sales.stor_id
)
and
exists
(
select * from
sales join titles on sales.title_id = titles.title_id
where titles. [ type ] = ' mod_cook '
and stores.stor_id = sales.stor_id
)
/**/ /**/ /**/ /* Question 8
How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
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
Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6 8
9 11
...
*/
-- 2
declare @temp table (id int )
declare @count int
set @count = 1
while @count <= 20
begin
insert into @temp values ( @count )
set @count = @count + 1
end
select id - 1 as ' Missing after ' ,id + 1 as ' Missing before '
from ( select id from @temp
except
select id from test
) as t
select id as ' Missing after ' , id + 2 as ' Missing before '
from test t1
where not exists
(
select * from test t2
where t1.id = t2.id - 1
)
-- score: 6
create proc proc_ShowMissing
as
declare @MissingInfo table
(
Missing_after int primary key ,
Missing_before int
)
declare @num_1 int , @num_2 int
declare cNum cursor FAST_FORWARD
for
select id from test
open cNum
fetch cNum into @num_1
fetch cNum into @num_2
-- print cast(@num_1 as varchar(10)) + '''' + cast(@num_2 as varchar(10))
while @@Fetch_Status = 0
begin
if ( @num_1 + 1 ) <> ( @num_2 )
begin
insert into @MissingInfo values ( @num_1 , @num_2 )
end
else
print ' contignous '
set @num_1 = @num_2
fetch cNum into @num_2
end
close cNum
deallocate cNum
select * from @MissingInfo
execute proc_ShowMissing
--
print ( ' Missing after Missing before ' )
print ( ' ------------- -------------- ' )
declare @iLast int , @iNext int , @iCurCount int
declare cTest CURSOR FAST_FORWARD
for select id
from test
open cTest
fetch cTest into @iCurCount
set @iLast = @iCurCount
while @@Fetch_Status = 0
begin
set @iNext = @iCurCount
if @iLast + 1 <> @iNext and @iLast <> @iNext
print cast ( @iLast as varchar ) + ' ' + cast ( @iNext as varchar )
set @iLast = @iNext
fetch cTest into @iCurCount
end
close cTest
deallocate cTest
-- score:10
select temp . [ Missing after ] , min (test.ID) as [ Missing before ] from
(
select ID as [ Missing after ] from test
where ID + 1 < ( select min (ID) from test t2 where t2.ID > test.ID)
) as temp
join test on temp . [ Missing after ] < test.ID
group by temp . [ Missing after ]
-- score: 10
select t1.id as ' Missing after ' ,t2.id as ' Minssing before ' from
(
select row_number() over ( order by id) as ' row_number ' ,id
from test) as t1
join ( select row_number() over ( order by id) as ' row_number ' ,id
from test) as t2
on t1.row_number = t2.row_number - 1
where t1.id <> t2.id - 1
-- 参考答案
select max (a.id) as "Missing After",
min (b.id) as "Missing Before"
from test a ,test b
where a.id < b.id
group by b.id
having min (b.id) - max (a.id) > 1
/**/ /**/ /**/ /* Question 9
How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book,
and another named type mean the type of books.
Now I want to get the result as below:
type title price
------------ ---------------------------------------------------------------------------------
business The Busy Executive's Database Guide 19.9900
...
...
...
...
*/
-- (1)
Select type,price,title
from titles
Where Price > ( select avg (price) from titles)
这样取得的是所有书的平均价格,而不是某一类书的平均价格。
-- (2)
with avgprice(type,price) as
(
select type, avg (price) as price
from titles t2
group by type
)
select distinct titles.type,titles.title,titles.price
from titles
inner join avgprice on (titles.type = avgprice.type and titles.price > avgprice.price)
使用CTE也可以实现,但如果取出的数据量很大会影响性能。
为什么要用distinct呢?
-- (3)
select a.type, a.title, a.price
from titles a
where a.price > (
select avg ( isnull (price, 0 )) -- 有价格为null的情况,算平均值时应该算上价格为null的书本
from titles b
where a.type = b.type
)
因为没有具体提及统计的需求,但这样严谨的作风非常值得大家学习。
参考答案:
select t.type, t.title, t.price
from titles t
where t.price >
( select avg (price) from titles tt where tt.type = t.type)
Can you use a batch SQL or store procedure to calculating the Number of Days in a Month ?
I am trying to find a way to calculate the total number of days in each month .
I am trying to avoid using a case statement, as it fails for February on leap years.
Is there a way to do it using the date functions in SQL Server?
*/
-- (1)
create proc sp_DayOfthisMonth ( @year int , @month int )
as
begin
declare @startDate char ( 10 ), @endDate char ( 10 )
set @startDate = cast ( @year as char ( 4 )) + ' - ' + cast ( @month as char ( 2 )) + ' -1 '
set @endDate = cast ( @year as char ( 4 )) + ' - ' + cast ( @month + 1 as char ( 2 )) + ' -1 '
select datediff ( day , cast ( @startDate as datetime ), cast ( @endDate as datetime ))
end
理解题意,也想到了使用DateDiff这样的日期处理函数。
但是由于传入的参数是整数而非日期类型,当传入参数错误时缺乏对异常的捕获和控制处理。
例如:
exec sp_DayOfthisMonth 2006 , 13
exec sp_DayOfthisMonth 2006 , - 1 将会发生数据类型转换错误。
并且由于在计算下一月份时没有使用DateAdd函数,而是简单的让 @month加1 ,
当传入月份参数为12时将会出错,也即该存储过程无法计算12月份的天数。
exec sp_DayOfthisMonth 2006 , 12
-- (2)
create procedure pro_monthcalucate( @month int , @daysnum int output)
as
if (( @month = 1 ) or ( @month = 3 ) or ( @month = 5 ) or ( @month = 7 ) or ( @month = 8 ) or ( @month = 10 ) or ( @month = 12 ))
set @daysnum = 31
else
if ( @month = 2 )
set @daysnum = 28
else
if (( @month = 2 ) or ( @month = 4 ) or ( @month = 6 ) or ( @month = 9 ) or ( @month = 11 ))
set @daysnum = 30
else
begin
print ' the wrong month '
set @daysnum = 0
end
declare @numofmonth int
exec pro_monthcalucate - 2 , @numofmonth output
print @numofmonth
思维不够严谨,只简单的想到月份,没有考虑到闰年的情况。
2004年的2月份是29天,而2006年的2月份只有28天。
对SQL Server的内置日期函数不熟悉,没有考虑到可以使用DateDiff函数求日期的差值。
编程缺乏技巧,长串的(( @month = 1 ) or ( @month = 3 ) or ( @month = 5 ) or ( @month = 7 ) or ( @month = 8 ) or ( @month = 10 ) or ( @month = 12 ))
可以写成 @month in ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ),代码简洁易读。
-- 穷举法
create proc sp_getMonthDays @year int
as
select datediff ( day , ' January 1,2006 ' , ' February 1,2006 ' ) as ' January ' ,
datediff ( day , ' February 1, ' + convert ( varchar , @year ), ' March 1, ' + convert ( varchar , @year )) as ' February ' ,
datediff ( day , ' March 1,2006 ' , ' April 1,2006 ' ) as ' March ' ,
datediff ( day , ' April 1,2006 ' , ' May 1,2006 ' ) as ' April ' ,
datediff ( day , ' May 1,2006 ' , ' June 1,2006 ' ) as ' May ' ,
datediff ( day , ' June 1,2006 ' , ' July 1,2006 ' ) as ' June ' ,
datediff ( day , ' July 1,2006 ' , ' August 1,2006 ' ) as ' July ' ,
datediff ( day , ' August 1,2006 ' , ' September 1,2006 ' ) as ' August ' ,
datediff ( day , ' September 1,2006 ' , ' October 1,2006 ' ) as ' September ' ,
datediff ( day , ' October 1,2006 ' , ' November 1,2006 ' ) as ' October ' ,
datediff ( day , ' November 1,2006 ' , ' December 1,2006 ' ) as ' November ' ,
datediff ( day , ' December 1,2006 ' , ' January 1,2007 ' ) as ' December '
-- (3)
CREATE FUNCTION fun_daysofmonth ( @DATE datetime )
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @daysofmonth int
set @daysofmonth = datediff ( day , @Date , dateadd ( month , 1 , @Date ))
RETURN ( @daysofmonth )
END ;
想到了使用DateDiff和DateAdd函数解题,但思维还不够严谨细致。
当下月天数比上月少时,会出错。
例如: select dbo.fun_daysofmonth( ' 2006-1-31 ' )
参考答案:
create proc sp_getDaysOfMonth
@dateInput datetime
as
declare @dateFirstDay datetime
set @dateFirstDay = convert ( varchar , year ( @dateInput )) + ' - ' + convert ( varchar , month ( @dateInput )) + ' -01 '
select month ( @dateInput ) as ' Month ' , datediff ( day , @dateFirstDay , dateadd ( month , 1 , @dateFirstDay )) as ' Days '
go
exec sp_getDaysOfMonth ' 2006-4-6 '
/**/ /**/ /**/ /* Question 2
Can you use a SQL statement to calculating it?
How can I print "10 to 20" for books that sell for between $10 and $20,
"unknown" for books whose price is null, and "other" for all other prices?
You can use the table titles in database pubs.
*/
-- (1)
select title_id,title,
price = case
when price < 20 and price > 10 then ' 10 to 20 '
when price is null then ' unknown '
else ' other '
end
from dbo.titles
大多数的人都忽略了 <= 和 >= 。
参考答案:
select title, ' Price Category ' =
case
when price between 10 and 20 then ' 10 to 20 '
when price is null then ' unknown '
else ' other '
end
from titles
/**/ /**/ /**/ /* Question 3
Can you use a SQL statement to finding duplicate values?
How can I find authors with the same last name?
You can use the table authors in database pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2
(1 row(s) affected)
*/
-- (1)
select au_lname, count ( * ) number_dups
from authors
group by au_lname
大多数人都只是取出au_lname出现的次数,而没有使用having子句选择出重复的au_lname。
-- (2)
select au_lname,number_dups
from ( select au_lname,number_dups = count ( 1 ) from authors group by au_lname) as tmp
where number_dups >= 2
虽然答案是正确的,但明显没有掌握having子句的用法。
参考答案:
select au_lname, count ( * ) as ' number_dups '
from authors
group by au_lname
having count ( * ) > 1
/**/ /**/ /**/ /* Question 4
Can you create a cross-tab report in my SQL Server?
How can I get the report about sale quantity for each store and each quarter
and the total sale quantity for each quarter at year 1993?
You can use the table sales and stores in database pubs.
Table Sales record all sale detail item for each store, column store_id is the id of each store,
ord_date is the order date of each sale item, and column qty is the sale quantity.
Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
---------------------------------------- ----------- ----------- -------------------------------
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0
Fricative Bookshop 60 35 0 0 25
Total 250 60 165 0 25
*/
-- (1)
drop table #stor_qty
select isnull (stores.stor_name, ' totle ' ) as stor_name, isnull ( datename (qq,sales.ord_date), 0 ) as quater, sum (sales.qty) as qty
into #stor_qty
from stores
join sales on stores.stor_id = sales.stor_id
where year (sales.ord_date) = 1993
group by (stores.stor_name), datename (qq,sales.ord_date) with cube
select stor_name, isnull ( [ 0 ] , 0 ) as ' totle ' , isnull ( [ 1 ] , 0 ) as ' Qtr1 ' , isnull ( [ 2 ] , 0 ) as ' Qtr2 ' , isnull ( [ 3 ] , 0 ) as ' Qtr3 ' , isnull ( [ 4 ] , 0 ) as ' Qtr4 ' ,( isnull ( [ 1 ] , 0 ) + isnull ( [ 2 ] , 0 ) + isnull ( [ 3 ] , 0 ) + isnull ( [ 4 ] , 0 )) as ' totle '
from
( select stor_name,qty,quater from #stor_qty
) as pro
pivot
( sum (qty) for quater in ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )) as pvt
基本掌握了with cube和pivot的用法,并会使用,但技巧还有待提升,结果集多了一列total。
在这个查询中并不需要使用临时表来完成,用子查询就可以了。
参考答案:
-- SQL Server 2000生成交叉表
SELECT stor_name,
SUM (qty) AS ' Total ' ,
SUM ( CASE datepart (qq, ord_date) WHEN 1 THEN qty ELSE 0 END ) AS Qtr1,
SUM ( CASE datepart (qq, ord_date) WHEN 2 THEN qty ELSE 0 END ) AS Qtr2,
SUM ( CASE datepart (qq, ord_date) WHEN 3 THEN qty ELSE 0 END ) AS Qtr3,
SUM ( CASE datepart (qq, ord_date) WHEN 4 THEN qty ELSE 0 END ) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year (ord_date) = 1993
GROUP BY stor_name
Union
SELECT ' Total ' ,
SUM (qty) AS ' Total ' ,
SUM ( CASE datepart (qq, ord_date) WHEN 1 THEN qty ELSE 0 END ) AS Qtr1,
SUM ( CASE datepart (qq, ord_date) WHEN 2 THEN qty ELSE 0 END ) AS Qtr2,
SUM ( CASE datepart (qq, ord_date) WHEN 3 THEN qty ELSE 0 END ) AS Qtr3,
SUM ( CASE datepart (qq, ord_date) WHEN 4 THEN qty ELSE 0 END ) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year (ord_date) = 1993
-- SQL Server 2005生成交叉表
select stor_name, isnull ( [ 0 ] , 0 ) as ' Total ' ,
isnull ( [ 1 ] , 0 ) as ' Qtr1 ' , isnull ( [ 2 ] , 0 ) as ' Qtr2 ' ,
isnull ( [ 3 ] , 0 ) as ' Qtr3 ' , isnull ( [ 4 ] , 0 ) as ' Qtr4 '
from
(
select isnull (t.stor_name, ' Total ' ) as ' stor_name ' ,
isnull ( datepart (qq, ord_date), 0 ) as ' Qtr ' , sum (qty) as ' qty '
from sales s
join stores t on s.stor_id = t.stor_id
where year (s.ord_date) = 1993
group by datepart (qq, ord_date), t.stor_name with cube
) as tmp
pivot
(
sum (qty) for Qtr in ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
) as pvt
/**/ /**/ /**/ /* Question 5
How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,
each row have a row number, how can you do that?
Result:
line-no title_id
----------- --------
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
8 PC1035
9 PC8888
10 PC9999
11 PS1372
12 PS2091
13 PS2106
14 PS3333
15 PS7777
16 TC3218
17 TC4203
18 TC7777
*/
-- (1)
declare @tmp table
(
line_no int identity ,
title_id varchar ( 6 )
)
insert into @tmp
select title_id from titles
select * from @tmp
drop table #tmp
select identity ( int , 1 , 1 ) as ' line-no ' ,title_id into #tmp from titles
order by title_id
select * from #tmp
参考答案:
-- SQL Server 2000
select count ( * ) as ' line-no ' ,a.title_id
from titles a join titles b on a.title_id > b.title_id
group by a.title_id
-- SQL Server 2005
select row_number() over ( order by title_id asc ) as ' line-no ' , title_id
from titles
/**/ /**/ /**/ /* Question 6
How can I list all California authors regardless of whether they have written a book?
In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each
author written.
CA behalf of california in table authors.
*/
-- (1)
select au_lname,au_fname,titau. *
from authors au
join titleauthor titau on au.au_id = titau.au_id and au.state = ' CA '
如果使用内连接,那么就只有在titleauthor表中有记录的作者才会被取出。
-- (2)
select au_id,au_lname,au_fname from authors a
where state = ' CA '
and exists ( select au_id from titleauthor t
where a.au_id = t.au_id)
select au_lname,au_fname,state
from dbo.authors
where state = ' CA ' and au_id in
(
select au_id from dbo.titleauthor
)
与内连接类似。
参考答案:
select a.au_fname ,a.au_lname, b.title_id
from authors a
left outer join titleauthor as b on a.au_id = b.au_id
where a.state = ' CA '
/**/ /**/ /**/ /* Question 7
How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
In database pubs, use three table stores,sales and titles to implement this requestment.
Now I want to get the result as below:
stor_id stor_name
------- ----------------------------------------
...
7896 Fricative Bookshop
...
...
...
*/
-- score : 0
select st.stor_id,st.stor_name
from dbo.stores st
join dbo.sales sl on st.stor_id = sl.stor_id
join dbo.titles tl on sl.title_id = tl.title_id and (tl.type = ' business ' and tl.type = ' trad_cook ' )
-- score : 0
select distinct a.stor_id , a.stor_name
from stores a
inner join sales b on (a.stor_id = b.stor_id)
inner join titles c on (b.title_id = c.title_id and c.type = ' bussiness ' or c.type = ' mod_cook ' )
select sal.stor_id, stor_name
from sales sal
join stores sto on sal.stor_id = sto.stor_id
join titles til on sal.title_id = til.title_id
where til. [ type ] = ' bussiness ' or til. [ type ] = ' mod_cook '
select distinct stores.stor_id,stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
where type in ( ' business ' , ' mod_cook ' )
-- another one , score: 0
begin tran Question7
select stor_id, stor_name
from stores
where stor_id in (
select stor_id
from sales
where title_id in (
select title_id
from titles
where type = ' business ' or type = ' mod_cook '
)
)
commit tran Question7
-- score: 6
with Stor_TilteType(stor_id,stor_name,title_type) as
( select st.stor_id,st.stor_name,ti.type
from stores st
join sales sa on st.stor_id = sa.stor_id
join titles ti on sa.title_id = ti.title_id
)
select distinct stor_id,stor_name
from Stor_TilteType
where title_type = ' business '
and stor_id in ( select stor_id
from Stor_TilteType
where title_type = ' mod_cook '
)
-- score: 8
select distinct stores.stor_id, stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
and titles.type = ' business '
intersect
select distinct stores.stor_id, stor_name
from stores
inner join sales on stores.stor_id = sales.stor_id
inner join titles on sales.title_id = titles.title_id
and titles.type = ' mod_cook '
-- score: 10
select distinct st.stor_id,st.stor_name
from stores st
inner join sales ss1 on ss1.stor_id = st.stor_id
inner join titles tit1 on ss1.title_id = tit1.title_id
where tit1. [ type ] = ' business ' and exists
(
select *
from sales ss2
inner join titles tit2 on ss2.title_id = tit2.title_id
where st.stor_id = ss2.stor_id and tit2. [ type ] = ' mod_cook '
)
-- 参考答案
select s.stor_id, st.stor_name
from sales s
join titles t on s.title_id = t.title_id and t.type in ( ' mod_cook ' , ' business ' )
join stores st on s.stor_id = st.stor_id
group by s.stor_id, st.stor_name
having count ( distinct t.type) = 2
select stor_id, stor_name from
stores
where
exists
(
select * from
sales join titles on sales.title_id = titles.title_id
where titles. [ type ] = ' business '
and stores.stor_id = sales.stor_id
)
and
exists
(
select * from
sales join titles on sales.title_id = titles.title_id
where titles. [ type ] = ' mod_cook '
and stores.stor_id = sales.stor_id
)
/**/ /**/ /**/ /* Question 8
How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
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
Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6 8
9 11
...
*/
-- 2
declare @temp table (id int )
declare @count int
set @count = 1
while @count <= 20
begin
insert into @temp values ( @count )
set @count = @count + 1
end
select id - 1 as ' Missing after ' ,id + 1 as ' Missing before '
from ( select id from @temp
except
select id from test
) as t
select id as ' Missing after ' , id + 2 as ' Missing before '
from test t1
where not exists
(
select * from test t2
where t1.id = t2.id - 1
)
-- score: 6
create proc proc_ShowMissing
as
declare @MissingInfo table
(
Missing_after int primary key ,
Missing_before int
)
declare @num_1 int , @num_2 int
declare cNum cursor FAST_FORWARD
for
select id from test
open cNum
fetch cNum into @num_1
fetch cNum into @num_2
-- print cast(@num_1 as varchar(10)) + '''' + cast(@num_2 as varchar(10))
while @@Fetch_Status = 0
begin
if ( @num_1 + 1 ) <> ( @num_2 )
begin
insert into @MissingInfo values ( @num_1 , @num_2 )
end
else
print ' contignous '
set @num_1 = @num_2
fetch cNum into @num_2
end
close cNum
deallocate cNum
select * from @MissingInfo
execute proc_ShowMissing
--
print ( ' Missing after Missing before ' )
print ( ' ------------- -------------- ' )
declare @iLast int , @iNext int , @iCurCount int
declare cTest CURSOR FAST_FORWARD
for select id
from test
open cTest
fetch cTest into @iCurCount
set @iLast = @iCurCount
while @@Fetch_Status = 0
begin
set @iNext = @iCurCount
if @iLast + 1 <> @iNext and @iLast <> @iNext
print cast ( @iLast as varchar ) + ' ' + cast ( @iNext as varchar )
set @iLast = @iNext
fetch cTest into @iCurCount
end
close cTest
deallocate cTest
-- score:10
select temp . [ Missing after ] , min (test.ID) as [ Missing before ] from
(
select ID as [ Missing after ] from test
where ID + 1 < ( select min (ID) from test t2 where t2.ID > test.ID)
) as temp
join test on temp . [ Missing after ] < test.ID
group by temp . [ Missing after ]
-- score: 10
select t1.id as ' Missing after ' ,t2.id as ' Minssing before ' from
(
select row_number() over ( order by id) as ' row_number ' ,id
from test) as t1
join ( select row_number() over ( order by id) as ' row_number ' ,id
from test) as t2
on t1.row_number = t2.row_number - 1
where t1.id <> t2.id - 1
-- 参考答案
select max (a.id) as "Missing After",
min (b.id) as "Missing Before"
from test a ,test b
where a.id < b.id
group by b.id
having min (b.id) - max (a.id) > 1
/**/ /**/ /**/ /* Question 9
How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book,
and another named type mean the type of books.
Now I want to get the result as below:
type title price
------------ ---------------------------------------------------------------------------------
business The Busy Executive's Database Guide 19.9900
...
...
...
...
*/
-- (1)
Select type,price,title
from titles
Where Price > ( select avg (price) from titles)
这样取得的是所有书的平均价格,而不是某一类书的平均价格。
-- (2)
with avgprice(type,price) as
(
select type, avg (price) as price
from titles t2
group by type
)
select distinct titles.type,titles.title,titles.price
from titles
inner join avgprice on (titles.type = avgprice.type and titles.price > avgprice.price)
使用CTE也可以实现,但如果取出的数据量很大会影响性能。
为什么要用distinct呢?
-- (3)
select a.type, a.title, a.price
from titles a
where a.price > (
select avg ( isnull (price, 0 )) -- 有价格为null的情况,算平均值时应该算上价格为null的书本
from titles b
where a.type = b.type
)
因为没有具体提及统计的需求,但这样严谨的作风非常值得大家学习。
参考答案:
select t.type, t.title, t.price
from titles t
where t.price >
( select avg (price) from titles tt where tt.type = t.type)