首页 文章

3表JOIN与group by不起作用

提问于
浏览 次
0

我有3张 table ：

CREATE TABLE data (
    id VARCHAR(30) NOT NULL PRIMARY KEY,
    cat VARCHAR(30),
    name VARCHAR(30)
) ENGINE=InnoDB;


CREATE TABLE sales_data (
    id VARCHAR(30) NOT NULL,
    sold date,
    sale_s SMALLINT(5)
) ENGINE=InnoDB;


CREATE TABLE expenses (
    id VARCHAR(30) NOT NULL,
    date_exp date,
    exp DECIMAL(5,3)
) ENGINE=InnoDB;

并且需要汇总到销售列的每月级别（在表sales_data上，以创建Month列 . 这很简单：

SELECT 
    B.id as "Product ID", 
    B.name as "Product Name", 
    B.cat as "Product Category", 
    MONTHNAME(A.sold) as "Month", 
    sum(A.sale_s) as "Sales per Month"
FROM sales_data A
JOIN data B
ON A.prod_id=B.prod_id
WHERE 1
GROUP BY 1,4
ORDER BY 1, A.sale_date ASC;

但现在，我需要使用media_expenditures表中的Product_ID和Month列将月度销售数据与月度媒体支出数据合并 .

我试图在子查询中求和：

SELECT * 
FROM prod_data A 
LEFT JOIN 
(SELECT 
    id,
    sold,
    sum(sale_s)
FROM sales_data
group by id, MONTH(sold)
) AS B
ON A.id=B.id

RIGHT JOIN
(SELECT
    id,
    date_exp,
    expenses --don't need to SUM since it's only 1 row per month
FROM expenses
) AS C
ON A.id=C.id

ORDER BY A.id, B.sold

我不工作，并且几个小时以来一直在尝试，差不多一天在办公室里 . 我需要它来SUM第一个JOIN，然后能够从media_expenditure表中获取该月的行 .

mysql sql join group-by

2 回答

  • 0

    您需要在第二个连接条件中包含月份 .

    SELECT * 
FROM product_data A 
LEFT JOIN 
(SELECT 
    prod_id,
    sale_date,
    sum(sales)
FROM sales_data
group by prod_id, MONTH(sale_date)
) AS B
ON A.prod_id=B.prod_id

RIGHT JOIN
(SELECT
    prod_id,
    exp_date,
    media_expenditure --don't need to SUM since it's only 1 row per month
FROM media_expenditure
) AS C
ON A.prod_id=C.prod_id AND MONTH(B.sale_date) = MONTH(C.exp_date)

ORDER BY A.prod_id, B.sale_date
    回复于
  • 0

    竿，

    如果我理解正确，你希望在同一个选择中获得销售总额，那么我相信以下内容对您有所帮助 .

    SELECT 
    TB1.prod_id as "Product ID",
    TB1.prod_name as "Product Name", 
    TB1.prod_category as "Product Category",
    TB1.Sales,
    TB2.Expenditure
FROM
(
    SELECT 
        B.prod_id,
        B.prod_name,
        B.prod_category,
        MONTHNAME(A.sale_date) as "Month", 
        sum(A.sales) as "Sales"
    FROM 
        sales_data A
        JOIN product_data B
            ON A.prod_id=B.prod_id
    GROUP BY 
        1,4
) AS TB1 
    LEFT JOIN
    (
        SELECT
            A.prod_id, 
            MONTHNAME(A.sale_date) as "Month",
            SUM(B.media_expenditure) "Expenditure"
        FROM
            sales_data A
            JOIN media_expenditure B
                ON A.prod_id=B.prod_id
        GROUP BY 
            1,2

    ) AS TB2
    ON  TB1.prod_id = TB2.prod_id AND
        TB1.Month = TB2.Month
    回复于

相关问题