Oracle数据库返回星期

Oracle数据库返回星期

with a AS(
  SELECT * FROM INSTALL WHERE 地市='梧' AND 产品='宽带' AND 类型='装'
),
b AS(
SELECT a.*,
  round((外线时间 - 首次拨打电话时间)*24,9) AS 小时,
	TO_CHAR(外线时间, 'Day') AS "回单时间星期（英文前后有空格）",
	TRIM(TO_CHAR(外线时间, 'Day')) AS "外线回单日期是",
	CASE TRIM(TO_CHAR(外线时间, 'Day'))
	       WHEN 'Monday'    THEN '星期一'
	       WHEN 'Tuesday'   THEN '星期二'
	       WHEN 'Wednesday' THEN '星期三'
	       WHEN 'Thursday'  THEN '星期四'
	       WHEN 'Friday'    THEN '星期五'
	       WHEN 'Saturday'  THEN '星期六'
	       WHEN 'Sunday'    THEN '星期天'
	       END AS "上班日期是"
	FROM a WHERE rownum<=10
)
SELECT ROW_NUMBER() OVER (ORDER BY 编码 ASC) AS 序号,编码,TO_CHAR(外线时间, 'YYYY-MM-DD') AS 外线时间,上班日期是,COUNT(1) AS 周末上班总数 FROM b 
WHERE 上班日期是 IN('星期六','星期天')
GROUP BY 上班日期是,编码,外线回单时间