sql查询员工入职多少天_sql 查询四舍五入_sql怎么四舍五入(30)

a.matname (+) = b.matname AND b.matname = c.matname (+) AND

a.spec (+) = b.spec AND b.spec = c.spec (+) AND

a.indate (+) = b.outdate AND b.outdate = c.backdate (+)

GROUP BY b.mattype, b.matname, b.spec, b.outdate

UNION

SELECT c.mattype, c.matname, c.spec, c.backdate AS indate,

SUM(nvl(a.amount, 0) - NVL(b.amount, 0) + NVL(c.amount, 0))

AS amount, SUM(nvl(a.matsum, 0) - NVL(b.matsum, 0)

+ NVL(c.matsum, 0)) AS matsum

FROM (SELECT a.mattype AS mattype, a.matname AS matname,

a.spec AS spec, b.indate AS indate, a.amount AS amount,

a.matsum AS matsum, b.matsource AS matsource

FROM materialin a, matin b

WHERE b.serial = a.serial) a, matuse b, matback c

WHERE a.mattype (+) = c.mattype AND b.mattype (+) = c.mattype AND

a.matname (+) = c.matname AND c.matname = b.matname (+) AND

c.spec = a.spec (+) AND c.spec = b.spec (+) AND

c.backdate = b.outdate (+) AND c.backdate = a.indate (+)

GROUP BY c.mattype, c.matname, c.spec, c.backdate) b

WHERE TO_CHAR(b.indate, 'yyyy-mm') <= TO_CHAR(a.indate, 'yyyy-mm') AND

a.mattype = b.mattype (+) AND a.matname = b.matname (+) AND

a.spec = b.spec (+)

GROUP BY a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm')) a,

---未完，待续

Top

---接上面

(SELECT a.mattype, a.matname, a.spec, TO_CHAR(a.indate, 'yyyy-mm') AS indate,

SUM(b.amount) AS amount, SUM(b.matsum) AS matsum

FROM (SELECT a.mattype, a.matname, a.spec, a.indate, SUM(nvl(a.amount, 0)

- NVL(b.amount, 0) + NVL(c.amount, 0)) AS amount, SUM(nvl(a.matsum,

0) - NVL(b.matsum, 0) + NVL(c.matsum, 0)) AS matsum

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/ruanjian/article-30385-30.html