在论坛中出现的比较难的sql有关问题：3

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。另外，考虑到前2篇太长，看起来不太方便，所以拆分为第3篇

http://bbs.csdn.net/topics/390619048

情境描述：根据表A里的物料去比对表B,然后表A根据A1括号里两个尺寸浪费最少来将A1替换成最省的物料。


表A用量需求表：Table A


A0(自增长ID)   A1(物料编号)   
-------------------------------
0                    ls001-(900*110)
1                    ls002-(200*300)
....


表B库存物料表: B1没有重复，可以当作ID来使用 Table B:

B1(库存物料)        B2(规格1)      B3（规格2）
----------------------------------------------
ls001-(700*200)     700            200
ls001-(910*140)     910            140
ls001-(920*120)     920            120
...                 ...            ...
ls002-(100*200)     100            200
ls002-(200*350)     200            350
ls002-(220*320)     220            320
...              
 
原理是：ls001取(920*120)的话浪费分别是左边920-900=20，右边120-110=10，总共浪费是30， 是ls001库存规格(700*200)，(910*140)，(920*120)里浪费最少的，ls002同理。

最后A1字段被替换后的效果如下：

A0(自增长ID)   A1(物料编号)   
-------------------------------
0              ls001-(920*120)
1              ls002-(220*320)
...

各位有什么好的方案或者算法可分享来学习借鉴一下 ^_^

我的解法：

drop table adrop table bcreate table a (a0 int,a1 varchar(100),a2 int,a3 int)insert into aSELECT 0,'ls001-(900*110)',900,110 UNION ALLSELECT 1,'ls002-(200*300)',200,300create table b (B1 varchar(100),B2 int,B3 int)insert into b SELECT 'ls001-(700*200)',700,200 UNION ALLSELECT 'ls001-(910*140)',910,140 UNION ALLSELECT 'ls001-(920*120)',920,120 UNION ALLSELECT 'ls002-(100*200)',100,200 UNION ALLSELECT 'ls002-(200*350)',200,350 UNION ALLSELECT 'ls002-(220*320)',220,320;with tas(select a0,a1,       substring(a1,1,charindex('-',a1)-1) as b1,       a2,a3       --substring(a1,charindex('(',a1)+1, charindex('*',a1)-charindex('(',a1)-1) as b2,       --substring(a1,charindex('*',a1)+1, charindex(')',a1)-charindex('*',a1)-1) as b3       from a),ttas(select t.a0,       t.a1,       b.b1,       row_number() over(partition by t.a1                              order by abs(t.a2-b.b2) + abs(t.a3 - b.b3)) as rownumfrom binner join t        on b.b1 like t.b1 + '%')select a0,b1 as a1from tt where rownum = 1/*a0	a10	ls001-(920*120)1	ls002-(220*320)*/

2、sql中怎么判断某个字段的值是否连续？

http://bbs.csdn.net/topics/390615670

比如：A表的字段AID的值为：1、2、4、5、7、8、10
怎么用sql查询出2、5、8的结果呢？
要查的结果就是查询这组数据从哪里开始不连续的。

我的解法：

create table A(AID int) insert into A(AID) select 1 union all select 2 union all select 4 union all select 5 union all select 7 union all select 8 union all select 10select aidfrom(select a.aid,       (select min(aid) from a aa where aa.aid > a.aid) min_aidfrom A )awhere aid +1 < min_aid/*aid258*/

3、关于日期条件出现的奇怪问题。

http://bbs.csdn.net/topics/390498925

select top 1 ekeyid,ProbeMaterial,Result,LabTestDate from PatientLabTestResults
where ProbeMaterial='Ca'  
  and LabTestDate between convert(datetime,2013/1/1) and
   convert(datetime,'2013/6/24') order by LabTestDate desc

这样查的出结果，变成
select top 1 ekeyid,ProbeMaterial,Result,LabTestDate from PatientLabTestResults 
where ProbeMaterial='Ca'  
  and LabTestDate between convert(datetime,2013/1/1) and
   convert(datetime,2013/6/24) order by LabTestDate desc

这样后就查不出结果了，其实只是去除了2013/6/24的'号而已，这个大家能理解是什么问题吗？

if OBJECT_ID('t') is not null   drop table tgocreate table t(d datetime)insert into tselect cast('2013-05-01' as datetime) as dunion allselect cast('2013-05-10' as datetime)/*1.通过查询计划能看出，SQL Server把下面的查询转化成了：select * from t where d >= convert(datetime,2013/5/1,0)  and d <=  convert(datetime,'2013/6/24',0) 也就是查询条件：   d >= convert(datetime,2013/5/1,0)  and d <=  convert(datetime,'2013/6/24',0) 进一步转化：    d >= '1901-02-07 00:00:00.000'  and d <=  2013-06-24 00:00:00.000 这样就能查询出结果集。 */select *from twhere d between convert(datetime,2013/5/1) and  convert(datetime,'2013/6/24')/*2.通过查询计划能看出，SQL Server把下面的查询转化成了：select * from t where d >= convert(datetime,2013/5/1,0)  and d <=  convert(datetime,2013/6/24,0) 也就是查询条件：   d >= convert(datetime,2013/5/1,0)  and d <=  convert(datetime,2013/6/24,0) 进一步转化：    d >= '1901-02-07 00:00:00.000'  and d <=  '1900-01-14 00:00:00.000' 由于SQL Server 把2013/6/24中的斜杠，当成了除号，也就是按除法计算了，比如：2013/6/24 就等于13，那么由于datetime默认值是默认值： 1900-01-01 00:00:00，     那么加上13后，就是1900-01-14 00:00:00.000，这样后就查不出结果了. */select *from twhere d between convert(datetime,2013/5/1) and  convert(datetime,2013/6/24)

其实就是，

2013/5/1 就是一个除法运算，结果为402。
2013/6/24 做除法运算后，就是13。

由于datetime数据类型的默认值为：'1900-01-01 00:00:00'，

所以上面的convert(datetime,2013/5/1)就是'1900-01-01 00:00:00' 再加上402，
就是'1901-02-07 00:00:00.000',

而convert(datetime,2013/6/24)就是是'1900-01-01 00:00:00' 再加上 13，
就是'1900-01-14 00:00:00.000',

所以就会查不出结果来。

所以，上面的2013/5/1 要写成 '2013/5/1',一定要加上引号。

select top 200 id, ClassId,Name,Price,BoxContain,BoxLength,BoxWidth,BoxHeight,CName,EName,CPack,PhotoFolder,EPack,0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08 from ProductData where ClassId=101 and BoxContain >0 and BoxContain is not null and round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) >=0 And round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) <= 10000 and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) >=10 and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) <=1000 and EPack='Window Box' order by id asc

select *from (select top 200 id, ClassId,Name,Price,BoxContain,BoxLength,BoxWidth,BoxHeight,CName,EName,CPack,PhotoFolder,EPack,0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08 from ProductData where ClassId=101 and BoxContain >0 and BoxContain is not null and round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) >=0 And round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) <= 10000  and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) >=10  and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) <=1000  and EPack='Window Box' order by id asc)twhere(	select max(id)     from	(	select top 200 id, ClassId,Name,Price,BoxContain,BoxLength,BoxWidth,BoxHeight,CName,EName,CPack,PhotoFolder,EPack,	0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08 	from ProductData 	where ClassId=101 and BoxContain >0 and BoxContain is not null 	and round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) >=0	 And round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3) <= 10000 	 and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) >=10 	 and (0.46*(round(((convert(decimal(10,2),BoxLength)*convert(decimal(10,2),BoxWidth) * convert(decimal(10,2),BoxHeight))/1000000),3))*35.32/BoxContain+Price/6*1.08) <=1000 	 and EPack='Window Box'          order by id asc	)t) > a

5、内连接后，如何分页。

http://bbs.csdn.net/topics/390617884

sqlserver 对一张表进行分页查询,但是还要通过第二章张表获取信息
比如有一张表
create table Student(
sid int primary key identity(1,1) ,
sname varchar(15) not null
)
第二张表
create table Comment(
id int primary key identity(1,1) ,
sid int not null
)
我需要对第二张表comment 进行分页查询，但是还要通过第一张表查询姓名
要改如何写sql代码

我用内连接后接下来该如何做

declare @page_size int;declare @page_num int;--比如：每页10条记录set @page_size = 10;--比如：先取第1页set @page_num = 1;select id,sid,snamefrom(select c.id,       c.sid,       s.sname,              --这里按照@@servername来排序，       --你可以根据需要按照id，sid，sname等字段来排序       (row_number() over(order by @@servername) - 1) / @page_size as rownumfrom commet cinner join student s        on c.sid = s.sid)twhere rownum = @page_num - 1

由于没有实验数据，下面通过sys.objects来实现：

declare @page_size int;declare @page_num int;--比如：每页10条记录set @page_size = 10;--比如：先取第1页set @page_num = 1;select *from(select *,          row_number() over(order by @@servername) as rownum,       --这里按照@@servername来排序，       --你可以根据需要按照id，sid，sname等字段来排序       (row_number() over(order by @@servername) - 1) / @page_size as pagenumfrom sys.objects)twhere pagenum = @page_num - 1