sql例题附代码测试数据.docx

1、sql例题附代码测试数据/*第一题:表中有以下資料季別客戶款式單號品名11Q3 FIL 25131640 10-0646 男梭織運動上衣11Q3 FIL 25131640 10-0655 男梭織運動上衣(兩面穿)SP10 HIN 80236 09-0155 女平針背心SP10 HIN 80236 10-0120 女背心FA09 HIN 80221 09-0059 男平織上衣FA09 HIN 80221 09-0058 男針織上衣怎麼得出的最大單號,結果為:11Q3 FIL 25131640 10-0655 男梭織運動上衣(兩面穿)SP10 HIN 80236 10-0120 女背心FA09 H

2、IN 80221 09-0059 男平織上衣*/-code:-create table tb-(JB varchar(20),- KH varchar(20),- KS varchar(20),- DH varchar(20),- PM nvarchar(50) - insert into tb - select 11Q3,FIL,25131640,10-0646,男梭織運動上衣 union all-select 11Q3,FIL, 25131640, 10-0655, 男梭織運動上衣(兩面穿) union all-select SP10, HIN, 80236, 09-0155, 女平針背心

3、 union all-select SP10, HIN, 80236, 10-0120, 女背心 union all-select FA09, HIN, 80221, 09-0059, 男平織上衣 union all-select FA09, HIN, 80221, 09-0058, 男針織上衣-SELECT * FROM TB-select * from tb where DH in (select max(DH)from tb group by JB)/*第二题:-订单号，商品分类id,商品sku,销售数量orderuid,classid,sku,amountso01,1,a1,10so0

4、2,1,a2,5so03,1,a3,15so04,2,b1,10so05,2,b2,20so06,2,b3,30so07,3,c1,10so08,3,c2,5so09,3,c3,10so10,4,d2,1so11,1,a2,10 (不同的订单购商品明细一样)so11,3,c2,5 (不同的订单购商品明细一样)要求:按销售量统计，按分类分组，取销售前位结果（横着排sql）：classid,sku1,sku21,a2,a32,b2,b33,c1,c24,d2结果（竖着排sql2）classid,sku1,a21,a32,b22,b33,c13,c24,d2CREATE TABLE Goods(or

5、deruid VARCHAR(10),classid INT,sku VARCHAR(10),amount INT)GOINSERT INTO Goods VALUES(so01,1,a1,10)INSERT INTO Goods VALUES(so02,1,a2,5)INSERT INTO Goods VALUES(so03,1,a3,15)INSERT INTO Goods VALUES(so04,2,b1,10)INSERT INTO Goods VALUES(so05,2,b2,20)INSERT INTO Goods VALUES(so06,2,b3,30)INSERT INTO G

6、oods VALUES(so07,3,c1,10)INSERT INTO Goods VALUES(so08,3,c2,5)INSERT INTO Goods VALUES(so09,3,c3,10)INSERT INTO Goods VALUES(so10,4,d2,1)INSERT INTO Goods VALUES(so11,1,a2,10)INSERT INTO Goods VALUES(so11,3,c2,5)解法一：use students3;with c as(select sum(amount) as sums,classid,skufrom Goodsgroup by cla

7、ssid,sku)select classid,skufrom(select classid,sku,row_number() over(partition by classid order by sums desc) as row from c) twhere row=1 or row=2解法二:use students3godrop table #tempselect sum(amount) as sum,classid,sku into #temp from Goods group by classid,skuselect classid,sku from (select classid

8、,sku ,row_number()over(partition by classid order by sum desc)as rownum from #temp )twhere rownum=1 or rownum=2*/第三题:- 要查询出列C# 01课程比课程高信息- 表/*S# C# SCORE01 01 80.001 02 90.001 03 99.002 01 70.002 02 60.002 03 80.003 01 80.003 02 80.003 03 80.004 01 50.004 02 30.004 03 20.005 01 76.005 02 87.006 01 3

9、1.006 03 34.007 02 89.007 03 98.0-想要输出的结果S# C# SCORE02 01 70.002 02 60.002 03 80.004 01 50.004 02 30.004 03 20.0. 表create table SC(S# varchar(10),C# varchar(10),score decimal(18,1) insert into SC values(01 , 01 , 80) insert into SC values(01 , 02 , 90) insert into SC values(01 , 03 , 99) insert into

10、 SC values(02 , 01 , 70) insert into SC values(02 , 02 , 60) insert into SC values(02 , 03 , 80) insert into SC values(03 , 01 , 80) insert into SC values(03 , 02 , 80) insert into SC values(03 , 03 , 80) insert into SC values(04 , 01 , 50) insert into SC values(04 , 02 , 30) insert into SC values(0

11、4 , 03 , 20) insert into SC values(05 , 01 , 76) insert into SC values(05 , 02 , 87) insert into SC values(06 , 01 , 31) insert into SC values(06 , 03 , 34) insert into SC values(07 , 02 , 89) insert into SC values(07 , 03 , 98) go解法一:SELECT *FROM SC AS a WHERE NOT EXISTS(SELECT 1 FROM SC AS b WHERE

12、 S#=01 AND NOT EXISTS(SELECT 1 FROM SC WHERE S#=a.S# AND C#=b.C# AND scoreb.score and b.C#=02) )use students3select * from SC where S# in(select S# from SC as a where C#=01and exists (select S# from SC as b where a.S#=b.S# and a.scoreb.score and b.C#=02 )*/*第四题：我想建一个通过传递参数创建表的存储过程，现在存储过程创建好了，但是用命令执行

13、的时候总出错，代码如下：create proc proc1(tname varchar(50)ASBEGINDeclare tablename As varchar(50),sql As varchar(5000)set tablename = tnameset sql=create table +tablename+(id int IDENTITY (1,1) PRIMARY KEY ,uname nVarChar(50) null)exec(sql)END这个是存储过程，存储过程本身是没有问题的我执行的时候用exec dbo.proc1 test可以实现，但exec dbo.proc1 1

14、23就不行会报错说消息102，级别15，状态1，第1 行123 附近有语法错误。这是为什么？改成nvchar类型的也不行，到底执行存储过程的时候怎么加参数解：主要是因为被认为是数字,而不是一个有效的名称.加上123 即可。*/-第五题:/*SQL 多行相同值，只显示第一行的数据显示效果：1 101 201 331 502 20需要显示的效果是：1 10 20 33 502 20 create table Num(id int ,score int)insert into Numselect 1,10 union allselect 1,20 union allselect 1,33 union

15、 allselect 1,50 union allselect 2,20解法一：;with cte as( select *,rowNum=ROW_NUMBER() over(partition by id order by score) from Num)select case when rowNum=1 then cast(id as varchar) else end AS id,score from cte解法二:;with cte as(select rid=row_number() over (partition by id order by score),* from Num)s

16、elect case when rid=1 then ltrim(id) else end,score from cte解法三：use students3drop table #tempselect *,rowNum=ROW_NUMBER() over(partition by id order by score) into #temp from Num select case when rownum=1 then ltrim(id)else end as id,score from #temp*/*第六题:请教各位高人个问题。我想根据一个时间段，列出时间段内每一天的日期（日期+星期）例如：b

17、egintime=2011-9-1 endtime=2011-9-30我想通过上面的选择列出下面结果2011-9-1 2011-9-2 2011-9-3 2011-9-4 2011-9-5 2011-9-6 2011-9-7 。等等 星期四星期五星期六星期日星期一星期二星期三。等等解法一:declare sdate datetimedeclare edate datetimeset sdate = 2011-09-01set edate = 2011-09-30select 日期= dateadd(dd,num,sdate) ,星期= datename(weekday , dateadd(dd

18、,num,sdate)from (select isnull(select count(1) from sysobjects where idt.id),0) as num from sysobjects t) awhere dateadd(dd,num,sdate)=edateorder by 日期解法二:declare startDate datetimedeclare endDate datetimeSELECT startDate = 2011-09-01 ,endDate = 2011-09-30;WITH tb AS (SELECT startDate AS dateUNION a

19、llSELECT DATEADD(DAY,1,date) FROM tb WHERE DATEendDate)SELECT tb.date , datename(weekday,tb.date)from tb*/*第七题:使用EXISTS 和NOT EXISTS 查找交集与差集使用EXISTS 和NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。差集包含只属于两个集合中的第一个集合的元素。city 列中authors 和publishers 的交集是作者和出版商共同居住的城市的集合。USE pubsSELECT DISTIN

20、CT cityFROM authorsWHERE EXISTS (SELECT * FROM publishers WHERE authors.city = publishers.city)下面是结果集：city-Berkeley(1 row(s) affected)当然，该查询可以写成一个简单的联接。USE pubsSELECT DISTINCT authors.cityFROM authors INNER JOIN publishersON authors.city = publishers.citycity 列中authors 和publishers 的差集是作者所居住的、但没有出版商居

21、住的所有城市的集合，也就是除Berkeley 以外的所有城市。USE pubsSELECT DISTINCT cityFROM authorsWHERE NOT EXISTS (SELECT * FROM publishers WHERE authors.city = publishers.city)该查询也可以写成：USE pubsSELECT DISTINCT cityFROM authorsWHERE city NOT IN (SELECT city FROM publishers)*/*第八题:SELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS

22、 HDID , Y AS CHECK_RESULT, 1 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , N AS CHECK_RESULT, 1 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , Y AS CHECK_RESULT, 2 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-066331

23、5B90B8 AS HDID , Y AS CHECK_RESULT, 3 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , Y AS CHECK_RESULT, 4 CHECK_ITEM_GROUP_ID例如CHECK_ITEM_GROUP_ID 为的记录，在CHECK_RESULT中，同时有Y和N的值，这种情况，我只需要拿CHECK_RESULT = N那条记录该SQL应该怎么写。不能在WHERE中写CHECK_RESULT = N这个条件解法一:; with cte as(s

24、elect HDID,CHECK_RESULT,CHECK_ITEM_GROUP_ID,rownum=row_number()over(partition by CHECK_ITEM_GROUP_ID order by cast( CHECK_ITEM_GROUP_ID as int)from (SELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , Y AS CHECK_RESULT, 1 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS H

25、DID , N AS CHECK_RESULT, 1 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , Y AS CHECK_RESULT, 2 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B90B8 AS HDID , Y AS CHECK_RESULT, 3 CHECK_ITEM_GROUP_IDUNION ALLSELECT E598695C-2478-4DBB-8C8F-0663315B

26、90B8 AS HDID , Y AS CHECK_RESULT, 4 CHECK_ITEM_GROUP_ID)t)select HDID,CHECK_RESULT,CHECK_ITEM_GROUP_ID from cte where rownum= case when cast(CHECK_ITEM_GROUP_ID as int)=1 then 2else 1endHDID CHECK_RESULT CHECK_ITEM_GROUP_ID- - -E598695C-2478-4DBB-8C8F-0663315B90B8 N 1E598695C-2478-4DBB-8C8F-0663315B

27、90B8 Y 2E598695C-2478-4DBB-8C8F-0663315B90B8 Y 3E598695C-2478-4DBB-8C8F-0663315B90B8 Y 4(4 行受影响)*/*第九题：表结构为 ID BID ISMust Result1 0 true OK2 1 true NO3 1,2 true null4 2,3 true null以ID 为4 的记录为例，需要查询出ID 为2,3的Result BID 的数据类型为nvarchar使用关键字In 无法进行查询!在sql 中该如何查询！使用sql 语句，不是C#的代码? 解法一:create table tb2(ID

28、int,BID varchar(20),ISMust varchar(10),Result varchar(10)insert into tb2 select 1,0,true,OKinsert into tb2 select 2,1,true,NOinsert into tb2 select 3,1,2,true,nullinsert into tb2 select 4,2,3,true,nullgodeclare sql varchar(2000)select sql=select * from tb2 where ID in(+bID+) from tb2exec(sql)*/*第十题:请教各位： 数据记录是这样的 1 2 3 4 5 12 17 18 19 20 25 请问sqlserver2000如何显示成这样-5，-20，解法一:declare t table(num int)insert into t select 1 union all select