五好的Book

本站主要是研究linux操作系统-主要指ubuntu,debian系,php及html应用技术-包括css,javascript
中遇到的问题所做的杂碎记录,以便给自己及同样遇到类似问题的人做问题解决的参考

一个多重查询的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张表
每本书每天的消费记录总和,广告费用总和,更新书每天的字数

本原创文章未经允许不得转载 | 当前页面:五好的Book » 一个多重查询的SQL语句

评论