这样的查询如何来实现???在线等
表结构如下,表名 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的数据,并且只取第一条
查询结果如下:
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, 100
select 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)
[解决办法]
‘并且只取第一条 ’应该是取最小的吧。
--创建测试环境
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
*/
