序列号 行号 价钱 状态 图书编号
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没有出仓记录,所以不需要比较
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.价钱
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='出仓'
*--定义要显示的列
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.价钱
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 行)
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
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
------------
不行,要有才能知道记录是属于进或出
seqno--有可能是连续的进几次,然后再出几次。。这种情况是判断不了的...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
现在程序是这样设计的,同一种编号的图书就是 进仓,出仓,然后再进仓,出仓,可以循环操作!可以进完仓后,不出仓操作!
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.seqnoselect * 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“本次进”的情况