shop-wiz logo
Follow Us on Facebook Follow Us on Google+ Follow Us on Twitter Follow Us on LinkedIn
제목 mysql 에서 누적 쿼리 구하기
등록일 2015.11.18 HIT 3376 작성자 폰돌
##  간단한 사용법
SELECT
    tbl1.a,
    tbl1.b,
    sum(tbl2.b)
FROM
    tbl1
INNER JOIN
    tbl2 ON tbl2.a <= tbl1.a
GROUP BY
    tbl1.a, tbl1.b
ORDER BY
    tbl1.a

##실제예
SELECT tb1.joindate, tb1.student_cnt, tb1.tutor_cnt, sum(tb2.student_cnt), sum(tb2.tutor_cnt)
    FROM
    (
        SELECT
            DATE_FORMAT(m.joindate, "%Y%m%d") as joindate, count(m.idx) as student_cnt, count(t.m_useridx) as tutor_cnt
        FROM
            member m
        LEFT JOIN
            tutor t
        ON
            m.idx = t.m_useridx AND t.m_tutorstate >= 7
        GROUP BY
            DATE_FORMAT(m.joindate, "%Y%m%d")
    ) tb1
    INNER JOIN
        (
        SELECT
            DATE_FORMAT(m.joindate, "%Y%m%d") as joindate, count(m.student_idx) as student_cnt, count(t.tutor_idx) as tutor_cnt
        FROM
            member m
        LEFT JOIN
            tutor t
        ON
            m.idx = t.useridx
        GROUP BY
            DATE_FORMAT(m.joindate, "%Y%m%d")
        ) as tb2
     ON
         tb2.joindate < tb1.joindate
    GROUP BY
        tb1.joindate
    ORDER BY
        tb1.joindate desc;
댓글보기
등록된 댓글이 없습니다.

Powered by shop-wiz since 2002, e-mail:wangta69@naver.com