SQL语句 mysql改写成oracle

十指键魔 发布于 2017/09/21 14:34
阅读 95
收藏 0

SELECT
  DATE_FORMAT(market.market_day, '%x%v') AS timeWeek
  , market.market_day AS data_time
  , min(market.market_day) AS min_time
  , max(market.market_day) AS max_time
  , max(DATA.data_value) AS data_value
FROM
  sys_market_day_tbl market
  LEFT JOIN (
    SELECT
      *
    FROM
      web_basic_data_tbl
    WHERE
      delete_flg = '0'
      AND data_item_id = 'BASIC000000000000081'
      AND DATE_FORMAT(data_time, '%Y') >= '2015'
  ) data
    ON market.market_day = data.data_time
WHERE
  market.delete_flg = '0'
  AND DATE_FORMAT(market_day, '%Y') >= '2015'
GROUP BY
  DATE_FORMAT(market.market_day, '%x%v')
ORDER BY
  market.market_day


求怎么改 

 

我改完之后

SELECT
  to_char(
    to_date(market.market_day, 'yyyy-mm-dd')
    , 'yyyyiw'
  ) AS timeWeek
  , market.market_day AS data_time
  , min(market.market_day) AS min_time
  , max(market.market_day) AS max_time
  , max(DATA.data_value) AS data_value
FROM
  sys_market_day_tbl market
  LEFT JOIN (
    SELECT
      *
    FROM
      web_basic_data_tbl
    WHERE
      delete_flg = '0'
      AND data_item_id = 'BASIC000000000000081'
      AND to_char(to_date(data_time, 'yyyy-mm-dd'), 'yyyy') >= '2015'
  ) data
    ON market.market_day = data.data_time
WHERE
  market.delete_flg = '0'
  AND to_char(to_date(market_day, 'yyyy-mm-dd'), 'yyyy') >= '2015'
GROUP BY
  to_char(
    to_date(market.market_day, 'yyyy-mm-dd')
    , 'yyyyiw'
  )
ORDER BY
  market.market_day


因为 select 多查了这个 market.market_day AS data_time 导致group by 不行

求大牛教教怎么改写sql

加载中
0
vetter_wei
vetter_wei

Oracle中没有DATE_FORMAT函数,但是提供了to_date函数,第二个参数为日期格式

十指键魔
十指键魔
这个当然会啊 是改完之后 GROUP BY to_char(to_date(market.market_day, 'yyyy-mm-dd'), 'yyyyiw') ORDER BY market.market_day 报错 group by 这部分有问题
返回顶部
顶部