基于最佳比的MySQL显示提取
基于最佳比的MySQL显示提取
提问于 2014-10-02 05:49:22
9月21日
30/T1459.1-1988个人电脑销售产品的商品、产品、产品等产品
11/T1582-1997键盘销售、商品、商业、金融等领域
14/T1459.1-1993扬声器销售的商品、商品、产品等
商品
*-*
9月22日
10/T1459.1-1988个人产品销售商品商品、产品等
/T1459.2-1993键盘销售商品、商品等
14/T1459.1-1993扬声器销售的商品、商品、产品等
商品
*-*
*每台电脑出售的额外费用(总和(键盘、扬声器、鼠标)/个人电脑)
我想出的最有效的方法是，与最高比率的一天相比，整个月的总和都是无效的：
$result = mysql_query("SELECT User,
DATE(`Created`) as DATE,
sum(PCs) as PCsTotal,
sum(Keyboards) as KeyboardsTotal,
sum(Speakers) as SpeakersTotal,
sum(Mices) as MiceTotal,
sum(KeyboardTotal + SpeakersTotal + MiceTotal) as Extras,
(Extras / PCsTotal) as Ratio
From my_db
where `Created` >= DATE_SUB(curdate(), INTERVAL 1 MONTH) and `Created` <= CURDATE() - INTERVAL DAY(NOW()) - 0 day
GROUP BY Ratio
HAVING (sum(KeyboardsTotal)>5) and (sum(MiceTotal)>5)
LIMIT 1请求的结果：
作为HTML表中的提取显示的最高到最低比率，对每个用户都是唯一的：
/T1391-1988用户准准、高、高、低、高
15/T1583-1993基尔、技术和技术等
14/T1593-1995技术
/T1591-1990技术技术
(约翰不符合条件)
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-10-02 06:39:55
请尝试以下查询：
SELECT a.User, a.`DATE`, a.PCsTotal, a.KeyboardsTotal, a.SpeakersTotal,
a.MiceTotal, a.Extras, a.Ratio
FROM (
SELECT User,
DATE(`Created`) as `DATE`,
SUM(PCs) as PCsTotal,
SUM(Keyboards) as KeyboardsTotal,
SUM(Speakers) as SpeakersTotal,
SUM(Mices) as MiceTotal,
SUM(KeyboardTotal + SpeakersTotal + MiceTotal) as Extras,
(SUM(KeyboardTotal + SpeakersTotal + MiceTotal) / SUM(PCs)) as Ratio
FROM my_db
WHERE `Created` >= DATE_SUB(curdate(), INTERVAL 1 MONTH)
AND `Created` <= CURDATE() - INTERVAL DAY(NOW()) - 0 day
GROUP BY User, `DATE`
HAVING (sum(KeyboardsTotal)>5) and (sum(MiceTotal)>5)
) a
INNER JOIN (
SELECT User, MAX(Ratio) as max_day_ratio
FROM (
SELECT User,
DATE(`Created`) as `DATE`,
(SUM(KeyboardTotal + SpeakersTotal + MiceTotal) / SUM(PCs)) as Ratio
FROM my_db
WHERE `Created` >= DATE_SUB(curdate(), INTERVAL 1 MONTH)
AND `Created` <= CURDATE() - INTERVAL DAY(NOW()) - 0 day
GROUP BY User, `DATE`
HAVING (sum(KeyboardsTotal)>5) and (sum(MiceTotal)>5)
) c
GROUP BY User
) b ON b.User = a.User
WHERE a.Ratio = b.max_day_ratio页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/26155094
复制相关文章
相似问题
腾讯云开发者
