gpt4 book ai didi

mysql - 按年份添加两列金额

转载 作者:行者123 更新时间:2023-11-29 08:38:55 24 4
gpt4 key购买 nike

下面是我的查询,它将我的客户分组在一起,并对他们在过去两年中花费的总金额进行了总结。奇迹般有效。不过,我需要更多的知识来了解我的 sql 知识。

有没有办法再选择三个列并用他们每年(2010、2011、2012)花费的钱来填充它?

SELECT  SUM(price) AS money_spent_total, co.customer_id, cu.first_name, cu.last_name, cu.email_primary, cu.phone_primary, co.date_paid
FROM customer_order AS co
INNER JOIN customer AS cu ON (cu.customer_id = co.customer_id)
WHERE cu.customer_id != 32518 AND co.date_paid > "2010-1-1" AND co.date_paid < "2013-1-1"
GROUP BY co.customer_id

最佳答案

您需要GROUP BY YEAR(date_paid)除了 customer_id 。您也不应该选择 GROUP BY 中不存在的任何列。 。此外,您还可以使用BETWEEN运算符而不是 ><您的日期范围。

SELECT Year(co.date_paid), 
co.customer_id,
Sum(price) AS money_spent_total
FROM customer_order AS co
INNER JOIN customer AS cu
ON ( cu.customer_id = co.customer_id )
WHERE cu.customer_id != 32518
AND co.date_paid BETWEEN '2010-01-01' AND '2013-01-01'
GROUP BY Year(co.date_paid),
co.customer_id

获取原始 SELECT 中的列,你会做类似的事情(未测试):

SELECT a.date_year_paid, 
a.customer_id,
a.money_spent_total,
cu.first_name,
cu.last_name,
cu.email_primary,
cu.phone_primary
FROM (SELECT Year(co.date_paid) AS date_year_paid,
co.customer_id,
Sum(price) AS money_spent_total
FROM customer_order AS co
INNER JOIN customer AS cu
ON ( cu.customer_id = co.customer_id )
WHERE cu.customer_id != 32518
AND co.date_paid BETWEEN '2010-01-01' AND '2013-01-01'
GROUP BY Year(co.date_paid),
co.customer_id) a
LEFT JOIN customer cu
ON cu.customer_id = a.customer_id

如果您正在执行INNER JOIN仅用于客户信息,然后可以删除。

  SELECT a.customer_id, 
a.date_year_paid,
a.money_spent_total,
cu.first_name,
cu.last_name,
cu.email_primary,
cu.phone_primary
FROM (SELECT customer_id, YEAR(date_paid) AS date_year_paid, SUM(price) AS money_spent_total
FROM customer_order
GROUP BY customer_id, YEAR(date_paid)) a
LEFT JOIN customer cu
ON cu.customer_id = a.customer_id

最后,如果您想将年份分组为列:

SELECT a.customer_id, 
a.y2010 AS '2010_money_paid',
a.y2011 AS '2011_money_paid',
a.y2012 AS '2012_money_paid',
cu.first_name,
cu.last_name,
cu.email_primary,
cu.phone_primary
FROM (SELECT customer_id,
Sum(CASE
WHEN Year(date_paid) = 2010 THEN price
ELSE 0
end) AS 'y2010',
Sum(CASE
WHEN Year(date_paid) = 2011 THEN price
ELSE 0
end) AS 'y2011',
Sum(CASE
WHEN Year(date_paid) = 2012 THEN price
ELSE 0
end) AS 'y2012'
FROM customer_order
GROUP BY customer_id) a
LEFT JOIN customer cu
ON cu.customer_id = a.customer_id

结果

| CUSTOMER_ID | 2010_MONEY_PAID | 2011_MONEY_PAID | 2012_MONEY_PAID | FIRST_NAME | LAST_NAME | EMAIL_PRIMARY | PHONE_PRIMARY |------------------------------------------------------------------------------------------------------------------------------|           1 |            9000 |            3000 |            2000 |        Bob |     Smith | bob@smith.com |    1112223333 ||           2 |            4000 |            5000 |            1000 |        Tom |     Jones | tom@jones.com |    2223334444 |

See the demo

关于mysql - 按年份添加两列金额,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14403352/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com