查询中的日期数组

debugcn 发表于 Dev

塔哈马塞布

SET @qdate := '2014-02-17';
SELECT 
    com.trading_code,
    com.business_segment_id,
    dmkt.close_price,
    dmkt.cdate,
    dmkt.turnover,
    dmkt.volume,
    sh.total_no_share,
    IF(dmkt.close_price IS NULL,
        ((SELECT 
                close_price
            FROM
                daily_mkt_status
            WHERE
                trading_code = com.trading_code
                    AND cdate <= @qdate
            ORDER BY cdate DESC
            LIMIT 1) * sh.total_no_share),
        (dmkt.close_price * sh.total_no_share)) AS mcap
FROM
    company AS com
        LEFT JOIN
    daily_mkt_status AS dmkt ON (com.trading_code = dmkt.trading_code
        AND dmkt.cdate = @qdate )
        LEFT JOIN
    share_info AS sh ON (com.trading_code = sh.trading_code)

此代码仅在我使用变量时才有效

SET @qdate :- '2014-02-17'

但我希望它是这样的另一个表中的日期数组

SET @qdate IN (SELECT cdate FROM market_index);

qdate将循环遍历表market_index中的所有日期。我经历了所有数组变量问题，但由于我的代码的这一部分对我不起作用

cdate <= @qdate

但是我需要这部分，否则我的查询没有给我我想要的输出。

希望我很清楚，在此先感谢。

订婚

为什么不简单地加入market_index餐桌呢？

SELECT
    com.trading_code,
    com.business_segment_id,
    dmkt.close_price,
    dmkt.cdate,
    dmkt.turnover,
    dmkt.volume,
    sh.total_no_share,
    mi.cdate
    IF(dmkt.close_price IS NULL,
        ((SELECT
                close_price
            FROM
                daily_mkt_status
            WHERE
                trading_code = com.trading_code
                    AND cdate <= mi.cdate
            ORDER BY cdate DESC
            LIMIT 1) * sh.total_no_share),
        (dmkt.close_price * sh.total_no_share)) AS mcap
FROM
    company AS com
    JOIN market_index AS mi
    LEFT JOIN daily_mkt_status AS dmkt ON (
        com.trading_code = dmkt.trading_code
        AND dmkt.cdate = mi.cdate
    )
    LEFT JOIN share_info AS sh ON (com.trading_code = sh.trading_code)

您可能还想添加一个GROUP BY子句，以消除不必要的重复项

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。