解决方案 »
- 在一台十几万的服务器上和一台64位PC机上,服务器的速度还要慢,请高手解答
- exp导出时出现ora-00904和ora-01003错误,不能成功导出
- 求一完整的物化视图过程及语句
- windows server2003 能安装 oracle817吗
- escape 问题 帮忙解释一下...
- pl/sql怎么返回数据集?
- hint问题,急!!!
- 怎样用ODBC访问ORACLE 9i ~~急用~~ 在线等~~
- oracle用exp做备份能把视图也备份出来么?应该怎么做啊?
- 请教关于weblogic10.3运行两天左右就必须重启的问题
- 求delphi7或者xe5链接oracle的方法
- 菜鸟求指导SQL怎么写
其实方法有很多,例如:select b.* from (
select to_char(sysdate-1,'yyyy-IW-Day') a from dual
union select to_char(sysdate-2,'yyyy-IW-Day') from dual
union select to_char(sysdate-3,'yyyy-IW-Day') from dual
union select to_char(sysdate-4,'yyyy-IW-Day') from dual
union select to_char(sysdate-5,'yyyy-IW-Day') from dual
union select to_char(sysdate-6,'yyyy-IW-Day') from dual
union select to_char(sysdate,'yyyy-IW-Day') from dual ) b
order by decode(substr(a,9),'星期一',1,'星期二',2,'星期三',3,'星期四',4,'星期五',5,'星期六',6,'星期日',7)
SELECT DISTINCT
(
TO_CHAR (CREATETIME, 'yyyy-IW-Day')
)
case (CREATETIME, 'yyyy-IW-Day')
when 'yyyy-IW-Day'= 'yyyy-IW-星期一' THEN 1
when 'yyyy-IW-Day'= 'yyyy-IW-星期二' THEN 2
when 'yyyy-IW-Day'= 'yyyy-IW-星期三' THEN 3
when 'yyyy-IW-Day'= 'yyyy-IW-星期四' THEN 4
when 'yyyy-IW-Day'= 'yyyy-IW-星期五' THEN 5
when 'yyyy-IW-Day'= 'yyyy-IW-星期六' THEN 6
when 'yyyy-IW-Day'= 'yyyy-IW-星期日' THEN 7
END week
FROM
DAYSTATEMENTCOLD
WHERE
TO_CHAR (CREATETIME, 'yyyy-IW') = (
SELECT DISTINCT
(
TO_CHAR (CREATETIME, 'yyyy-IW')
)
FROM
DAYSTATEMENTCOLD
WHERE
TO_CHAR (CREATETIME, 'yyyy-IW-Day') = TO_CHAR ('2014-32-星期一')
)
ORDER BY week ASC
思路是:根据 ('2014-32-星期一')其中32是指周,然后得到这一周所有的星期,再对TO_CHAR (CREATETIME, 'yyyy-IW-Day')进行排序。
when 'yyyy-IW-Day'= 'yyyy-IW-星期一' THEN 1
when 'yyyy-IW-Day'= 'yyyy-IW-星期二' THEN 2
when 'yyyy-IW-Day'= 'yyyy-IW-星期三' THEN 3
when 'yyyy-IW-Day'= 'yyyy-IW-星期四' THEN 4
when 'yyyy-IW-Day'= 'yyyy-IW-星期五' THEN 5
when 'yyyy-IW-Day'= 'yyyy-IW-星期六' THEN 6
when 'yyyy-IW-Day'= 'yyyy-IW-星期日' THEN 7
END week
when的条件是俩字符串进行比较,这样case取出来的结果肯定是null
为啥不直接按照日期进行排序呢?效果应该是一样的呀
sql语句是
SELECT DISTINCT
(
TO_CHAR (CREATETIME, 'yyyy-IW-Day')
)AS SPARE
FROM
DAYSTATEMENTCOLD
WHERE
TO_CHAR (CREATETIME, 'yyyy-IW') =
( SELECT DISTINCT
(
TO_CHAR (CREATETIME, 'yyyy-IW')
)
FROM
DAYSTATEMENTCOLD
WHERE
TO_CHAR (CREATETIME, 'yyyy-IW-Day') = TO_CHAR ('2014-32-星期一'))
ORDER BY TO_CHAR (CREATETIME, 'yyyy-IW-Day') ASC
结果是
把你的 ORDER BY TO_CHAR (CREATETIME, 'yyyy-IW-Day') ASC
改为ORDER BY CREATETIME ASC
SELECT DISTINCT TO_CHAR (CREATETIME, 'yyyy-IW-Day')AS SPARE
FROM DAYSTATEMENTCOLD
WHERE TO_CHAR (CREATETIME, 'yyyy-IW') =
(SELECT DISTINCT TO_CHAR (CREATETIME, 'yyyy-IW')
FROM DAYSTATEMENTCOLD
WHERE TO_CHAR (CREATETIME, 'yyyy-IW-Day') = TO_CHAR ('2014-32-星期一'))
ORDER BY CREATETIME ASC
这样会报 : 不是 SELECTed 表达式这样的错误。还是感谢你耐心的回答,真的有点不好意思老
个别工具上确实存在类似问题,比如说某些报表工具
如果是报表的话,可以在select的时候多select一列,显示的时候不显示就是了
另外你的条件写得有点罗嗦
SELECT DISTINCT trunc(CREATETIME),TO_CHAR (CREATETIME, 'yyyy-IW-Day')AS SPARE
FROM DAYSTATEMENTCOLD
WHERE TO_CHAR (CREATETIME, 'yyyy-IW') = '2014-32'
ORDER BY trunc(CREATETIME) ASC