记录成对进行比较

[disneyzhang]

序列号 行号 价钱 状态 图书编号

seqno lineno price status bookno

00001 1 10 进仓 A0001
00002 2 11 进仓 A0001
00003 3 12 进仓 A0001

00004 1 10 出仓 A0001
00005 2 11.5 出仓 A0001
00006 3 12.5 出仓 A0001

00007 1 13 进仓 A0001
00008 2 14 进仓 A0001
00009 3 15 进仓 A0001

00010 1 13 出仓 A0001
00011 2 14.5 出仓 A0001
00012 3 15 出仓 A0001

00013 1 23 进仓 A0001
00014 2 24 进仓 A0001
00015 3 25 进仓 A0001




上面是图书进出仓记录,请问如何写Sql 语句进行判断某一种"图书编号"进出仓的价钱是否相同 (成对进行比较),

即序列号00001与00004; 00002与00005; 00003与00006

序列号00007与00010; 00008与00011; 00009与00012 进行对比(行号,图书编号相同),

而序列号00013,00014,00015没有出仓记录,所以不需要比较

[中国风]

如果没有数量的情况下这样比较还是可以的..
有数量是就不行了。。

[中国风]

select
	*--定义要显示的列
from 
	(select
		*,(select count(1) from T where bookno=a.bookno and 状态=a.状态 and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where a.状态='进仓')T1
join
	(select
		*,(select count(1) from T where bookno=a.bookno and 状态=a.状态  and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where 	a.状态='出仓')T2
	on T1.bookno=t2.bookno and t1.con=t2.con and t1.价钱<>t2.价钱

[tim_spac]

create table BB (
   seqno char(5)
  ,lineno int
  ,price numeric(10,2)
  ,status char(2)
  ,bookno char(5)
)
select
   i_seqno = i.seqno
  ,o_seqno = o.seqno
  ,i_price = i.price 
  ,o_price = o.price 
  ,flag = cast(case when i.price = o.price then '相同' else '不同' end as char(2))
from BB i, BB o
where i.bookno=o.bookno and i.lineno=o.lineno -- 行号,图书编号相同
and i.status='进仓' and o.status='出仓'

[中国风]

或加上lineno

select
	*--定义要显示的列
from 
	(select
		*,(select count(1) from T where bookno=a.bookno and 状态=a.状态 and [lineno]=a.[lineno] and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where a.状态='进仓')T1
join
	(select
		*,(select count(1) from T where bookno=a.bookno and 状态=a.状态 and [lineno]=a.[lineno] and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where 	a.状态='出仓')T2
	on T1.bookno=t2.bookno and t1.con=t2.con and t1.[lineno] =t2.[lineno] and t1.价钱<>t2.价钱

[中国风]

create table T( 
		seqno nvarchar(5),
		[lineno] int,
		price numeric(18,2), 
		status nvarchar(5),
		bookno nvarchar(5)
	)

insert T select '00001', 1 ,10, '进仓' ,'A0001' 
insert T select '00002', 2 ,11 ,'进仓' ,'A0001' 
insert T select '00003', 3 ,12 ,'进仓' ,'A0001' 

insert T select '00004' ,1 ,10, '出仓', 'A0001' 
insert T select '00005', 2 ,11.5 ,'出仓' ,'A0001' 
insert T select '00006' ,3 ,12.5 ,'出仓' ,'A0001' 

insert T select '00007' ,1 ,13, '进仓','A0001' 
insert T select '00008' ,2, 14, '进仓' ,'A0001' 
insert T select '00009' ,3 ,15, '进仓' ,'A0001' 

insert T select '00010' , 1, 13 ,'出仓' ,'A0001' 
insert T select '00011' ,2, 14.5, '出仓' ,'A0001' 
insert T select '00012', 3,15 ,'出仓' ,'A0001' 

insert T select '00013', 1, 23 ,'进仓','A0001' 
insert T select '00014' ,2 ,24 ,'进仓' ,'A0001' 
insert T select '00015' ,3, 25 ,'进仓' ,'A0001' 




select
	*--定义要显示的列
from 
	(select
		*,(select count(1) from T where bookno=a.bookno and status=a.status and [lineno]=a.[lineno] and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where a.status='进仓')T1
join
	(select
		*,(select count(1) from T where bookno=a.bookno and status=a.status and [lineno]=a.[lineno] and [seqno]!>a.[seqno]) as con
	from 
		T a 
	where 	a.status='出仓')T2
	on T1.bookno=t2.bookno and t1.con=t2.con and t1.[lineno] =t2.[lineno] and t1.price<>t2.price


seqno lineno      price                status bookno con         seqno lineno      price                status bookno con         
----- ----------- -------------------- ------ ------ ----------- ----- ----------- -------------------- ------ ------ ----------- 
00002 2           11.00                进仓     A0001  1           00005 2           11.50                出仓     A0001  1
00003 3           12.00                进仓     A0001  1           00006 3           12.50                出仓     A0001  1
00008 2           14.00                进仓     A0001  2           00011 2           14.50                出仓     A0001  2

（所影响的行数为 3 行）

[disneyzhang]

非常感谢楼主两位朋友的回复,请问如果没有状态这个字段,根据seq no.可以进行判断吗?

表数据如果如下:

序列号 行号 价钱 图书编号

seqno lineno price bookno

00001 1 10 A0001
00002 2 11 A0001
00003 3 12 A0001

00004 1 10 A0001
00005 2 11.5 A0001
00006 3 12.5 A0001

00007 1 13 A0001
00008 2 14 A0001
00009 3 15 A0001

00010 1 13 A0001
00011 2 14.5 A0001
00012 3 15 A0001

00013 1 23 A0001
00014 2 24 A0001
00015 3 25 A0001

[tim_spac]

create table BB (
   seqno char(5)
  ,lineno int
  ,price numeric(10,2)
  ,status char(2)
  ,bookno char(5)
)
select
   i_seqno = i.seqno
  ,o_seqno = o.seqno
  ,i_price = i.price 
  ,o_price = o.price 
  ,flag = cast(case when i.price = o.price then '相同' else '不同' end as char(2))
from BB i, BB o
where i.bookno=o.bookno and i.lineno=o.lineno -- 行号,图书编号相同
and i.seqno < o.seqno

[中国风]

请问如果没有状态这个字段,根据seq no.可以进行判断吗?
------------
不行，要有才能知道记录是属于进或出

[中国风]

seqno--有可能是连续的进几次，然后再出几次。。这种情况是判断不了的...

[cxmcxm]

select * from 表 a,表 b
where a.status='进仓' and b.status='出仓' and a.bookno=b.bookno and a.lineno=b.lineno and a.price=b.price
      and a.seqno<b.seqno 
	and not exists(select * from 表 where status='出仓' and seqno>a.seqno and lineno=b.lineno and bookno=b.bookno
						and seqno<b.seqno)

假设一进一出

[disneyzhang]

发表于：2008-01-08 22:32:049楼 得分:0
seqno--有可能是连续的进几次，然后再出几次。。这种情况是判断不了的...


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
现在程序是这样设计的,同一种编号的图书就是 进仓,出仓,然后再进仓,出仓,可以循环操作!

可以进完仓后,不出仓操作!

[tim_spac]

create table BB (
   seqno char(5)
  ,[lineno] int
  ,price numeric(10,2)
  ,bookno char(5)
)

insert BB select '00001', 1 ,10  ,'A0001' 
insert BB select '00002', 2 ,11  ,'A0001' 
insert BB select '00003', 3 ,12  ,'A0001' 

insert BB select '00004' ,1 ,10,  'A0001' 
insert BB select '00005', 2 ,11.5  ,'A0001' 
insert BB select '00006' ,3 ,12.5  ,'A0001' 

insert BB select '00007' ,1 ,13, 'A0001' 
insert BB select '00008' ,2, 14, 'A0001' 
insert BB select '00009' ,3 ,15, 'A0001' 

insert BB select '00010' , 1, 13  ,'A0001' 
insert BB select '00011' ,2, 14.5, 'A0001' 
insert BB select '00012', 3,15  ,'A0001' 

insert BB select '00013', 1, 23 ,'A0001' 
insert BB select '00014' ,2 ,24  ,'A0001' 
insert BB select '00015' ,3, 25  ,'A0001' 

select
   i_seqno = i.seqno
  ,o_seqno = o.seqno
  ,i_price = i.price 
  ,o_price = o.price 
  ,flag = cast(case when i.price = o.price then '相同' else '不同' end as char(2))
into #t1
from BB i, BB o
where i.bookno=o.bookno and i.[lineno]=o.[lineno] -- 行号,图书编号相同
and i.seqno < o.seqno

select * from #t1 a
where not exists (select 1 from #t1 b
	where b.i_seqno=a.i_seqno and b.o_seqno<a.o_seqno)

drop table #t1,BB

这样的比较包括了:“上次出”vs“本次进”的情况