gpt4 book ai didi

Mysql select with CASE 未检索其他列值?

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

作为我从问题中得到的答案,这让我因为某种原因而疯狂地应用我的代码,为什么它不起作用。

这是我表上的部分数据。

enter image description here

原始代码:

SELECT AGE_RANGE, COUNT(*) FROM (
SELECT CASE
WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
WHEN YearsOld > 40 THEN '40+'
END AS 'AGE_RANGE'
FROM (
SELECT YEAR(CURDATE())-YEAR(DATE(birthdate)) 'YearsOld'
FROM MyTable
) B
) A
GROUP BY AGE_RANGE

这是结果。

enter image description here

我想做的是,我正在尝试添加另一列,该列将计算该区域中有多少人,该区域将是位置,正如您在图片上看到的那样其中包括珀斯、西澳大利亚、森伯里、维多利亚等。

第一次尝试解决我的问题

正如您在下面看到的,我添加了位置和 COUNT(location) loc 来获取位置名称并计算表中重复的位置数量。

SELECT AGE_RANGE, COUNT(*), location, COUNT(location) loc FROM (
SELECT CASE
WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
WHEN YearsOld > 40 THEN '40+'
END AS 'AGE_RANGE', 'location'
FROM (
SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld'
FROM event_participants
) B
) A
FROM event_participants WHERE location <> '' GROUP BY location HAVING loc >= 1 ORDER BY loc DESC LIMIT 5

哪些结果......

enter image description here

显然,听到上帝的帮助并没有效果。

我第二次尝试解决此问题

SELECT AGE_RANGE, COUNT(*), location FROM (
SELECT CASE
WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
WHEN YearsOld > 40 THEN '40+'
END AS 'AGE_RANGE', 'location'
FROM (
SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld'
FROM event_participants
) B
) A
GROUP BY AGE_RANGE

结果是......

enter image description here

它只显示位置,而不是显示国家/地区名称。

嗯..

长话短说,代码有什么问题?这个问题有什么解决办法吗?

就像,我想要的结果是,

AGE_RANGE COUNT(*) location            total number of same location

16-20 4 Name of location #

21-30 45 Name of location #

31-40 79 Name of location #

40+ 102 Name of location #

最佳答案

这应该可行:将每个子查询视为它自己的表,因此您只需确保选择所需的所有列即可。 (CASE WHEN THEN END AS) 实际上只是一个单列值。您可能还想按位置分组,以便可以在不同位置获得相同的年龄范围。 (例如,加拿大为 11-15,美国为 11-15)

SELECT AGE_RANGE, COUNT(*), A.location FROM (
SELECT CASE
WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
WHEN YearsOld > 40 THEN '40+'
END AS 'AGE_RANGE', B.location
FROM (
SELECT YEAR(CURDATE())-YEAR(DATE(birthday)) 'YearsOld',
location /* << just missing this select */
FROM event_participants
) B
) A
GROUP BY A.location, AGE_RANGE

关于Mysql select with CASE 未检索其他列值?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17335285/

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