欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

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

更新时间: 2014-01-05 02:07:22 责任编辑: Author_N1

 

在论坛中出现的比较难的sql问题:3


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

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



1、MS-SQL 根据实际所需规格table去比对另一个库存table取浪费最少的数据

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 a
drop table b

create table a (a0 int,a1 varchar(100),a2 int,a3 int)

insert into a
SELECT 0,'ls001-(900*110)',900,110 UNION ALL
SELECT 1,'ls002-(200*300)',200,300


create table b (B1 varchar(100),B2 int,B3 int)

insert into b 
SELECT 'ls001-(700*200)',700,200 UNION ALL
SELECT 'ls001-(910*140)',910,140 UNION ALL
SELECT 'ls001-(920*120)',920,120 UNION ALL
SELECT 'ls002-(100*200)',100,200 UNION ALL
SELECT 'ls002-(200*350)',200,350 UNION ALL
SELECT 'ls002-(220*320)',220,320


;with t
as
(
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
),

tt
as
(
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 rownum
from b
inner join t
        on b.b1 like t.b1 + '%'
)

select a0,b1 as a1
from tt 
where rownum = 1
/*
a0	a1
0	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 10

select aid
from
(
select a.aid,
       (select min(aid) from a aa where aa.aid > a.aid) min_aid
from A 
)a
where aid +1 < min_aid
/*
aid
2
5
8
*/


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 t
go

create table t(d datetime)


insert into t
select cast('2013-05-01' as datetime) as d
union all
select 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 t
where 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 t
where 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',一定要加上引号。



4、查询出一段数据后判断记录里面的最大id,是否大于值a 查询语句如下:
http://bbs.csdn.net/topics/390619191
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

我的解法,适用于SQL Server 2000:

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
)t
where
(
	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,sname
from
(
select c.id,
       c.sid,
       s.sname,
       
       --这里按照@@servername来排序,
       --你可以根据需要按照id,sid,sname等字段来排序
       (row_number() over(order by @@servername) - 1) / @page_size as rownum
from commet c
inner join student s
        on c.sid = s.sid
)t
where 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 pagenum
from sys.objects
)t
where pagenum = @page_num - 1


上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:07:22
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/5859/
本文WWW.DOC100.NET DOC100.NET版权所有。