数据库表与表中数据比对的SQL语句怎么写呢?各位大侠帮忙进来看一下哈,先谢谢啦

程序员小哈 吉林大学 2011-05-12 03:00:48
麻烦大家帮忙看看哈、

如果在VC程序中我求得了,如下一些K值。K=1.15 、 3.46 、7.2 、 8.53 、9.74 、10.29,
要求数据库中的非空的数据K在上面那些K中都存在,也就是可以查询出SampleID为3、5、10
这三条数据,允许K存在一定偏差,偏差大小就是表中对应的KOffSet

我求出来的那些数据我觉得,我可以先插入一个表中,然后再进行SQL比对,不知道合理不?
如果合理,那这条SQL该怎么写呢?先谢谢大家了



这是我这个问题之前的一个帖子,问题已经解决了,不过那个帖子是部分匹配,也就是一个值匹配,现在要每条数据的所有值都匹配,而且每条数据的K的个数不一样多,问题比较麻烦。
http://topic.csdn.net/u/20110105/09/f657046f-57ac-4a50-a685-8f8f29d1e7d0.html
...全文
198 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
程序员小哈 2011-06-07
自己再顶一下,憋在这里好久啦
回复
程序员小哈 2011-05-20
问题还没有解决,顶一下,大家再帮忙看看呀,哪里描述不清楚我再补充呀
回复
--小F-- 2011-05-13
真的没看懂 只需要发测试数据和所需要的结果 以及 得到结果的一些规则就行了 不要太长 不然就不知道什么意思了
回复
程序员小哈 2011-05-13
[Quote=引用 6 楼 acherat 的回复:]

SQL code

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Library]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_Library](
[SampleID] [int] IDENTITY(1,1) NO……
[/Quote]


其实,我是用VC求一谱图的峰位置,比如谱图中峰在“1.15,3.46,7.2,8.53,9.74,10.29”的时间位置上,而库中,就是上面脚本中录入了一些物质的峰位置(时间),我想看看这个谱图中出现的峰“1.15,3.46,7.2,8.53,9.74,10.29”,对应库中的哪些物质(其中表的第二列是空气湿度,),比如:当前谱图中有3.46这个值,而这条数据“INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',30,3.45,0.05)” 恰好3.46 between 3.45-0.05 and 3.45+0.05 中间,那么这条数据就符合要求。


但是这条数据就不符合:
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',30,5.45,0.03,8.56,0.03)
虽然8.56在偏差范围内,但是第一个值5.45没有匹配的,所以不满足要求

对于上面的几个值:“1.15,3.46,7.2,8.53,9.74,10.29” 如果不考虑湿度关系,应该能到如下几条数据:
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',30,3.45,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',50,3.5,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',40,7.2,0.03,8.5,0.03,9.72,0.03)

得到这三条,湿度我可以
Where Humidity in (
Select Top 1 Humidity From tb_Library
Group by Humidity
Order by min(abs(Humidity - @Humidity )))限定了,就是如何匹配还不知道,麻烦你帮我看看吧,谢谢
回复
AcHerat 2011-05-13

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Library]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_Library](
[SampleID] [int] IDENTITY(1,1) NOT NULL,
[SampleName] [nvarchar](50) NOT NULL,
[Humidity] [float] NOT NULL,
[K1] [float] NOT NULL,
[K1OffSet] [float] NOT NULL,
[K2] [float] NULL,
[K2OffSet] [float] NULL,
[K3] [float] NULL,
[K3OffSet] [float] NULL,
[K4] [float] NULL,
[K4OffSet] [float] NULL
) ON [PRIMARY]
END

GO

INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',30,3.45,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',40,3.6,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',50,3.5,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',30,5.45,0.03,8.56,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',40,5.5,0.03,8.6,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',50,5.60,0.03,8.46,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',30,7.12,0.03,8.45,0.03,9.65,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',40,7.2,0.03,8.5,0.03,9.72,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',50,7.18,0.03,8.48,0.03,9.85,0.03)


你按这个脚本说下你想怎么做,最终的结果是怎么样的!
回复
程序员小哈 2011-05-13
[Quote=引用 8 楼 fredrickhu 的回复:]

真的没看懂 只需要发测试数据和所需要的结果 以及 得到结果的一些规则就行了 不要太长 不然就不知道什么意思了
[/Quote]

测试数据及表结构
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Library]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_Library](
[SampleID] [int] IDENTITY(1,1) NOT NULL,
[SampleName] [nvarchar](50) NOT NULL,
[Humidity] [float] NOT NULL,
[K1] [float] NOT NULL,
[K1OffSet] [float] NOT NULL,
[K2] [float] NULL,
[K2OffSet] [float] NULL,
[K3] [float] NULL,
[K3OffSet] [float] NULL,
[K4] [float] NULL,
[K4OffSet] [float] NULL
) ON [PRIMARY]
END

GO

INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',30,3.45,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',40,3.6,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',50,3.5,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',30,5.45,0.03,8.56,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',40,5.5,0.03,8.6,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',50,5.60,0.03,8.46,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',30,7.12,0.03,8.45,0.03,9.65,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',40,7.2,0.03,8.5,0.03,9.72,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',50,7.18,0.03,8.48,0.03,9.85,0.03)



期待结果:
K1、K2、K3、K4的值在"1.15,3.46,7.2,8.53,9.74,10.29"中存在(匹配)的,返回结果如下:
'TNT',30,3.45,0.05
'TNT',50,3.5,0.05
'黑火药',40,7.2,0.03,8.5,0.03,9.72,0.03

这回描述少了一些,再帮忙看看哈
回复
程序员小哈 2011-05-12
[Quote=引用 2 楼 acherat 的回复:]

SQL code

--类似于:


create table tb(id int,ic decimal(12,2),os decimal(12,2))
insert into tb
select 1,3.45,0.05 union all
select 2,4.5,0.2 union all
select 3,7.2,0.05 union all
select 4,9.3,0.2
go

d……
[/Quote]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UspOutputData]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UspOutputData]
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int

set nocount on
set @objectId=object_id(@tablename)

if @objectId is null -- 判断对象是否存在
begin
print ''The object not exists''
return
end
set @objectname=rtrim(object_name(@objectId))

if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print ''object not in current database''
return
end

if OBJECTPROPERTY(@objectId,''IsTable'') < > 1 -- 判断对象是否是table
begin
print ''The object is not table''
return
end

select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80

if @ident is not null
print ''SET IDENTITY_INSERT ''+@TableName+'' ON''

declare syscolumns_cursor cursor

for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid

open syscolumns_cursor
set @column=''''
set @columndata=''''
fetch next from syscolumns_cursor into @name,@xtype

while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理

begin
set @column=@column+case when len(@column)=0 then'''' else '',''end+@name

set @columndata=@columndata+case when len(@columndata)=0 then '''' else '','''','''',''
end

+case when @xtype in(167,175) then ''''''''''''''''''+''+@name+''+'''''''''''''''''' --varchar,char
when @xtype in(231,239) then ''''''N''''''''''''+''+@name+''+'''''''''''''''''' --nvarchar,nchar
when @xtype=61 then ''''''''''''''''''+convert(char(23),''+@name+'',121)+'''''''''''''''''' --datetime
when @xtype=58 then ''''''''''''''''''+convert(char(16),''+@name+'',120)+'''''''''''''''''' --smalldatetime
when @xtype=36 then ''''''''''''''''''+convert(char(36),''+@name+'')+'''''''''''''''''' --uniqueidentifier
else @name end

end

end

fetch next from syscolumns_cursor into @name,@xtype

end

close syscolumns_cursor
deallocate syscolumns_cursor

set @sql=''set nocount on select ''''insert ''+@tablename+''(''+@column+'') values(''''as ''''--'''',''+@columndata+'','''')'''' from ''+@tablename

print ''--''+@sql
exec(@sql)

if @ident is not null
print ''SET IDENTITY_INSERT ''+@TableName+'' OFF''

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MISDB]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MISDB](
[id] [int] IDENTITY(1,1) NOT NULL,
[HumidityId] [int] NOT NULL,
[K1] [float] NOT NULL,
[K2] [float] NULL,
[K3] [float] NULL,
[OffSet] [float] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Library]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_Library](
[SampleID] [int] IDENTITY(1,1) NOT NULL,
[SampleName] [nvarchar](50) NOT NULL,
[Humidity] [float] NOT NULL,
[K1] [float] NOT NULL,
[K1OffSet] [float] NOT NULL,
[K2] [float] NULL,
[K2OffSet] [float] NULL,
[K3] [float] NULL,
[K3OffSet] [float] NULL,
[K4] [float] NULL,
[K4OffSet] [float] NULL
) ON [PRIMARY]
END

GO

INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',30,3.45,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',40,3.6,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet)
VALUES ('TNT',50,3.5,0.05)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',30,5.45,0.03,8.56,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',40,5.5,0.03,8.6,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet)
VALUES ('RDX',50,5.60,0.03,8.46,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',30,7.12,0.03,8.45,0.03,9.65,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',40,7.2,0.03,8.5,0.03,9.72,0.03)
INSERT INTO tb_Library (SampleName,Humidity,K1,K1OffSet,K2,K2OffSet,K3,K3OffSet)
VALUES ('黑火药',50,7.18,0.03,8.48,0.03,9.85,0.03)



我弄了一个我的表的脚本,用我数据吧
回复
程序员小哈 2011-05-12
[Quote=引用 2 楼 acherat 的回复:]

SQL code

--类似于:


create table tb(id int,ic decimal(12,2),os decimal(12,2))
insert into tb
select 1,3.45,0.05 union all
select 2,4.5,0.2 union all
select 3,7.2,0.05 union all
select 4,9.3,0.2
go

d……
[/Quote]

不好意思,我安你的SQL语句没有移植成功,一个值的我理解了,三个或者多个值的,每行值的个数都不一样,我该怎么做呢?
我怎么能把你的SQL语句和我的
Select * From tb_Library
Where Humidity in (
Select Top 1 Humidity From tb_Library
Group by Humidity
Order by min(abs(Humidity - 31)))
AND ( (abs(ISNULL(K1,0)-8.45)<K1OffSet)
OR (abs(ISNULL(K2,0)-8.45)<K2OffSet)
OR (abs(ISNULL(K3,0)-8.45)<K3OffSet))

这个结合到一起呢?期待你的回复,谢谢
回复
程序员小哈 2011-05-12
[Quote=引用 2 楼 acherat 的回复:]

SQL code

--类似于:


create table tb(id int,ic decimal(12,2),os decimal(12,2))
insert into tb
select 1,3.45,0.05 union all
select 2,4.5,0.2 union all
select 3,7.2,0.05 union all
select 4,9.3,0.2
go

d……
[/Quote]

这么快就回复了啊,先谢谢呀,对我来说,有点复杂,我先体会一下,满意了,就给分哈
回复
AcHerat 2011-05-12

--类似于:


create table tb(id int,ic decimal(12,2),os decimal(12,2))
insert into tb
select 1,3.45,0.05 union all
select 2,4.5,0.2 union all
select 3,7.2,0.05 union all
select 4,9.3,0.2
go

declare @str varchar(8000)
set @str = '3.46,7.2'
;with cte as
(
select substring(@str,number,charindex(',',@str+',',number) - number) as cnt
from master..spt_values
where [type] = 'p' and number between 1 and len(@str)
and substring(',' + @str,number,1) = ','
)

select a.*
from tb a join cte b on cast(b.cnt as decimal(12,2)) between (a.ic - a.os) and (a.ic + a.os)

drop table tb


/*

id ic os
----------- --------------------------------------- ---------------------------------------
1 3.45 0.05
3 7.20 0.05

(2 行受影响)
回复
AcHerat 2011-05-12
1.15 、 3.46 、7.2 、 8.53 、9.74 、10.29

1# 可以将这些数据按固定符号分割作为一个临时表#t

2# 再用#t里的数据去匹配你表的数据,符合条件的取出来就OK!
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2011-05-12 03:00
社区公告
暂无公告