那个看的太难受了,补充一下 我是这样写的 select buhuo=case when b.slqty>a.qty then '*' else '' end ,c.PLUno,d.maxqty,d.minqty,c.pluname,c.spec,d.gspec,mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc,b.slqty,slamt=b.slqty*c.slprc from (select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc else c.csprc end,slprc=b.slprc from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid where a.pluid=b.pluid and a.pluno like :PLUNO+'%') c join (SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE SHPID<>4 group by a.pluid) a on a.pluid=c.pluid left join (SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4 GROUP BY pluid)b on a.pluid=b.pluid left join baspluext d on a.pluid=d.pluid但是,返回了大量相同的结果, 其中 maxqty和minqty都是baspluext表中的字段 我只要1个就够了, 哎.... 难道有神人出现吗?
楼上真是站着说话不腰疼.告诉楼上,不说你啥了,不想帮人家就算了,没什么的,但是别发牢骚,我们都曾经菜过. 看的出来楼主是新人.这个我已经给你改写好了. 如果有任何疑问,你私信我,我帮你搞定select buhuo=case when b.slqty>a.qty then '*' else '' end , c.maxqty, c.minqty, c.PLUno, c.pluname, c.spec, c.gspec, mll=(c.slprc-c.csprc)/c.slprc*100, c.csprc, c.slprc , a.QTY, kcje=a.qty*c.csprc, b.slqty, slamt=b.slqty*c.slprc from (select pluid = a.pluid , spec = a.spec, pluno = a.pluno, pluname = a.pluname, csprc = case when c.csprc is null then b.avgcsprc else c.csprc end, slprc = b.slprc, gspec = d.gspec, maxqty = d.maxqty, minqty = d.minqty from basplumain a left outer join (select pluid,gspec,maxqty,minqty from baspluext) d on a.pluid = d.pluid, baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid where a.pluid=b.pluid AND [PLUNO] ) c left outer join (SELECT PLUID=a.pluid, QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE SHPID<>4 group by a.pluid) a on c.pluid = a.pluid left outer join (SELECT slQTY=SUM(QTY), pluid FROM FINEBKSHP WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4 GROUP BY pluid )b on c.pluid = b.pluid
直接加个 distinct 试试:select distinct buhuo=case when b.slqty>a.qty then '*' else '' end ,d.maxqty,d.minqty,c.PLUno,c.pluname,c.spec,d.gspec, mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc, b.slqty,slamt=b.slqty*c.slprc from (select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc else c.csprc end ,slprc=b.slprc from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid where a.pluid=b.pluid and a.pluno like :PLUNO+'%') c join (SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE SHPID<>4 group by a.pluid) a on a.pluid=c.pluid left join (SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4 GROUP BY pluid)b on a.pluid=b.pluid left join baspluext d on a.pluid=d.pluid
或者: select buhuo=case when b.slqty>a.qty then '*' else '' end ,d.maxqty,d.minqty,c.PLUno,c.pluname,c.spec,d.gspec, mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc, b.slqty,slamt=b.slqty*c.slprc from (select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc else c.csprc end ,slprc=b.slprc from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid where a.pluid=b.pluid and a.pluno like :PLUNO+'%') c join (SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE SHPID<>4 group by a.pluid) a on a.pluid=c.pluid left join (SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4 GROUP BY pluid)b on a.pluid=b.pluid left join (select distinct pluid,maxqty,minqty,gspec from baspluext) d on a.pluid=d.pluid
我是这样写的
select buhuo=case when b.slqty>a.qty then '*' else '' end ,c.PLUno,d.maxqty,d.minqty,c.pluname,c.spec,d.gspec,mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc,b.slqty,slamt=b.slqty*c.slprc
from
(select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc else c.csprc end,slprc=b.slprc
from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid
where a.pluid=b.pluid and a.pluno like :PLUNO+'%') c join
(SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY)
FROM FINSTOCKSHP a
WHERE SHPID<>4
group by a.pluid) a on a.pluid=c.pluid
left join
(SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP
WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4
GROUP BY pluid)b on a.pluid=b.pluid
left join baspluext d on a.pluid=d.pluid但是,返回了大量相同的结果, 其中 maxqty和minqty都是baspluext表中的字段
我只要1个就够了, 哎.... 难道有神人出现吗?
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
看的出来楼主是新人.这个我已经给你改写好了.
如果有任何疑问,你私信我,我帮你搞定select buhuo=case when b.slqty>a.qty then '*' else '' end ,
c.maxqty,
c.minqty,
c.PLUno,
c.pluname,
c.spec,
c.gspec,
mll=(c.slprc-c.csprc)/c.slprc*100,
c.csprc,
c.slprc ,
a.QTY,
kcje=a.qty*c.csprc,
b.slqty,
slamt=b.slqty*c.slprc
from (select pluid = a.pluid ,
spec = a.spec,
pluno = a.pluno,
pluname = a.pluname,
csprc = case when c.csprc is null then b.avgcsprc else c.csprc end,
slprc = b.slprc,
gspec = d.gspec,
maxqty = d.maxqty,
minqty = d.minqty
from basplumain a left outer join (select pluid,gspec,maxqty,minqty from baspluext) d on a.pluid = d.pluid,
baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid
where a.pluid=b.pluid
AND [PLUNO] ) c left outer join (SELECT PLUID=a.pluid,
QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY)
FROM FINSTOCKSHP a
WHERE SHPID<>4
group by a.pluid) a on c.pluid = a.pluid
left outer join (SELECT slQTY=SUM(QTY),
pluid
FROM FINEBKSHP
WHERE DOCTYP/1000000=21
and level3=0
and docdat>=:BDAT
and docdat<=:EDAT
and SHPID<>4
GROUP BY pluid )b on c.pluid = b.pluid
mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc,
b.slqty,slamt=b.slqty*c.slprc
from (select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc
else c.csprc end ,slprc=b.slprc
from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid
where a.pluid=b.pluid and a.pluno like :PLUNO+'%')
c join (SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a
WHERE SHPID<>4
group by a.pluid) a on a.pluid=c.pluid
left join (SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP
WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4
GROUP BY pluid)b on a.pluid=b.pluid
left join baspluext d on a.pluid=d.pluid
select buhuo=case when b.slqty>a.qty then '*' else '' end ,d.maxqty,d.minqty,c.PLUno,c.pluname,c.spec,d.gspec,
mll=(c.slprc-c.csprc)/c.slprc*100,c.csprc,c.slprc ,a.QTY,kcje=a.qty*c.csprc,
b.slqty,slamt=b.slqty*c.slprc
from (select pluid=a.pluid ,spec=a.spec,pluno=a.pluno,pluname=a.pluname,csprc=case when c.csprc is null then b.avgcsprc
else c.csprc end ,slprc=b.slprc
from basplumain a,baspluprc b left outer join getcurcsprc() c on b.pluid=c.pluid
where a.pluid=b.pluid and a.pluno like :PLUNO+'%')
c join (SELECT PLUID=a.pluid,QTY=sum(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a
WHERE SHPID<>4
group by a.pluid) a on a.pluid=c.pluid
left join (SELECT slQTY=SUM(QTY),pluid FROM FINEBKSHP
WHERE DOCTYP/1000000=21 and level3=0 and docdat>=:BDAT and docdat<=:EDAT and SHPID<>4
GROUP BY pluid)b on a.pluid=b.pluid
left join
(select distinct pluid,maxqty,minqty,gspec from baspluext) d on a.pluid=d.pluid