SELECT A.CPRICE,B.OPRICE FROM HISTORY A,HISTORY B WHERE TO_CHAR(A.PNAME,'D') = '1' AND A.PRODUCTID='00000001' AND TO_CHAR(A.PNAME-7,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') AND TO_CHAR(B.PNAME,'D') = '2' AND A.PRODUCTID='00000001' AND TO_CHAR(B.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') SELECT MAX(PMAX), MIN(PMIN) FROM HISTORY WHERE PRODUCTID='00000001' AND TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') 这是取本周的,我用了两条.请高手将一条的写出.
TO_CHAR(A.PNAME,'D') 1是星期天 2是星期一 TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') 取本周SELECT MAX(a.PMAX), MIN(a.PMIN),CPRICE, OPRICE FROM HISTORY a, WHERE TO_CHAR(a.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')TO_CHAR(A.PNAME,'D') = '1' TO_CHAR(B.PNAME,'D') = '2'
panq先生的: SELECT MAX(a.PMAX), MIN(a.PMIN),CPRICE, OPRICE FROM HISTORY a, WHERE TO_CHAR(a.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')TO_CHAR(A.PNAME,'D') = '1' TO_CHAR(B.PNAME,'D') = '2' 有问题!行不通
seal_kh先生的: 可不可以加上group by 用来取出每一个星期的呢?
SELECT A.CPRICE, B.OPRICE, c.PMAX, F.PMIN FROM HISTORY A, HISTORY B , history c, HISTORY F WHERE A.CPRICE IN ( SELECT CPRICE FROM HISTORY D WHERE TO_CHAR(D.PNAME,'D') = '1' AND TO_CHAR(D.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') ) AND B.OPRICE IN ( SELECT OPRICE FROM HISTORY E WHERE TO_CHAR(E.PNAME,'D') = '2' AND TO_CHAR(E.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') ) AND C.PMAX IN (SELECT MAX(PMAX) FROM HISTORY WHERE TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')) AND F.PMAX IN (SELECT MIN(PMIN) FROM HISTORY WHERE TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW'))
AND TO_CHAR(B.PNAME,'D') = '2' AND A.PRODUCTID='00000001' AND TO_CHAR(B.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')
SELECT MAX(PMAX), MIN(PMIN) FROM HISTORY WHERE PRODUCTID='00000001' AND TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')
这是取本周的,我用了两条.请高手将一条的写出.
1是星期天
2是星期一
TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')
取本周SELECT MAX(a.PMAX), MIN(a.PMIN),CPRICE, OPRICE
FROM HISTORY a,
WHERE TO_CHAR(a.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')TO_CHAR(A.PNAME,'D') = '1'
TO_CHAR(B.PNAME,'D') = '2'
SELECT MAX(a.PMAX), MIN(a.PMIN),CPRICE, OPRICE
FROM HISTORY a,
WHERE TO_CHAR(a.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW')TO_CHAR(A.PNAME,'D') = '1'
TO_CHAR(B.PNAME,'D') = '2'
有问题!行不通
可不可以加上group by 用来取出每一个星期的呢?
A.CPRICE,
B.OPRICE,
c.PMAX,
F.PMIN
FROM
HISTORY A,
HISTORY B ,
history c,
HISTORY F
WHERE
A.CPRICE IN ( SELECT CPRICE FROM HISTORY D WHERE TO_CHAR(D.PNAME,'D') = '1' AND TO_CHAR(D.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') )
AND B.OPRICE IN ( SELECT OPRICE FROM HISTORY E WHERE TO_CHAR(E.PNAME,'D') = '2' AND TO_CHAR(E.PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW') )
AND C.PMAX IN (SELECT MAX(PMAX) FROM HISTORY WHERE TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW'))
AND F.PMAX IN (SELECT MIN(PMIN) FROM HISTORY WHERE TO_CHAR(PNAME,'YYYYMMW')=TO_CHAR(SYSDATE,'YYYYMMW'))
说不定可以解决的更好。
http://www.csdn.net/expert/topic/227/227030.shtm
先注明一下:谢谢大家的帮忙,明天来给分!ok?谢谢 seal_kh先生,谢谢panq先生。