sql例题附代码测试数据.docx
《sql例题附代码测试数据.docx》由会员分享，可在线阅读，更多相关《sql例题附代码测试数据.docx（62页珍藏版）》请在冰豆网上搜索。
sql例题附代码测试数据
/**
第一题:
表中有以下資料
季別客戶款式單號品名
11Q3FIL2513164010-0646男梭織運動上衣
11Q3FIL2513164010-0655男梭織運動上衣（兩面穿）
SP10HIN8023609-0155女平針背心
SP10HIN8023610-0120女背心
FA09HIN8022109-0059男平織上衣
FA09HIN8022109-0058男針織上衣
怎麼得出的最大單號,結果為:
11Q3FIL2513164010-0655男梭織運動上衣（兩面穿）
SP10HIN8023610-0120女背心
FA09HIN8022109-0059男平織上衣
**/
--code:
--createtabletb
--（JBvarchar（20）,
--KHvarchar（20）,
--KSvarchar（20）,
--DHvarchar（20）,
--PMnvarchar（50））
--insertintotb
--select'11Q3','FIL','25131640','10-0646','男梭織運動上衣'unionall
--select'11Q3','FIL','25131640','10-0655','男梭織運動上衣（兩面穿）'unionall
--select'SP10','HIN','80236','09-0155','女平針背心'unionall
--select'SP10','HIN','80236','10-0120','女背心'unionall
--select'FA09','HIN','80221','09-0059','男平織上衣'unionall
--select'FA09','HIN','80221','09-0058','男針織上衣'
--SELECT*FROMTB
--select*fromtbwhereDHin（selectmax（DH）fromtbgroupbyJB）
/**
第二题:
--订单号，商品分类id,商品sku,销售数量
orderuid,classid,sku,amount
so01,1,a1,10
so02,1,a2,5
so03,1,a3,15
so04,2,b1,10
so05,2,b2,20
so06,2,b3,30
so07,3,c1,10
so08,3,c2,5
so09,3,c3,10
so10,4,d2,1
so11,1,a2,10（不同的订单购商品明细一样）
so11,3,c2,5（不同的订单购商品明细一样）
要求:
按销售量统计，按分类分组，取销售前位
结果（横着排sql）：
classid,sku1,sku2
1,a2,a3
2,b2,b3
3,c1,c2
4,d2
结果（竖着排sql2）
classid,sku
1,a2
1,a3
2,b2
2,b3
3,c1
3,c2
4,d2
CREATETABLEGoods
（orderuidVARCHAR（10）,
classidINT,
skuVARCHAR（10）,
amountINT）
GO
INSERTINTOGoodsVALUES（'so01',1,'a1',10）
INSERTINTOGoodsVALUES（'so02',1,'a2',5）
INSERTINTOGoodsVALUES（'so03',1,'a3',15）
INSERTINTOGoodsVALUES（'so04',2,'b1',10）
INSERTINTOGoodsVALUES（'so05',2,'b2',20）
INSERTINTOGoodsVALUES（'so06',2,'b3',30）
INSERTINTOGoodsVALUES（'so07',3,'c1',10）
INSERTINTOGoodsVALUES（'so08',3,'c2',5）
INSERTINTOGoodsVALUES（'so09',3,'c3',10）
INSERTINTOGoodsVALUES（'so10',4,'d2',1）
INSERTINTOGoodsVALUES（'so11',1,'a2',10）
INSERTINTOGoodsVALUES（'so11',3,'c2',5）
解法一：
usestudents3
;
withcas
（
selectsum（amount）assums,classid,sku
fromGoods
groupbyclassid,sku）
selectclassid,sku
from（selectclassid,sku,row_number（）over（partitionbyclassidorderbysumsdesc）asrowfromc）t
whererow=1orrow=2
解法二:
usestudents3
go
droptable#temp
selectsum（amount）assum,classid,skuinto#tempfromGoodsgroupbyclassid,sku
selectclassid,skufrom（selectclassid,sku,row_number（）over（partitionbyclassidorderbysumdesc）asrownumfrom#temp）t
whererownum=1orrownum=2
**/
第三题:
--要查询出列C#01课程比课程高信息
--表
/***
S#C#SCORE
010180.0
010290.0
010399.0
020170.0
020260.0
020380.0
030180.0
030280.0
030380.0
040150.0
040230.0
040320.0
050176.0
050287.0
060131.0
060334.0
070289.0
070398.0
--想要输出的结果
S#C#SCORE
020170.0
020260.0
020380.0
040150.0
040230.0
040320.0
........
表
createtableSC（S#varchar（10）,C#varchar（10）,scoredecimal（18,1））
insertintoSCvalues（'01','01',80）
insertintoSCvalues（'01','02',90）
insertintoSCvalues（'01','03',99）
insertintoSCvalues（'02','01',70）
insertintoSCvalues（'02','02',60）
insertintoSCvalues（'02','03',80）
insertintoSCvalues（'03','01',80）
insertintoSCvalues（'03','02',80）
insertintoSCvalues（'03','03',80）
insertintoSCvalues（'04','01',50）
insertintoSCvalues（'04','02',30）
insertintoSCvalues（'04','03',20）
insertintoSCvalues（'05','01',76）
insertintoSCvalues（'05','02',87）
insertintoSCvalues（'06','01',31）
insertintoSCvalues（'06','03',34）
insertintoSCvalues（'07','02',89）
insertintoSCvalues（'07','03',98）
go
解法一:
SELECT*
FROMSCASa
WHERE
NOTEXISTS（SELECT1FROMSCASbWHERES#='01'ANDNOTEXISTS（SELECT1FROMSCWHERES#=a.S#ANDC#=b.C#ANDscore）
解法二:
select*fromSCwhereS#in（
selectS#fromSCawhereC#='01'andexists（select1fromScbwherea.s#=b.s#anda.score>b.scoreandb.C#='02'）
）
usestudents3
select*fromSCwhereS#in
（selectS#fromSCasawhereC#='01'andexists（selectS#fromSCasbwherea.S#=b.S#anda.score>b.scoreandb.C#='02'））
***/
/**
第四题：
我想建一个通过传递参数创建表的存储过程，现在存储过程创建好了，但是用命令执行的时候总出错，代码如下：
createprocproc1
（
@tnamevarchar（50）
）
AS
BEGIN
Declare@tablenameAsvarchar（50）,@sqlAsvarchar（5000）
set@tablename=@tname
set@sql='createtable'+@tablename+'（idintIDENTITY（1,1）PRIMARYKEY,unamenVarChar（50）null）'
exec（@sql）
END
这个是存储过程，存储过程本身是没有问题的
我执行的时候用execdbo.proc1'test'可以实现，但execdbo.proc1'123'就不行
会报错说
消息102，级别15，状态1，第1行
'123'附近有语法错误。
这是为什么？
改成nvchar类型的也不行，到底执行存储过程的时候怎么加参数
解：
主要是因为被认为是数字,而不是一个有效的名称.加上[123]即可。
**/
--第五题:
/**
SQL多行相同值，只显示第一行的数据
显示效果：
110
120
133
150
220
需要显示的效果是：
110
20
33
50
220
createtableNum
（idint,scoreint）
insertintoNum
select1,10unionall
select1,20unionall
select1,33unionall
select1,50unionall
select2,20
解法一：
;
withcteas
（
select*,rowNum=ROW_NUMBER（）over（partitionbyidorderbyscore）fromNum
）
selectcasewhenrowNum=1thencast（idasvarchar）else''endASid,scorefromcte
解法二:
;withcteas（
selectrid=row_number（）over（partitionbyidorderbyscore）,*fromNum
）
select
casewhenrid=1thenltrim（id）
else''
end,scorefromcte
解法三：
usestudents3
droptable#temp
select*,rowNum=ROW_NUMBER（）over（partitionbyidorderbyscore）into#tempfromNum
selectcasewhenrownum=1thenltrim（id）
else''
endasid,scorefrom#temp
**/
/**
第六题:
请教各位高人个问题。
我想根据一个时间段，列出时间段内每一天的日期（日期+星期）
例如：
@begintime='2011-9-1'
@endtime='2011-9-30'
我想通过上面的选择列出下面结果
2011-9-12011-9-22011-9-32011-9-42011-9-52011-9-62011-9-7。
。
。
。
。
等等
星期四星期五星期六星期日星期一星期二星期三。
。
。
。
。
等等
解法一:
declare@sdatedatetime
declare@edatedatetime
set@sdate='2011-09-01'
set@edate='2011-09-30'
select
日期=dateadd（dd,num,@sdate）,星期=datename（weekday,dateadd（dd,num,@sdate））
from
（selectisnull（（selectcount
（1）fromsysobjectswhereidwhere
dateadd（dd,num,@sdate）<=@edate
orderby日期
解法二:
declare@startDatedatetime
declare@endDatedatetime
SELECT@startDate='2011-09-01',@endDate='2011-09-30'
;WITHtbAS（
SELECT@startDateAS'date'
UNIONall
SELECTDATEADD（DAY,1,date）FROMtbWHEREDATE<@endDate
）
SELECTtb.date,datename（weekday,tb.date）fromtb
**/
/**
第七题:
使用EXISTS和NOTEXISTS查找交集与差集
使用EXISTS和NOTEXISTS引入的子查询可用于两种集合原理的操作：
交集与差集。
两个集合的交集包含同时属于两个原集合的所有元素。
差集包含只属于两个集合中的第一个集合的元素。
city列中authors和publishers的交集是作者和出版商共同居住的城市的集合。
USEpubs
SELECTDISTINCTcity
FROMauthors
WHEREEXISTS
（SELECT*
FROMpublishers
WHEREauthors.city=publishers.city）
下面是结果集：
city
--------
Berkeley
（1row（s）affected）
当然，该查询可以写成一个简单的联接。
USEpubs
SELECTDISTINCTauthors.city
FROMauthorsINNERJOINpublishers
ONauthors.city=publishers.city
city列中authors和publishers的差集是作者所居住的、但没有出版商居住的所有城市的集合，也就是除Berkeley以外的所有城市。
USEpubs
SELECTDISTINCTcity
FROMauthors
WHERENOTEXISTS
（SELECT*
FROMpublishers
WHEREauthors.city=publishers.city）
该查询也可以写成：
USEpubs
SELECTDISTINCTcity
FROMauthors
WHEREcityNOTIN
（SELECTcity
FROMpublishers）
**/
/**
第八题:
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'1'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'N'ASCHECK_RESULT,'1'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'2'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'3'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'4'CHECK_ITEM_GROUP_ID
例如CHECK_ITEM_GROUP_ID为的记录，在CHECK_RESULT中，同时有'Y'和'N'的值，这种情况，我只需要拿CHECK_RESULT='N'那条记录
该SQL应该怎么写。
不能在WHERE中写CHECK_RESULT='N'这个条件
解法一:
;
withcteas
（
selectHDID,CHECK_RESULT,CHECK_ITEM_GROUP_ID,rownum=row_number（）over（partitionbyCHECK_ITEM_GROUP_IDorderbycast（CHECK_ITEM_GROUP_IDasint））from
（
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'1'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'N'ASCHECK_RESULT,'1'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'2'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'3'CHECK_ITEM_GROUP_ID
UNIONALL
SELECT'E598695C-2478-4DBB-8C8F-0663315B90B8'ASHDID,'Y'ASCHECK_RESULT,'4'CHECK_ITEM_GROUP_ID
）t
）
selectHDID,CHECK_RESULT,CHECK_ITEM_GROUP_IDfromctewhererownum=
case
whencast（CHECK_ITEM_GROUP_IDasint）=1then2
else1
end
HDIDCHECK_RESULTCHECK_ITEM_GROUP_ID
---------------------------------------------------------------------------------
E598695C-2478-4DBB-8C8F-0663315B90B8N1
E598695C-2478-4DBB-8C8F-0663315B90B8Y2
E598695C-2478-4DBB-8C8F-0663315B90B8Y3
E598695C-2478-4DBB-8C8F-0663315B90B8Y4
（4行受影响）
**/
/**
第九题：
表结构为
IDBIDISMustResult
10trueOK
21trueNO
31,2truenull
42,3truenull
以ID为4的记录为例，需要查询出ID为2,3的Result
BID的数据类型为nvarchar
使用关键字In无法进行查询!
在sql中该如何查询！
使用sql语句，不是C#的代码?
解法一:
createtabletb2（IDint,BIDvarchar（20）,ISMustvarchar（10）,Resultvarchar（10））
insertintotb2select1,'0','true','OK'
insertintotb2select2,'1','true','NO'
insertintotb2select3,'1,2','true',null
insertintotb2select4,'2,3','true',null
go
declare@sqlvarchar（2000）
select@sql='select*fromtb2whereIDin（'+bID+'）'fromtb2
exec（@sql）
**/
/**
第十题:
请教各位：
数据记录是这样的
1
2
3
4
5
12
17
18
19
20
25
请问sqlserver2000如何显示成这样-5，，-20，
解法一:
declare@ttable（numint）
insertinto@tselect1
unionallselect
升级会员 