语句是这样的
select medicode,mediname,spec,opername,unitpric,sum(quantity) as shuliang,sum(itemamou) as jin
from dictoper,dictmedi,nclinreciinfo,nclininvoinfo
where nclinreciinfo.mediid=dictmedi.mediid and nclininvoinfo.doctid=dictoper.operid
and nclininvoinfo.autonumb=nclinreciinfo.autonumb and nclininvoinfo.ownamou not IN
(SELECT abs(ownamou) as ownamou
FROM nClinInvoInfo
WHERE status = 2)
and dictoper.opername=:l_opername
and nclininvoinfo.strcreadate between :d_begin and :d_end
group by medicode,mediname,spec,opername,unitpric
order by medicode asc但是执行之后的结果仍然有同样的 medicode 出现在查询结果中
这是为什么,要想避免这个问题,应该怎么改?
select medicode,mediname,spec,opername,unitpric,sum(quantity) as shuliang,sum(itemamou) as jin
from dictoper,dictmedi,nclinreciinfo,nclininvoinfo
where nclinreciinfo.mediid=dictmedi.mediid and nclininvoinfo.doctid=dictoper.operid
and nclininvoinfo.autonumb=nclinreciinfo.autonumb and nclininvoinfo.ownamou not IN
(SELECT abs(ownamou) as ownamou
FROM nClinInvoInfo
WHERE status = 2)
and dictoper.opername=:l_opername
and nclininvoinfo.strcreadate between :d_begin and :d_end
group by medicode,mediname,spec,opername,unitpric
order by medicode asc但是执行之后的结果仍然有同样的 medicode 出现在查询结果中
这是为什么,要想避免这个问题,应该怎么改?
select medicode,mediname,sum(quantity) as shuliang,sum(itemamou) as jin
from dictoper,dictmedi,nclinreciinfo,nclininvoinfo
where nclinreciinfo.mediid=dictmedi.mediid and nclininvoinfo.doctid=dictoper.operid
and nclininvoinfo.autonumb=nclinreciinfo.autonumb and nclininvoinfo.ownamou not IN
(SELECT abs(ownamou) as ownamou
FROM nClinInvoInfo
WHERE status = 2)
and dictoper.opername=:l_opername
and nclininvoinfo.strcreadate between :d_begin and :d_end
group by medicode,mediname
order by medicode asc
并且显示的结果里面按照medicode进行分组,计算出数量sum(quantity)和金额sum(itemamou),问题是按照上面的语句执行后,结果里面还是有重复的medicode
select medicode,max(mediname),max(spec),max(opername),max(unitpric),sum(quantity) as shuliang,sum(itemamou) as jin
from dictoper,dictmedi,nclinreciinfo,nclininvoinfo
where nclinreciinfo.mediid=dictmedi.mediid and nclininvoinfo.doctid=dictoper.operid
and nclininvoinfo.autonumb=nclinreciinfo.autonumb and nclininvoinfo.ownamou not IN
(SELECT abs(ownamou) as ownamou
FROM nClinInvoInfo
WHERE status = 2)
and dictoper.opername=:l_opername
and nclininvoinfo.strcreadate between :d_begin and :d_end
group by medicode
order by medicode asc