1select a.empid,a.ename,sum(b.qty),sum(sales) from employee a,sales b where a.empid=b.empid and to_char(date,'yyyymmdd')>='20070101' and to_char(date,'yyyymmdd')<='20071231' group by a.empid,a.ename having sum(sales)>10000
如果你的DATE只到年月 and to_char(date,'yyyymm')>='200701' and to_char(date,'yyyymm')<='200712' 这里就改成这样
C'est la Vie select a.empid,a.ename,sum(b.qty),sum(sales) from employee a,sales b where a.empid=b.empid and to_char(date,'yyyymmdd')>='20070101' and to_char(date,'yyyymmdd')<='20071231' group by a.empid,a.ename having sum(sales)>10000 这样写会不会因为 结果里有a.ename 这个 既不是聚合对象 也不是分组对象 而报错呢!
2select * from( select * from ( select b.date,a.empid,a.ename,sum(b.qty) qty,sum(sales) amt,FIRST_VALUE(SUM(sales)) OVER (ORDER BY b.date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) -sum(sales) upfrom employee a,sales b where a.empid=b.empid and to_char(date,'yyyymm')> ='200701' and to_char(date,'yyyymm') <='200712' group by a.empid,a.ename,b.date order by 6 ) j where to_char(j.date,'yyyymm')='200702' order by 6 desc) where rownum=1
下面写的是伪SQL, 有些地方请作小小改变:问题一: SELECT A.EMPID, B.ENAME, TOTAL_QTY,TOTAL_SALES FROM (SELECT EMPID, SUM(QTY) OVER (PARTITION BY EMPID ORDER BY ID) TOTAL_QTY, SUM(SALES) OVER (PARTITION BY EMPID ORDER BY ID) TOTAL_SALES, ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY ID) SEQ FROM SALES WHERE DATE = '2007') A,/*针对年月请做相应的改变以满足条件*/ EMPLOYEE B WHERE A.EMPID = B.EMPID AND TOTAL_SALES > 10000 AND A.SEQ = 1 问题二: SELECT * FROM ( SELECT EMPID, SUM_SALES, DENSE_RANK() OVER (PARTITION BY SUM_SALES ORDER BY SUM_SALES) SEQ FROM ( SELECT A.EMPID, C.ENAME, SUM(A.SALES - B.SALES) SUM_SALES FROM SALES A, SALES B, EMPLOYEE C WHERE A.EMPID = B.EMPID AND A.EMPID = C.EMPID AND A.DATE = '2007年2月' AND A.DATE = '2007年1月'/*针对年月请做相应的改变以满足条件*/ GROUP BY A.EMPID, C.ENAME ) ) WHERE SEQ = 1
from employee a,sales b
where a.empid=b.empid
and to_char(date,'yyyymmdd')>='20070101'
and to_char(date,'yyyymmdd')<='20071231'
group by a.empid,a.ename
having sum(sales)>10000
and to_char(date,'yyyymm')>='200701'
and to_char(date,'yyyymm')<='200712'
这里就改成这样
select a.empid,a.ename,sum(b.qty),sum(sales)
from employee a,sales b
where a.empid=b.empid
and to_char(date,'yyyymmdd')>='20070101'
and to_char(date,'yyyymmdd')<='20071231'
group by a.empid,a.ename
having sum(sales)>10000
这样写会不会因为 结果里有a.ename 这个 既不是聚合对象 也不是分组对象 而报错呢!
from(
select *
from
(
select b.date,a.empid,a.ename,sum(b.qty) qty,sum(sales) amt,FIRST_VALUE(SUM(sales)) OVER (ORDER BY b.date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) -sum(sales) upfrom employee a,sales b
where a.empid=b.empid
and to_char(date,'yyyymm')> ='200701'
and to_char(date,'yyyymm') <='200712'
group by a.empid,a.ename,b.date
order by 6
) j
where to_char(j.date,'yyyymm')='200702'
order by 6 desc)
where rownum=1
这个不会啊,分组里有了啊
下面写的是伪SQL, 有些地方请作小小改变:问题一:
SELECT A.EMPID, B.ENAME, TOTAL_QTY,TOTAL_SALES
FROM (SELECT EMPID,
SUM(QTY) OVER (PARTITION BY EMPID ORDER BY ID) TOTAL_QTY,
SUM(SALES) OVER (PARTITION BY EMPID ORDER BY ID) TOTAL_SALES,
ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY ID) SEQ
FROM SALES
WHERE DATE = '2007') A,/*针对年月请做相应的改变以满足条件*/
EMPLOYEE B
WHERE A.EMPID = B.EMPID AND TOTAL_SALES > 10000 AND A.SEQ = 1
问题二:
SELECT *
FROM (
SELECT EMPID, SUM_SALES,
DENSE_RANK() OVER (PARTITION BY SUM_SALES ORDER BY SUM_SALES) SEQ
FROM (
SELECT A.EMPID, C.ENAME, SUM(A.SALES - B.SALES) SUM_SALES
FROM SALES A, SALES B, EMPLOYEE C
WHERE A.EMPID = B.EMPID AND A.EMPID = C.EMPID AND
A.DATE = '2007年2月' AND A.DATE = '2007年1月'/*针对年月请做相应的改变以满足条件*/
GROUP BY A.EMPID, C.ENAME
)
)
WHERE SEQ = 1