一个多重查询的SQL语句

好吧直接上sql

SELECT e.*, d.num
FROM (
    SELECT am.bid, money, cost, uid, cooperation
        , buyout, base, `into`
    FROM (
        SELECT a.bid, money, uid, cooperation, buyout
            , base, `into`
        FROM (
            SELECT bid, SUM(m.money) AS money, b.uid, cooperation
                , buyout, base, `into`
            FROM money_out m
                LEFT JOIN book b ON m.bid = b.id
            WHERE m.ctime >= 1518105600
                AND m.ctime < 1518192000
                AND m.money > 0
            GROUP BY bid
            UNION
            SELECT bid, SUM(m.money) AS money, b.uid, cooperation
                , buyout, base, `into`
            FROM money_out_book m
                LEFT JOIN book b ON m.bid = b.id
            WHERE m.ctime >= 1518105600
                AND m.ctime < 1518192000
                AND m.money > 0
            GROUP BY bid
        ) a
        GROUP BY bid
    ) am
        LEFT JOIN (
            SELECT bid, SUM(cost) AS cost
            FROM wx_link m
            WHERE m.ctime >= 1518105600
                AND m.ctime < 1518192000
            GROUP BY bid
        ) c
        ON c.bid = am.bid
) e
    LEFT JOIN (
        SELECT bid, SUM(num) AS num
        FROM arc m
        WHERE m.ctime >= 1518105600
            AND m.ctime < 1518192000
            AND status = 1
        GROUP BY bid
    ) d
    ON e.bid = d.bid

现在来解释下上面这段sql 的作用:
首先他查询了 money_out(按章节消费记录), book(书), money_out_book(按本消费记录), wx_link(广告费用), arc(文章) 5张表
每本书每天的消费记录总和,广告费用总和,更新书每天的字数

分享到:

发表评论

昵称

沙发空缺中,还不快抢~