日期:2014-05-18  浏览次数:20545 次

如何筛选出未采购或者未出库的product?
有一张stock库存表,里面type字段为1的为采购,2为出库,其余主要字段有stockid,productid和updatetime
另有一张product表,里面有所有的productid,productname

应该如何查询在5月份内未采购或者未出库的product呢?
结果如下图


------解决方案--------------------
SQL code
SELECT A.productid AS 药品ID,A.productname AS 药品名,
  CASE WHEN
    NOT EXISTS(SELECT 1 FROM stock WHERE productid=A.productid AND type='1' AND month(updatetime)=5)
    THEN '未采购'
  END AS remark,
  CASE WHEN
    NOT EXISTS(SELECT 1 FROM stock WHERE productid=A.productid AND type='2' AND month(updatetime)=5)
    THEN '未出库'
  END AS remark2
FROM product a