在子查询中使用分组方式 [英] Use group by inside subquery
本文介绍了在子查询中使用分组方式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用mysql在house_details表中的subincome字段中显示progressive_total和cumulative_sum组.我在此 链接
I am trying to display progressive_total and cumulative_sum group by the subincome field in house_details table using mysql. I built my schema in this link
有效查询:
SELECT *,
COALESCE(
(SELECT SUM(x.rupees)
FROM house_details x
WHERE MONTH(x.date) < t1.month), '-') AS progressive_total,
(SELECT SUM(x.rupees)
FROM house_details x
WHERE MONTH(x.date) <= t1.month) AS cumulative_sum
FROM
(SELECT MONTHNAME(t.date) AS `monthname`,
MONTH(t.date) `month`,
YEAR(t.date) AS YEAR,
t.income,
t.subincome,
t.ssubincome,
SUM(rupees) AS amount,
GROUP_CONCAT(receipt_id) AS receipt_ids
FROM house_details t
WHERE YEAR(t.date) = YEAR(CURRENT_DATE())
GROUP BY month(t.date),
t.subincome
ORDER BY t.date) t1
但这在字段中给出了不相关的cumulative_sum.
but this gives irrelevant cumulative_sum in the field.
我试图在子查询中使用group by:
I tried to use group by inside the subquery like this:
查询:
SELECT *,
COALESCE(
(SELECT SUM(x.rupees)
FROM house_details x
WHERE MONTH(x.date) < t1.month
GROUP BY x.subincome), '-') AS progressive_total,
(SELECT SUM(x.rupees)
FROM house_details x
WHERE MONTH(x.date) <= t1.month
GROUP BY x.subincome) AS cumulative_sum
FROM
(SELECT MONTHNAME(t.date) AS `monthname`,
MONTH(t.date) `month`,
YEAR(t.date) AS YEAR,
t.income,
t.subincome,
t.ssubincome,
SUM(rupees) AS amount,
GROUP_CONCAT(receipt_id) AS receipt_ids
FROM house_details t
WHERE YEAR(t.date) = YEAR(CURRENT_DATE())
GROUP BY month(t.date),
t.subincome
ORDER BY t.date) t1;
但显示错误子查询返回多个行.
推荐答案
您可以使用以下查询获取期望的结果集
You can use below query for your expected result set
SELECT *,
COALESCE(
(SELECT SUM(pt.rupees) FROM (
SELECT MONTH(`date`) `month`,
MAX(id) id,
SUM(rupees) rupees
FROM house_details
GROUP BY `month`,subincome
) pt
WHERE CASE WHEN pt.month = t1.month THEN pt.id < t1.id ELSE pt.month < t1.month END
), 0) AS progressive_total,
(SELECT SUM(rupees) FROM(
SELECT MONTH(`date`) `month`,
MAX(id) id,
SUM(rupees) rupees
FROM house_details
GROUP BY `month`,subincome
) cs
WHERE CASE WHEN cs.month = t1.month THEN cs.id <= t1.id ELSE cs.month <= t1.month END
) AS cumulative_sum
FROM (
SELECT MONTHNAME(t.date) AS `monthname`,
MAX(id) id,
MONTH(t.date) `month`,
YEAR(t.date) AS `year`,
GROUP_CONCAT(t.income) income,
t.subincome,
GROUP_CONCAT(t.ssubincome) ssubincome,
SUM(rupees) AS amount,
GROUP_CONCAT(receipt_id) AS receipt_ids
FROM house_details t
WHERE YEAR(t.date) = YEAR(CURRENT_DATE())
GROUP BY `monthname`,`month`, t.subincome
ORDER BY `month`
) t1
这篇关于在子查询中使用分组方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文