hongze_yb/models/tables/daily_base_column/query.go

package daily_base_column

import (
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/models/response"
)

// GetDataByBaseColumnTickers17 获取数据指标
func GetDataByBaseColumnTickers17(tickers []string) (list []*response.TickerDataItem, err error) {
	sql :=`SELECT
 ifnull(max(doo.date), max(dd.date)) AS date,
 dbc.base_column_ticker AS ticker,
 dbc.base_column_name,
 ifnull(
  (
   SELECT

   VALUE

   FROM
    daily_data
   WHERE
    ticker = dbc.base_column_ticker
   AND date = ifnull(max(doo.date), max(dd.date))
  ),
  (
   SELECT

   VALUE

   FROM
    daily_original
   WHERE
    ticker = dbc.base_column_ticker
   AND date = ifnull(max(doo.date), max(dd.date))
  )
 ) AS ticker_value,
 ifnull(
  (
   SELECT

   VALUE

   FROM
    daily_data
   WHERE
    ticker = dbc.base_column_ticker
   AND date < ifnull(max(doo.date), max(dd.date))
   ORDER BY
    date DESC
   LIMIT 1
  ),
  (
   SELECT

   VALUE

   FROM
    daily_original
   WHERE
    ticker = dbc.base_column_ticker
   AND date < ifnull(max(doo.date), max(dd.date))
   ORDER BY
    date DESC
   LIMIT 1
  )
 ) AS last_value
FROM
 daily_base_column AS dbc
LEFT JOIN daily_data AS dd ON dbc.base_column_ticker = dd.ticker
LEFT JOIN daily_original AS doo ON dbc.base_column_ticker = doo.ticker
WHERE
 dbc.base_column_ticker IN ?
GROUP BY
 dbc.base_column_ticker`
	err = global.DEFAULT_MYSQL.Raw(sql, tickers).Scan(&list).Error
	return
}

// GetDataByBaseColumnTickers 获取数据指标
func GetDataByBaseColumnTickers(tickers []string) (list []*response.TickerDataItem, err error) {
	sql :=`select ifnull(max(doo.date),max(dd.date)) as date,dbc.base_column_ticker as ticker,dbc.base_column_name ,
            ifnull((select value from daily_data where ticker = dbc.base_column_ticker and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1),(select value from daily_original where ticker = dbc.base_column_ticker and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1)) as ticker_value,
            ifnull((select value from daily_data where ticker = concat(dbc.base_column_ticker,'_dd') and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1),(select value from daily_original where ticker = concat(dbc.base_column_ticker,'_dd') and date <= ifnull(max(doo.date),max(dd.date))and value is not null order by date desc limit 1)) as dd_value,
            ifnull((select value from daily_data where ticker = concat(dbc.base_column_ticker,'_ww') and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1),(select value from daily_original where ticker = concat(dbc.base_column_ticker,'_ww') and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1)) as ww_value,
            ifnull((select value from daily_data where ticker = concat(dbc.base_column_ticker,'_mm') and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1),(select value from daily_original where ticker = concat(dbc.base_column_ticker,'_mm') and date <= ifnull(max(doo.date),max(dd.date)) and value is not null order by date desc limit 1)) as mm_value
       from
			 daily_base_column AS dbc
LEFT JOIN daily_data AS dd ON dbc.base_column_ticker = dd.ticker
LEFT JOIN daily_original AS doo ON dbc.base_column_ticker = doo.ticker
WHERE
 dbc.base_column_ticker IN ?
GROUP BY
 dbc.base_column_ticker`
	err = global.DEFAULT_MYSQL.Raw(sql, tickers).Scan(&list).Error
	return
}