gpt4 book ai didi

MySQL:用例何时对共享和不共享的值求和/求平均值?

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

我有一个查询要扩展,但遇到了障碍。我想要做的是返回包含围绕共享和非共享属性提供的数据的计数、总和和平均值的行。

我已经非常接近了,但是在我需要查看数据的地方返回了空值和 0。

让我解释一下...但如果我需要澄清,请告诉我。

首先这是我的 table :

CREATE TABLE `fruits` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`fruit` varchar(11) DEFAULT NULL,
`fruit_attribute` varchar(11) DEFAULT '',
`submissions` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

INSERT INTO `fruits` (`id`, `fruit`, `fruit_attribute`, `submissions`)
VALUES
(1,'Orange','tough peel',59),
(2,'Lemon','tough peel',70),
(3,'Orange','citrus',100),
(4,'Orange','juice',90),
(5,'Lemon','juice',75),
(6,'Lemon','tart',35),
(7,'Lemon','citurs',65),
(8,'Orange','breakfast',110),
(9,'Lemon','lemonaid',120),
(10,'Orange','florida',50);

接下来,我的查询:

SELECT ft.fruit,  
COUNT(distinct ft1.fruit_attribute) As att_shared_lemon,
SUM(CASE WHEN ft1.fruit_attribute IS NULL THEN 1 ELSE 0 END) As not_shared_lemon,
SUM(CASE WHEN ft1.fruit_attribute IS NOT NULL THEN ft.submissions END) as sum_shared_submissions,
SUM(CASE WHEN ft1.fruit_attribute IS NULL THEN ft.submissions END) as sum_notshared_submissions
FROM fruits ft LEFT JOIN
fruits ft1
ON ft.fruit_attribute = ft1.fruit_attribute and ft1.fruit = 'Orange'
GROUP BY ft.fruit
having fruit='Orange'
ORDER BY att_shared_lemon desc;

这是上面的 SQL fiddle :

http://sqlfiddle.com/#!9/86e863/12

期望的输出不包括下面看到的 0 和 Null 值:

+--------+------------------+------------------+------------------------+---------------------------+
| fruit | attr_shared_orange | attr_not_shared_orange| sum_shared_submissions | sum_notshared_submissions |
+--------+------------------+------------------+------------------------+---------------------------+
| Orange | 5 | 0 | 409 | (null) |
+--------+------------------+------------------+------------------------+---------------------------+

取而代之的是“橙色”未共享的属性总数以及未与“橙色”共享的属性的提交总和

我在 mac Yosemite 上运行 mysql 5.6。

理想情况下,我想在没有子选择的情况下实现这一点,但如果它是必需的并且没有选择,那么我想了解更多相关信息。

最佳答案

我认为这里的连接逻辑存在一个小问题,您想对水果之间的链接求和,但您的查询确保您始终将橙子连接到橙子,因此永远不会有不存在的属性 分享:

ON ft.fruit_attribute = ft1.fruit_attribute 和 ft1.fruit = 'Orange'

试试这个查询:

SELECT ft.fruit,  
COUNT(distinct ft1.fruit_attribute) As att_shared_lemon,
SUM(CASE WHEN ft1.fruit_attribute IS NULL THEN 1 ELSE 0 END) As not_shared_lemon,
SUM(CASE WHEN ft1.fruit_attribute IS NOT NULL THEN ft.submissions END) as sum_shared_submissions,
SUM(CASE WHEN ft1.fruit_attribute IS NULL THEN ft.submissions END) as sum_notshared_submissions
FROM fruits ft

LEFT JOIN fruits ft1
ON ft.fruit_attribute = ft1.fruit_attribute and ft.fruit = 'Orange'
AND ft1.fruit != ft.fruit

WHERE ft.fruit='Orange'

GROUP BY ft.fruit
ORDER BY att_shared_lemon desc;

关于MySQL:用例何时对共享和不共享的值求和/求平均值?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33474157/

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