表结构如下,表名 tblA 结构如下:
noid fItemId fQty1 fQty2
10 1 5 0
10 2 0 10
10 3 0 5 20 1 100 0
20 3 0 100
noid 相同的相当于一张单据
现要查fItemId=1 的数据
(也就是说从tblA中找出所有fItemId=1的数据,再关联到tblA,
关联的条件是fItemid<>1 并且如果fItemId=1时fQty1<>0 ,则要取fQty2<>0 的数据,反之如果fItemId=1时fQty2<>0 则要取fQty1<>0的数据,并且只取第一条
也就是说
select a.noid,a.fItemId as fItemId1,b.fItemId as fItemid2
from tblA as a
left outer join (select top 1 * from tblA where fItemId<>1
and 如果a.fQty1<>0 则从tblA中取fQty2<>0的记录
,如果a.fQty2<>0 则从tblA中取fQty1<>0的记录) as b
where a.fItemId=1
查询结果如下:
noid fItemId1 fItemId2
10 1 2
20 1 3
noid fItemId fQty1 fQty2
10 1 5 0
10 2 0 10
10 3 0 5 20 1 100 0
20 3 0 100
noid 相同的相当于一张单据
现要查fItemId=1 的数据
(也就是说从tblA中找出所有fItemId=1的数据,再关联到tblA,
关联的条件是fItemid<>1 并且如果fItemId=1时fQty1<>0 ,则要取fQty2<>0 的数据,反之如果fItemId=1时fQty2<>0 则要取fQty1<>0的数据,并且只取第一条
也就是说
select a.noid,a.fItemId as fItemId1,b.fItemId as fItemid2
from tblA as a
left outer join (select top 1 * from tblA where fItemId<>1
and 如果a.fQty1<>0 则从tblA中取fQty2<>0的记录
,如果a.fQty2<>0 则从tblA中取fQty1<>0的记录) as b
where a.fItemId=1
查询结果如下:
noid fItemId1 fItemId2
10 1 2
20 1 3
create table #tbla(noid int,fItemId int,fQty1 int,fQty2 int)--插入测试数据
insert #tbla(noid,fItemId,fQty1,fQty2)
select '10','1','5','0' union all
select '10','2','0','10' union all
select '10','3','0','5' union all
select '20','1','100','0' union all
select '20','3','0','100'--求解过程
select a.noid,a.fItemId as fItemId1,min(b.fItemId) as fItemid2
from #tbla a
left join
(
select *
from #tbla
where fItemId <> 1
) b on b.noid=a.noid
where a.fItemId=1
and ((a.fQty1<>0 and b.fQty2<>0) or (a.fQty2<>0 and b.fQty1<>0))
group by a.noid,a.fItemId
--删除测试环境
drop table #tbla/*--测试结果
noid fItemId1 fItemid2
----------- ----------- -----------
10 1 2
20 1 3
*/
--不知对不对create table tblA(noid int, fItemId int, fQty1 int, fQty2 int)
insert tblA select 10, 1, 5, 0
union all select 10, 2, 0, 10
union all select 10, 3, 0, 5
union all select 20, 1, 100, 0
union all select 20, 3, 0, 100select a.noid, a.fItemId,
fItemId2=(
select top 1 (case when a.fQty1<>0 and fQty2<>0 then fItemId when a.fQty2<>0 and fQty1<>0 then fItemId end)
from tblA where noid=a.noid and fItemId<>1
)
from tblA as a
where a.fItemId=1--result
noid fItemId fItemId2
----------- ----------- -----------
10 1 2
20 1 3(2 row(s) affected)