select a.* from tb a join (select 物品编号 from tb where 单号='001') b on a.单号=b.单号 where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
修正关联字段 select a.* from tb a join (select 物品编号 from tb where 单号='001') b on a.物品编号=b.物品编号 where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-07-03 14:53:43 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([单号] varchar(3),[物品编号] int,[重量] int) insert [tb] select '001',201,3 union all select '001',202,4 union all select '001',203,8 union all select '002',201,1 union all select '002',202,9 union all select '002',203,8 union all select '003',201,2 union all select '003',202,3 union all select '003',203,7 --------------开始查询-------------------------- select * from tb t where exists(select 1 from tb where 物品编号=t.物品编号 and abs(t.重量-重量)=1 and 单号='001' ) ----------------结果---------------------------- /* 单号 物品编号 重量 ---- ----------- ----------- 003 201 2 003 202 3 003 203 7(3 行受影响) */
if object_id('[tb]') is not null drop table [tb] go create table [tb]([单号] varchar(3),[物品编号] int,[重量] int) insert [tb] select '001',201,3 union all select '001',202,4 union all select '001',203,8 union all select '002',201,1 union all select '002',202,9 union all select '002',203,8 union all select '003',201,2 union all select '003',202,3 union all select '003',203,7select a.* from tb a join (select * from tb where 单号='001') b on a.物品编号=b.物品编号 where abs(a.重量-b.重量)<=1 and a.单号!=b.单号 /** 单号 物品编号 重量 ---- ----------- ----------- 002 203 8 003 201 2 003 202 3 003 203 7(4 行受影响) **/--只要单号 select distinct a.单号 from tb a join (select * from tb where 单号='001') b on a.物品编号=b.物品编号 where abs(a.重量-b.重量)<=1 and a.单号!=b.单号 /** 单号 ---- 002 003(2 行受影响) **/
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [单号] varchar(3), [物品编号] int, [重量] int ) go insert [test] select '001',201,3 union all select '001',202,4 union all select '001',203,8 union all select '002',201,1 union all select '002',202,9 union all select '002',203,8 union all select '003',201,2 union all select '003',202,3 union all select '003',203,7 go declare @DH varchar(5) set @DH='001' select * from test a where exists(select 1 from test b where 单号=@DH and a.物品编号=b.物品编号 and ABS(a.重量-b.重量)=1) /* 单号 物品编号 重量 003 201 2 003 202 3 003 203 7 */
join
(select 物品编号 from tb where 单号='001') b
on a.单号=b.单号
where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
select a.* from tb a
join
(select 物品编号 from tb where 单号='001') b
on a.物品编号=b.物品编号
where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-07-03 14:53:43
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([单号] varchar(3),[物品编号] int,[重量] int)
insert [tb]
select '001',201,3 union all
select '001',202,4 union all
select '001',203,8 union all
select '002',201,1 union all
select '002',202,9 union all
select '002',203,8 union all
select '003',201,2 union all
select '003',202,3 union all
select '003',203,7
--------------开始查询--------------------------
select * from tb t where exists(select 1 from tb where 物品编号=t.物品编号 and abs(t.重量-重量)=1 and 单号='001' )
----------------结果----------------------------
/* 单号 物品编号 重量
---- ----------- -----------
003 201 2
003 202 3
003 203 7(3 行受影响)
*/
go
create table [tb]([单号] varchar(3),[物品编号] int,[重量] int)
insert [tb]
select '001',201,3 union all
select '001',202,4 union all
select '001',203,8 union all
select '002',201,1 union all
select '002',202,9 union all
select '002',203,8 union all
select '003',201,2 union all
select '003',202,3 union all
select '003',203,7select a.* from tb a
join
(select * from tb where 单号='001') b
on a.物品编号=b.物品编号
where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
/**
单号 物品编号 重量
---- ----------- -----------
002 203 8
003 201 2
003 202 3
003 203 7(4 行受影响)
**/--只要单号
select distinct a.单号 from tb a
join
(select * from tb where 单号='001') b
on a.物品编号=b.物品编号
where abs(a.重量-b.重量)<=1 and a.单号!=b.单号
/**
单号
----
002
003(2 行受影响)
**/
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[单号] varchar(3),
[物品编号] int,
[重量] int
)
go
insert [test]
select '001',201,3 union all
select '001',202,4 union all
select '001',203,8 union all
select '002',201,1 union all
select '002',202,9 union all
select '002',203,8 union all
select '003',201,2 union all
select '003',202,3 union all
select '003',203,7
go
declare @DH varchar(5)
set @DH='001'
select * from test a
where exists(select 1 from test b
where 单号=@DH and
a.物品编号=b.物品编号 and ABS(a.重量-b.重量)=1)
/*
单号 物品编号 重量
003 201 2
003 202 3
003 203 7
*/