FOXPRO选择并加入结果

Question

我有一个很大的选择，我必须加入两个结果，但是我找不到使它起作用的方法。代码类似于：

    (select isnull(sum(fn.tqq),0) from fn where fn.icecream in (1,2,3,4,5,6,7,8,9) and (fn.fdata BETWEEN #2# AND #3#) and fn.usr1 not like '%'+'CHOC'+'%') as Total1,
(select isnull(sum(fn.tqq),0) from fn where fn.icecream in (1,2,3,4,5,6,7,8,9) and (fn.fdata BETWEEN #4# AND #5#) and pn.usr1 not like '%'+'Portes'+'%') as Total2,

是，我必须加入ICREAM 3和7的结果，以及冰淇淋8和9。

尽管标记为VFP，但该示例SQL听起来并不是VFP查询，也不是MS SQL Server查询。如果一秒钟我们认为这是有效的SQL，那么您可以在这样的代码中获得最初提出的内容：

Select t1.Total1, t2.Total2 From ;
    (Select Nvl(Sum(fn.tqq),0)  As Total1 ;
    from fn ;
    where fn.icecream In (1,2,3,4,5,6,7,8,9) And ;
    (fn.fdata Between #2# And #3#) And ;
    fn.usr1 Not Like '%'+'CHOC'+'%') t1, ;
    (Select Nvl(Sum(fn.tqq),0) As Total2 ;
    from fn ;
    where fn.icecream In (1,2,3,4,5,6,7,8,9) And ;
    (fn.fdata Between #4# And #5#) And ;
    fn.usr1 Not Like '%'+'Portes'+'%') t2

或（一个更VFP特定查询）：

Select ;
    Sum(Iif( InList(icecream,1,2,3,4,5,6,7,8,9) And ;
        Between(fdata, #2# , #3#) And ;
        !('CHOC'$usr1), tqq, 0)) as Total1, ;
    Sum(Iif( InList(icecream,1,2,3,4,5,6,7,8,9) And ;
        Between(fdata, #4# , #5#) And ;
        !('Portes'$usr1), tqq, 0)) as Total2 ;
    from fn

但是，阅读您的最后一句话，这更令人困惑。如果以上不是您所追求的，请为我们提供一些示例数据以及您要达到的结果，以便我们可以提供更好的数据。

好吧，我现在就迷路了，所以这是完整的代码：

    select 
X.VENDEDOR AS VENDEDOR,
X.VENDNM AS VENDNM,
X.NO AS NO,
X.nome AS nome,
isnull(sum(x.qtt_1),0) as Qtt_1,
isnull(sum(x.qtt_2),0) as Qtt_2,
#2# as Data1,
#3# as Data2,
#4# as Data3,
#5# as Data4,
(SELECT DESIGN FROM ST WHERE ST.forref ='*br1000*') AS ARTIGO,
(select isnull(sum(pn.qtt),0) from pn where pn.vendedor in (1,2,3,4,5,6,7,8,9) and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%') as Total1,
(select isnull(sum(pn.qtt),0) from pn where pn.vendedor in (1,2,3,4,5,6,7,8,9) and (pn.fdata BETWEEN #4# AND #5#) and pn.usr1 not like '%'+'Portes'+'%') as Total2,
((select isnull(sum(pn.qtt),0) from pn where pn.vendnm like x.vendnm and (pn.fdata BETWEEN #4# AND #5#) and pn.usr1 not like '%'+'Portes'+'%' )-(select isnull(sum(pn.qtt),0) from pn where pn.vendnm like x.vendnm and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%')) as Dif_Total_Vend,
isnull(
round((((select sum(pn.qtt) from pn where pn.vendedor in (1,2,3,4,5,6,7,8,9) and (pn.fdata BETWEEN #4# AND #5#) and pn.usr1 not like '%'+'Portes'+'%')
-
(select sum(pn.qtt) from pn where pn.vendedor in (1,2,3,4,5,6,7,8,9) and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%'))/
(select sum(pn.qtt) from pn where pn.vendedor in (1,2,3,4,5,6,7,8,9) and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%'))*100,2),0) as Perc_Dif,
isnull(
round((((select sum(pn.qtt) from pn where pn.vendnm like x.vendnm and (pn.fdata BETWEEN #4# AND #5#) and pn.usr1 not like '%'+'Portes'+'%')
-
(select sum(pn.qtt) from pn where pn.vendnm like x.vendnm and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%'))/
(select sum(pn.qtt) from pn where pn.vendnm like x.vendnm and (pn.fdata BETWEEN #2# AND #3#) and pn.usr1 not like '%'+'Portes'+'%'))*100,2),0) as Perc_Dif_Vend
from
(
select
pn.vendedor as Vendedor,
pn.vendnm as Vendnm,
pn.no as No,
(select nome from cl where cl.no=pn.no) as nome,
pn.qtt as Qtt_1,
0 as Qtt_2
from pn
where (pn.fdata BETWEEN #2# AND #3#) 
union all
select
pn.vendedor as Vendedor,
pn.vendnm as Vendnm,
pn.no as No,
(select nome from cl where cl.no=pn.no) as nome,
0 as Qtt_1,
pn.qtt as Qtt_2
from pn
where (pn.fdata BETWEEN #4# AND #5#) AND (pn.Vendedor '3' == pn.Vendedor '7')
)
x
group by x.no,x.nome, x.vendedor,x.vendnm
order by x.nome,x.vendedor

我想发生的事情是，供应商" 3"来代替数字'7'，而数字'8'来代替了" 9"。当我想要的是数字'3'和7'的结果时，我会得到重复的结果（数字'8'和'9'）。