有两个表:分别为y_baseinfo、y_kucun表 分别表示基本信息表 与 库存表。下面的代码选出的是在基本信息表中对应的产品的总库存数量。但基本信息的ypshengchanriqi<>'2002-01-01' 。我想实现实现的目的为将所有的基本信息都选出来,如果ypshengchanriqi='2002-01-01'年的该产品库存数量数量等于0则显示ypshengchanriqi='2002-01-01'的这条产品信息,如大于0则不显示对应的该产品信息。
请问如何实现啊!说直白点吧,这是张药品基本信息表,与药品的库存表,药品基本信息的标示为ypshengchanriqi='2002-01-01',如果ypshengchanriqi='2002-01-01',则该信息必是最基本药品信息,所有该药品批号的药都有他派生而出。我的目的很简单就是如果有该药品派生出的药品生产日期不为'2002-01-01'的药,则该药的基本信息就不用显示了,用派生的药就行了,如果没有,则该基本信息必须要显示,请问如何实现啊。该如何改代码?
SELECT a.yphuaxuemingcheng,a.yppinming, a.ypshengchanchangjia, a.ypguige, a.ypzuixiaodanwei, a.ypjinjia,
a.yppihao, a.ypshoujia,a.yphuiyuanjia,a.yphuiyuanjifen,a.ypticheng, a.ypjinhuodanwei, a.ypjibie,
a.yppinyinma, a.ypshuxing, a.ypzhuzhi, a.yppizhunwenhao,a.ypkucunxiaxian, a.ypshengchanriqi,
a.ypyouxiaoqi, a.ypbaozhuangliang,a.ypbiaoshi, a.idno as id,a.cpid,ISNULL(b.ypshuliang, 0)
AS ypshuliang
FROM (SELECT yphuaxuemingcheng,yppinming, ypshengchanchangjia, ypguige, ypzuixiaodanwei, ypjinjia,
yppihao, ypshoujia,yphuiyuanjia, yphuiyuanjifen,ypticheng,ypjinhuodanwei, ypjibie,
yppinyinma, ypshuxing, ypzhuzhi,yppizhunwenhao, ypkucunxiaxian, ypshengchanriqi,
ypyouxiaoqi, ypbaozhuangliang,ypbiaoshi, idno,cpid
FROM Y_baseinfo where (yppinyinma LIKE :pinyinma OR yppinming LIKE :yppinming or yphuaxuemingcheng like :ypbianma) and ypflag='是' and ypshengchanriqi<>'2002-01-01'
GROUP BY yphuaxuemingcheng,yppinming, ypshengchanchangjia, ypguige, ypzuixiaodanwei, ypjinjia,
yppihao, ypshoujia, yphuiyuanjia,yphuiyuanjifen,ypticheng,ypjinhuodanwei, ypjibie,
yppinyinma, ypshuxing, ypzhuzhi,yppizhunwenhao, ypkucunxiaxian, ypshengchanriqi,
ypyouxiaoqi, ypbaozhuangliang,ypbiaoshi, idno,cpid) a LEFT OUTER JOIN
(SELECT ypid,SUM(ypshuliang) AS ypshuliang
FROM Y_kucun
GROUP BY ypid) b ON a.idno = b.ypid
GROUP BY a.yphuaxuemingcheng,a.yppinming, a.ypshengchanchangjia, a.ypguige, a.ypzuixiaodanwei, a.ypjinjia,
a.yppihao, a.ypshoujia,a.yphuiyuanjia, a.yphuiyuanjifen,a.ypticheng,a.ypjinhuodanwei, a.ypjibie, a.yphuaxuemingcheng,
a.yppinyinma, a.ypshuxing, a.ypzhuzhi,a.yppizhunwenhao, a.ypkucunxiaxian, a.ypshengchanriqi,
a.ypyouxiaoqi, a.ypbaozhuangliang,a.ypbiaoshi, a.idno,a.cpid, b.ypshuliang
请问如何实现啊!说直白点吧,这是张药品基本信息表,与药品的库存表,药品基本信息的标示为ypshengchanriqi='2002-01-01',如果ypshengchanriqi='2002-01-01',则该信息必是最基本药品信息,所有该药品批号的药都有他派生而出。我的目的很简单就是如果有该药品派生出的药品生产日期不为'2002-01-01'的药,则该药的基本信息就不用显示了,用派生的药就行了,如果没有,则该基本信息必须要显示,请问如何实现啊。该如何改代码?
SELECT a.yphuaxuemingcheng,a.yppinming, a.ypshengchanchangjia, a.ypguige, a.ypzuixiaodanwei, a.ypjinjia,
a.yppihao, a.ypshoujia,a.yphuiyuanjia,a.yphuiyuanjifen,a.ypticheng, a.ypjinhuodanwei, a.ypjibie,
a.yppinyinma, a.ypshuxing, a.ypzhuzhi, a.yppizhunwenhao,a.ypkucunxiaxian, a.ypshengchanriqi,
a.ypyouxiaoqi, a.ypbaozhuangliang,a.ypbiaoshi, a.idno as id,a.cpid,ISNULL(b.ypshuliang, 0)
AS ypshuliang
FROM (SELECT yphuaxuemingcheng,yppinming, ypshengchanchangjia, ypguige, ypzuixiaodanwei, ypjinjia,
yppihao, ypshoujia,yphuiyuanjia, yphuiyuanjifen,ypticheng,ypjinhuodanwei, ypjibie,
yppinyinma, ypshuxing, ypzhuzhi,yppizhunwenhao, ypkucunxiaxian, ypshengchanriqi,
ypyouxiaoqi, ypbaozhuangliang,ypbiaoshi, idno,cpid
FROM Y_baseinfo where (yppinyinma LIKE :pinyinma OR yppinming LIKE :yppinming or yphuaxuemingcheng like :ypbianma) and ypflag='是' and ypshengchanriqi<>'2002-01-01'
GROUP BY yphuaxuemingcheng,yppinming, ypshengchanchangjia, ypguige, ypzuixiaodanwei, ypjinjia,
yppihao, ypshoujia, yphuiyuanjia,yphuiyuanjifen,ypticheng,ypjinhuodanwei, ypjibie,
yppinyinma, ypshuxing, ypzhuzhi,yppizhunwenhao, ypkucunxiaxian, ypshengchanriqi,
ypyouxiaoqi, ypbaozhuangliang,ypbiaoshi, idno,cpid) a LEFT OUTER JOIN
(SELECT ypid,SUM(ypshuliang) AS ypshuliang
FROM Y_kucun
GROUP BY ypid) b ON a.idno = b.ypid
GROUP BY a.yphuaxuemingcheng,a.yppinming, a.ypshengchanchangjia, a.ypguige, a.ypzuixiaodanwei, a.ypjinjia,
a.yppihao, a.ypshoujia,a.yphuiyuanjia, a.yphuiyuanjifen,a.ypticheng,a.ypjinhuodanwei, a.ypjibie, a.yphuaxuemingcheng,
a.yppinyinma, a.ypshuxing, a.ypzhuzhi,a.yppizhunwenhao, a.ypkucunxiaxian, a.ypshengchanriqi,
a.ypyouxiaoqi, a.ypbaozhuangliang,a.ypbiaoshi, a.idno,a.cpid, b.ypshuliang
假设y_baseinfo和y_kucun之间用id相连接
用左外连接应该可以实现
select * from y_baseinfo a left outer join y_kucun b on a.id=b.id
where b.id is not null or (b.id is null and not exists (select * from y_kucun c where c.id=a.id))