gpt4 book ai didi

php - 从数据库中选择数据并在 MYSQL 中创建新列

转载 作者:行者123 更新时间:2023-11-30 22:57:53 28 4
gpt4 key购买 nike

我有一个包含三列的表格:countrycodeyearvaluesYear 由若干年组成(2000、2001 和 2002)。我需要选择特定年份的数据并将其显示为新列。

    SELECT g.countrycode, g.values AS '2000'
FROM `gainfinal` g
WHERE `year` = '2000'

我使用上面的查询返回了两列 year 和 2000,列“2000”包含 2000 年的值。现在我需要选择 2001 年和 2002 年的数据并以类似的方式显示它。我怎样才能再创建两个专栏两年。

最佳答案

您将使用条件聚合。将聚合函数与 CASE 语句相结合。

这是一个总结 2000 年到 2003 年之间的值(value)观的例子。

SELECT  g.countrycode,
sum(case when `year` = '2000' then g.values else 0 end) AS "2000",
sum(case when `year` = '2001' then g.values else 0 end) AS "2001",
sum(case when `year` = '2002' then g.values else 0 end) AS "2002",
sum(case when `year` = '2003' then g.values else 0 end) AS "2003"
FROM `gainfinal` g
WHERE `year` between '2000' and '2003'
group by g.countrycode

您的示例数据不包含任何年份为 2002 年或 2003 年的行。因此它们应该为零。

如果需要 2004 年的数据,则必须将 2004 添加到 WHERE 子句的范围内,并将它的 CASE 语句添加到 SELECT 列表中,如下所示。

但是 2002 和 2003 仍将全为零,因为它们是零。

SELECT  g.countrycode,
sum(case when `year` = '2000' then g.values else 0 end) AS "2000",
sum(case when `year` = '2001' then g.values else 0 end) AS "2001",
sum(case when `year` = '2002' then g.values else 0 end) AS "2002",
sum(case when `year` = '2003' then g.values else 0 end) AS "2003",
sum(case when `year` = '2004' then g.values else 0 end) AS "2004"
FROM `gainfinal` g
WHERE `year` between '2000' and '2004'
group by g.countrycode

您必须指定年份范围,并且在选择列表中为您想要输出的每一年都有一个 case 语句。对于要动态确定的列,没有纯sql的解决方案。为此,在 Excel 中制作数据透视表可能会更好。以上假定您知道要提前显示哪些年份的数据。

因此,如果您想要从 1995 年到 2012 年,则每年都必须有一个 CASE 语句,如下所示:

SELECT  g.countrycode,
sum(case when `year` = '1995' then g.values else 0 end) AS "1995",
sum(case when `year` = '1996' then g.values else 0 end) AS "1996",
sum(case when `year` = '1997' then g.values else 0 end) AS "1997",
sum(case when `year` = '1998' then g.values else 0 end) AS "1998",
sum(case when `year` = '1999' then g.values else 0 end) AS "1999",
sum(case when `year` = '2000' then g.values else 0 end) AS "2000",
sum(case when `year` = '2001' then g.values else 0 end) AS "2001",
sum(case when `year` = '2002' then g.values else 0 end) AS "2002",
sum(case when `year` = '2003' then g.values else 0 end) AS "2003",
sum(case when `year` = '2005' then g.values else 0 end) AS "2004",
sum(case when `year` = '2006' then g.values else 0 end) AS "2005",
sum(case when `year` = '2007' then g.values else 0 end) AS "2006",
sum(case when `year` = '2008' then g.values else 0 end) AS "2007",
sum(case when `year` = '2009' then g.values else 0 end) AS "2008",
sum(case when `year` = '2010' then g.values else 0 end) AS "2009",
sum(case when `year` = '2011' then g.values else 0 end) AS "2010",
sum(case when `year` = '2012' then g.values else 0 end) AS "2011",
sum(case when `year` = '2012' then g.values else 0 end) AS "2012"
FROM `gainfinal` g
WHERE `year` between '1995' and '2012'
group by g.countrycode

enter image description here

关于php - 从数据库中选择数据并在 MYSQL 中创建新列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25461911/

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