sql多重连接查询和多重计数
我想从我的产品销售表中统计(当前日销售和每月销售和季度销售),单独所有三个查询都工作正常,但我想分成一个。sql多重连接查询和多重计数
SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT(product_serial_no) AS `S_FTD` FROM register_product_claim INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin WHERE retailer_code = '9017' AND register_product_claim.dt = '2016-08-04' GROUP BY tm.asin ORDER BY register_product_claim.dt DESC LIMIT 0 , 30
这是对于当前日期
2)这是每月出卖计数
SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT(product_serial_no) AS `S_MTD` FROM register_product_claim INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin WHERE retailer_code = '9017' AND MONTH(register_product_claim.dt) = '08' GROUP BY tm.asin ORDER BY register_product_claim.dt DESC LIMIT 0 , 30
3)这是本季度
SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT(product_serial_no) AS `S_QTD`
FROM register_product_claim INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin WHERE retailer_code = '9017'
AND (MONTH(register_product_claim.dt) = '08'
OR MONTH(register_product_claim.dt) = '07'
OR MONTH(register_product_claim.dt) = '06'
) GROUP BY tm.asin ORDER BY register_product_claim.dt DESC
LIMIT 0 , 30
特定零售商
所需输出为
model_number | item_description | mrp | asin | S_FTD | S_MTD | S_QTD
尝试这样的事情
SELECT
stat1.model_number model_number,
stat1.item_description item_description,
stat1.mrp mrp,
stat1.asin asin,
stat1.S_FTD S_FTD,
stat2.S_MTD S_MTD,
stat3.S_QTD S_QTD
FROM
(SELECT
tm.internal_name AS model_number,
tm.item_description,
tm.mrp,
tm.asin,
COUNT(product_serial_no) AS `S_FTD`
FROM
register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE
retailer_code = '9017'
AND register_product_claim.dt = '2016-08-04'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC
LIMIT 0 , 30) stat1
JOIN
(SELECT
tm.internal_name AS model_number,
COUNT(product_serial_no) AS `S_MTD`
FROM
register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE
retailer_code = '9017'
AND MONTH(register_product_claim.dt) = '08'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC
LIMIT 0 , 30) stat2 ON stat1.model_number = stat2.model_numer
JOIN
(SELECT
tm.internal_name AS model_number,
COUNT(product_serial_no) AS `S_QTD`
FROM
register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE
retailer_code = '9017'
AND (MONTH(register_product_claim.dt) = '08'
OR MONTH(register_product_claim.dt) = '07'
OR MONTH(register_product_claim.dt) = '06')
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC
LIMIT 0 , 30) stat3 ON stat1.model_number = stat3.model_number
感谢您的回复,查询工作找到一个问题,为明智的月份和季度明智我们有更多的产品名单正在销售,但只有两个模型显示和明智的和季度明智的查询约5个产品 – user3438532
好,试试要更改stat1 + stat2 JOIN为左连接 – Vanojx1
什么都没有发生:(你能否请编辑答案 – user3438532
您可以发布抽样日期和预期结果。 –