SELECT (substr(DYSFBH,2,2)) DYSFBH ,JZBH,YSXM, SUM(JE1) YPFY, SUM(JE2) ZFY
FROM YBXX.YBSFXX WHERE ZXRQ BETWEEN TO_DATE('2005-11-03','YYYY-MM-DD')
AND TO_DATE('2008-11-26','YYYY-MM-DD') AND JZLX IN ('1', '5')
GROUP BY substr(DYSFBH,2,2),JZBH,YSXM ORDER BY DYSFBH,JZBH,YSXM
这里在字段 substr(DYSFBH,2,2),和JZBH分组的情况下,比如同一条JZBH有4条记录对应不同的YSXM或为空,我现在需要取ysxm不为空的一条,能实现吗?如果不能实现那么只能取出全部明细,然后再代码中滚,这样的话对服务器代价太大
FROM YBXX.YBSFXX WHERE ZXRQ BETWEEN TO_DATE('2005-11-03','YYYY-MM-DD')
AND TO_DATE('2008-11-26','YYYY-MM-DD') AND JZLX IN ('1', '5')
GROUP BY substr(DYSFBH,2,2),JZBH,YSXM ORDER BY DYSFBH,JZBH,YSXM
这里在字段 substr(DYSFBH,2,2),和JZBH分组的情况下,比如同一条JZBH有4条记录对应不同的YSXM或为空,我现在需要取ysxm不为空的一条,能实现吗?如果不能实现那么只能取出全部明细,然后再代码中滚,这样的话对服务器代价太大
SELECT (substr(DYSFBH,2,2)) DYSFBH ,JZBH,YSXM, SUM(JE1) YPFY, SUM(JE2) ZFY
FROM YBXX.YBSFXX
WHERE ZXRQ BETWEEN TO_DATE('2005-11-03','YYYY-MM-DD')
AND TO_DATE('2008-11-26','YYYY-MM-DD') AND JZLX IN ('1', '5')
AND YSXM IS NOT NULL
GROUP BY substr(DYSFBH,2,2),JZBH,YSXM
ORDER BY DYSFBH,JZBH,YSXM
SELECT (substr(DYSFBH,2,2)) DYSFBH ,JZBH,YSXM, SUM(JE1) YPFY, SUM(JE2) ZFY ,count(YSXM) xxx
FROM YBXX.YBSFXX
WHERE ZXRQ BETWEEN TO_DATE('2005-11-03','YYYY-MM-DD')
AND TO_DATE('2008-11-26','YYYY-MM-DD') AND JZLX IN ('1', '5')
GROUP BY substr(DYSFBH,2,2),JZBH,YSXM
having count(YSXM)>0
比如你有如下四条明细。
BH XM qty
01 01 1
01 02 1
01 null 1
01 null 1
-------------
你需要的结果是什么?
BH XM qty
01 01 4
----------------
还是
BH XM qty
01 01 1
01 02 3
BH XM qty
01 01 4
----------------
或
BH XM qty
01 02 4
----------------
都可以
FROM YBXX.YBSFXX WHERE ZXRQ BETWEEN TO_DATE('2005-11-03','YYYY-MM-DD')
AND TO_DATE('2008-11-26','YYYY-MM-DD') AND JZLX IN ('1', '5')
GROUP BY substr(DYSFBH,2,2),JZBH,YSXM ORDER BY DYSFBH,YSXM