MySQL 如何创建这个子查询？

2023-11-21

我有以下表格

餐桌农场

+---------+--------+-------------------+-----------+------------+
| FARM_ID |Stock_ID| FARM_TITLE        | Size      | FARM_VALUE |
+---------+--------+-------------------+-----------+------------+
|       2 |      1 | AgriZone          | M         |        202 |
|       3 |      1 | Cow Mill          | L         |         11 |
|       4 |      2 | Beef Farm         | H         |        540 |
|       5 |      2 | CattleOne         | M         |       1080 |
|       6 |      2 | FarmOne           | L         |        455 |
|       7 |      3 | Perdue            | H         |        333 |
|       8 |      4 | Holstein          | M         |        825 |
|      10 |      1 | Dotterers         | H         |         98 |
+---------+--------+-------------------+-----------+------------+

桌门

+---------+---------+------------+
| GATE_ID | FARM_ID | FARM_VALUE |
+---------+---------+------------+
|       1 |       2 |          0 |
|       1 |       3 |          0 |
|       1 |       4 |        540 |
|       2 |       4 |        550 |
|       3 |       4 |        560 |
|       4 |       4 |        570 |
|       5 |       4 |        580 |
|       6 |       4 |        590 |
|       1 |       5 |       1080 |
|       2 |       5 |       1100 |
|       3 |       5 |       1120 |
|       4 |       5 |       1140 |
|       5 |       5 |       1160 |
|       6 |       5 |       1180 |
|       1 |       6 |        455 |
|       2 |       6 |        536 |
|       3 |       6 |        617 |
|       4 |       6 |        698 |
|       5 |       6 |        779 |
|       6 |       6 |        860 |
|       1 |       7 |          0 |
|       1 |       8 |          0 |
|       1 |      10 |          0 |
+---------+---------+------------+

表原点

+--------+----------+
| ORI_ID | ORI_NAME |
+--------+----------+
|      1 |   US     |
|      2 |   CA     |
|      3 |   MX     |
+--------+----------+

表库存

+--------+--------+-------------------+
|Stock_ID| ORI_ID | Stock_TITLE       |
+--------+--------+-------------------+
|      1 |      1 | P1                |
|      2 |      2 | P3                |
|      3 |      3 | Q4                |
|      4 |      3 | B3                |
+--------+--------+-------------------+

表结果

+-----------+---------+---------+------------+------------+
| RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE |    Score%  |
+-----------+---------+---------+------------+------------+
|         1 |       7 |       1 |        333 |        100 |
|         2 |       8 |       1 |        825 |        100 |
|         3 |       6 |       1 |        455 |         40 |
|         4 |       6 |       2 |        536 |          0 |
|         5 |       6 |       3 |        617 |          0 |
|         6 |       6 |       4 |        698 |        100 |
|         7 |       6 |       5 |        779 |          0 |
|         8 |       6 |       6 |        860 |         10 |
|         9 |       4 |       1 |        540 |        100 |
|        10 |       4 |       2 |        550 |         90 |
|        11 |       4 |       3 |        560 |          0 |
|        12 |       4 |       4 |        570 |        100 |
|        13 |       4 |       5 |        580 |         10 |
|        14 |       4 |       6 |        590 |          0 |
|        15 |       5 |       1 |       1080 |          0 |
|        16 |       5 |       2 |       1100 |          0 |
|        17 |       5 |       3 |       1120 |          0 |
|        18 |       5 |       4 |       1140 |         50 |
|        19 |       5 |       5 |       1160 |          0 |
|        20 |       5 |       6 |       1180 |        100 |
|        21 |       3 |       1 |         11 |        100 |
|        22 |      10 |       1 |         98 |         90 |
|        23 |       2 |       1 |        202 |        100 |
+-----------+---------+---------+------------+------------+

带注释的结果表：同上^

+-----------+---------+---------+------------+------------+
| RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE |   Score%   |
+-----------+---------+---------+------------+------------+

+-----------+---------+---------+------------+------------+
|         1 |       7 |       1 |        333 |        100 | <--|H-Case {H}
+-----------+---------+---------+------------+------------+    

+-----------+---------+---------+------------+------------+     
|         2 |       8 |       1 |        825 |        100 | <--|M-Case {M}
+-----------+---------+---------+------------+------------+

+-----------+---------+---------+------------+------------+
|         3 |       6 |       1 |        455 |         40 |
|         4 |       6 |       2 |        536 |          0 |
|         5 |       6 |       3 |        617 |          0 |
|         6 |       6 |       4 |        698 |        100 |  <--|L
|         7 |       6 |       5 |        779 |          0 |     |
|         8 |       6 |       6 |        860 |         10 |     |
+-----------+---------+---------+------------+------------+     |
|         9 |       4 |       1 |        540 |        100 |     |
|        10 |       4 |       2 |        550 |         90 |     |
|        11 |       4 |       3 |        560 |          0 |     |
|        12 |       4 |       4 |        570 |        100 |  <--+M-case {H,M,L}
|        13 |       4 |       5 |        580 |         10 |     |
|        14 |       4 |       6 |        590 |          0 |     |
+-----------+---------+---------+------------+------------+     |
|        15 |       5 |       1 |       1080 |          0 |     |
|        16 |       5 |       2 |       1100 |          0 |     |
|        17 |       5 |       3 |       1120 |          0 |     |
|        18 |       5 |       4 |       1140 |         50 |  <--|H
|        19 |       5 |       5 |       1160 |          0 |
|        20 |       5 |       6 |       1180 |        100 |
+-----------+---------+---------+------------+------------+

+-----------+---------+---------+------------+------------+
|        21 |       3 |       1 |         11 |        100 | <--|L
|        22 |      10 |       1 |         98 |         90 | <--+H-case {H,M,L}
|        23 |       2 |       1 |        202 |        100 | <--|M
+-----------+---------+---------+------------+------------+

所需计算：

Type 最多只能有三个值：{H,M,L}；
当所有值都存在时，它们的分级如下：H=70 M=20 L=10
所有独特的案例都是
情况{H,M}：H=80 M=20
情况{M,L}：M=60 L=40
情况{H,L}：H=90 L=10
情况{H}：H=100
情况{M}：M=100
情况{L}：L=100
情况{H,M,L}：H=70 M=20 L=10

进一步说明

Only Stock with with atleast one GATE, fully satisfied can get 100 points max
1. 例子：Q4有 3 套 6 个GATES;只有一个GATE必须满足设置（有分数）。
2. 所给出的点必须乘以它所涉及的特定情况示例：Q4情况为 {H,M,L}，这意味着 H=70； M=20； L=10 这将导致 (70*100%)+(20*50%)+(10*100%)=90（回顾上面的结果表注释）
即使未完全满足某个门，仍应考虑并计算分数。当没有完全满足的门时，应保留获得 MAX 积分的门。（如有不明白的会进一步解释）

如果我们执行查询来理解表和数据，它将如下所示

+---------+-----------+---------------+-----------+---------+-----------+---------+
| Origin  | Stock     | Farm Title    | Farm Value|   Gate  |  Size     |  Score  |
+---------+-----------+---------------+-----------+---------+-----------+---------+
| US      | P1        | Perdue        |       333 |       1 | H         |     100 |
| US      | P3        | Holstein      |       825 |       1 | M         |     100 |
| CA      | Q4        | FarmOne       |       455 |       1 | L         |      40 |
| CA      | Q4        | FarmOne       |       536 |       2 | L         |       0 |
| CA      | Q4        | FarmOne       |       617 |       3 | L         |       0 |
| CA      | Q4        | FarmOne       |       698 |       4 | L         |     100 |
| CA      | Q4        | FarmOne       |       779 |       5 | L         |       0 |
| CA      | Q4        | FarmOne       |       860 |       6 | L         |      10 |
| CA      | Q4        | Beef Farm     |       540 |       1 | H         |       0 |
| CA      | Q4        | Beef Farm     |       550 |       2 | H         |      90 |
| CA      | Q4        | Beef Farm     |       560 |       3 | H         |       0 |
| CA      | Q4        | Beef Farm     |       570 |       4 | H         |     100 |
| CA      | Q4        | Beef Farm     |       580 |       5 | H         |      10 |
| CA      | Q4        | Beef Farm     |       590 |       6 | H         |       0 |
| CA      | Q4        | CattleOne     |      1080 |       1 | M         |       0 |
| CA      | Q4        | CattleOne     |      1100 |       2 | M         |       0 |
| CA      | Q4        | CattleOne     |      1120 |       3 | M         |       0 |
| CA      | Q4        | CattleOne     |      1140 |       4 | M         |      50 |
| CA      | Q4        | CattleOne     |      1160 |       5 | M         |     100 |
| CA      | Q4        | CattleOne     |      1180 |       6 | M         |       0 |
| MX      | B3        | Cow Mill      |        11 |       1 | L         |     100 |
| MX      | B3        | Dotterers     |        98 |       1 | H         |      90 |
| MX      | B3        | AgriZone      |       202 |       1 | M         |     100 |
+---------+-----------+---------------+-----------+---------+-----------+---------+

渴望结果

+---------+-------------------+-------+
| Origin  |  Stock            | score |
+---------+-------------------+-------+
| US      |   P1              |   100 |
| US      |   P3              |   100 |
| CA      |   Q4              |    90 |
| MX      |   B3              |    93 |
+---------+-------------------+-------+

Explanation

Since origin has a stock其中由 3 个不同的farms以及那些farms have 6 gates每个。只要有一个gate-set（数字匹配gates）被得分为某个值，我们可以考虑整个STOCK完全找到了。这是唯一的方法stock可以考虑100。

此外，并重申，STOCKQ4 的情况为：{H,M,L} 以及所有gate (4)在某种程度上被发现了。gate4 的得分 (100% * H) + (50% * M) + (100% * L) 等于 (70*100%) + (20*50%) + (10*100%) = 90

因此：（取自上面）

  | CA      |   Q4              |    90 |

QED

那么我需要什么帮助正在创建子查询/子选择来完成此计算。我在下面的 SQL 小提琴链接中设置了上述场景中的所有内容（以及我一直在处理的正在进行的查询）。

非常感谢 stackoverflow 社区。

> SqlFiddle中的上述问题可以在这里找到

这是我一直在处理的查询。但是，结果与您在问题中发布的结果略有不同：

select o.origin_name, s.stock_title, sum(
  case f.size
    when 'H' then
      case
        when sizes = 'H,L,M' then 70
        when sizes = 'H,M' then 80
        when sizes = 'H,L' then 90
        when sizes = 'H' then 100
        else 0
      end
    when 'M' then
      case
        when sizes = 'H,L,M' then 20
        when sizes = 'H,M' then 20
        when sizes = 'L,M' then 60
        when sizes = 'M' then 100
        else 0
      end
    else
      case
        when sizes = 'H,L,M' then 10
        when sizes = 'L,M' then 40
        when sizes = 'H,L' then 10
        when sizes = 'L' then 100
        else 0
      end
  end * r.score / 100) FinalScore
from farm f
join (
  select f.stock_id, group_concat(distinct f.size order by f.size) sizes
  from farm f
  join results r on f.farm_id = r.farm_id
  group by f.stock_id
) stockSizes on f.stock_id = stockSizes.stock_id
join results r on f.farm_id = r.farm_id
join (
  select f.stock_id, r.gate_id
  from results r
  join farm f on r.farm_id = f.farm_id
  group by f.stock_id, r.gate_id
  having sum(r.score = 0) = 0
) FullGates
on FullGates.stock_id = f.stock_id and FullGates.gate_id = r.gate_id
join stock s on s.stock_id = f.stock_id
join origin o on o.origin_id = s.origin_id
group by o.origin_id, s.stock_id

Result:



+-------------+-------------+------------+
| ORIGIN_NAME | STOCK_TITLE | FINALSCORE |
+-------------+-------------+------------+
| US          | P1          |         93 |
| CA          | P3          |         90 |
| MX          | Q4          |        100 |
| MX          | B3          |        100 |
+-------------+-------------+------------+

让我知道这是否有效。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

subquery

MySQL 如何创建这个子查询？的相关文章

MySQL分层存储：搜索所有父母/祖父母等。给定子节点 id 的节点？

我使用分层模型存储类别如下所示 CATEGORIES id parent id name 1 0 Cars 2 0 Planes 3 1 Hatchbacks 4 1 Convertibles 5 2 Jets 6 3 Peugeot 7
即使更新语句没有影响任何行，ExecuteNonQuery 返回 1

我在这里面临一个非常奇怪的问题我的 DAL 是使用编写的OdbcConnection对象并且工作完美然而我必须尊重一些要求因此必须移动系统来使用MySqlConnection 你会说不应该有任何问题但是现在有一点误解当我执行
如何在 kubernetes 上使多个 pod 相互通信

我是 Kubernetes 新手我正在尝试通过 microk8s 将应用程序部署到 Kubernetes 该应用程序包含Python Flask后端 Angular前端 Redis和MySQL数据库我将映像部署在多个 Pod 中状态显
从heroku 提取数据库失败并出现 Encoding::CompatibilityError

我在执行 db pull 从 heroku 回到本地开发环境时遇到一些问题我的设置是通过 RVM 在 Mac OS X Snow Leopard 下的本地 Rails 3 Ruby 1 9 2 环境上通过 MacPorts 安装 MySQ
如何从 Qt 应用程序通过 ODBC 连接到 MySQL 数据库？

我有一个新安装的 MySQL 服务器它监听 localhost 3306 从 Qt 应用程序连接到它的正确方法是什么原来我需要将MySQL添加到ODBC数据源我在遵循这个视频教程后做到了这一点 https youtu be K3GZi
在 MySQL 连接字符串中指定密码

我使用 MySQL 作为 DB 和 Yeoman 生成器创建了 ExpressJS MVC 应用程序并在config js我想更改 MySQL 连接字符串但我不知道在字符串中指定密码我的字符串是mysql root localhost
使用 PHP MySql 进行关键字搜索？

我的 mysql 表中有标题 varchar 描述 text 关键字 varchar 字段我保留了关键字字段因为我认为我只会在这个字段中搜索但我现在需要在所有三个字段中进行搜索所以对于关键字 word1 word2 word3 我的
使用整数数组设置外键

我对使用 SQL 还很陌生但我在 Stack Overflow 上遇到过这个关于使用标签的问题推荐用于标记或标记的 SQL 数据库设计 https stackoverflow com questions 20856 recommende
MySQL - 使用可变路径加载数据文件

我在设置用于将数据放入表中的变量路径时遇到问题这就是我构建路径的方式 SET path1 CONCAT C Projekte Metrics DXL CSV EXPORT DATA YEAR NOW MONTH NOW DAY NOW B
了解自加入

我正在练习自加入这是我在编写查询时不明白的事情我有一张桌子 employee 员工表包含三个记录 id employee manager id 1 Ola NULL 2 Ahmed 1 3 Tove 1 最后一列 manager id 引
MySQL/PHP 插入同一行两次

我不明白为什么这段代码会插入同一行两次我已经将其精简为以下代码它被插入的 MySQL 表中有 10 列但即使查询中提到了所有这些列它仍然插入电子邮件受保护 cdn cgi l email protection 分两行具有单独的主
MYSQL从另一个表插入id

我有以下疑问我有 2 张桌子 id customers 1 alan 2 beth 3 john and id id customers value 1 1 bar 2 1 foo 3 2 baz 示例我需要在第二个表中添加值 alfa
检查字段是否为空

如果我想检查该字段是否有除 null 和空之外的其他字符查询是否正确 select CASE WHEN description IS NULL THEN null WHEN description IS NOT NULL THEN not
当按第三个分组时，MySQL 根据另一个字段的最小值更新字段

我已经阅读了几个关于选择最小值分组等的线程但似乎无法创建有效的查询来解决这个问题如有重复敬请原谅我有一个像这样的表 ID Date Value Tag 1 1 1 13 500 NULL 2 1 1 13 10 NULL 3 1
返回表中不存在的记录

如何获取表中没有记录的ID 例如 select id name mail from users where id in 2 3 4 5 6 该查询返回记录 2 3 4 的输出但不返回记录 5 和 6 因为表中不存在记录现在我想知道表中没
如何解决这个错误--dbWriteTable()

我成功连接到 MYSQL DB 并尝试将结果写回数据库 dbWriteTable con predicted min forecast min 其中 Forecast min 只是双精度向量我收到此错误消息函数类 fdef mtabl
设置 MySQL 触发器

我听说过有关触发器的事情我有几个问题什么是触发器我该如何设置它们除了典型的 SQL 内容之外是否还应该采取任何预防措施触发器允许您在发生某些事件例如插入表时在数据库中执行某个功能我无法具体评论mysql 注意事项触发器
土耳其语字符显示不正确[重复]

这个问题在这里已经有答案了 MySql 数据库使用 utf 8 编码数据存储正确我使用 set name utf8 查询来确保调用的数据是 utf 8 编码只要标头字符集是 utf 8 数据库中的所有变量都可以正常工作但静态html
为什么我的 php 代码无法连接到远程 MySql 数据库？

我正在尝试连接到远程 MySql 数据库但收到以下错误消息警告 mysqli connect HY000 2002 连接尝试失败因为连接方在一段时间后没有正确响应或者由于连接的主机未能响应而建立的连接失败在 C myLocalDi
Mysql：多个表还是一张大表？

这个问题已经被问过但我还没有找到 1 个语音答案最好这样做 1 张大桌子其中用户 ID 属性 1 属性 2 属性 3 属性 4 或 4 个小桌子其中用户 ID 属性 1 用户 ID 属性 2 用户 ID 属性 3 用户 ID 属

随机推荐

jQuery 验证 - 需要工作但不接受

我正在上传图像并尝试使用 jquery 对其进行验证这是我的代码
MATLAB 中的纹理映射

我有 3D 空间中的点及其相应的 2D 图像点如何利用 3D 点制作网格然后对网格形成的三角形面进行纹理处理请注意该函数trisurf您最初尝试使用的返回一个句柄patch目的如果你看一下 FaceColor 补丁对象的属性你可以
文件路径中的 Java JTree 目录结构

我一直在努力解决这个问题所以也许你们中的一些人可以帮助我我有一个文件列表及其完整路径这些只是文件位于另一台计算机上的字符串例如 C a b c file1 txt C a b c file2 txt C a d file3 txt
检测可滚动小部件是否手动或以编程方式滚动

有没有办法检测是否滚动 PageView PageController or Scrollable小部件 ScrollController 由用户手动完成或使用编程方式完成jumpTo or animateTo 在控制器对象上将可滚动小部
Django 管理员注册内联用户配置文件管理员时出现问题

我目前正在开发一个 django 项目我正在尝试将 UserProfile 模型内联添加到我的 User 模型中在我的 models py 中我有 class UserProfile models Model Extension to
将网格线与点阵图形中的轴刻度对齐

如果选择自定义网格线是否有办法对齐网格线默认情况下 panel grid h 1 v 1 可以完成这项工作但我想根据轴刻度细化网格将 h 和 v 设置为轴刻度的负长度并不总是有效更新下面是一个例子我希望实现水平网格线与 y 轴
从 heapq python 中弹出最大值，Python 中有最大堆吗？ [复制]

这个问题在这里已经有答案了可能的重复在 Python 中使用什么来实现最大堆我正在尝试以某种方式实现 python 的 heapq 但对于最大堆解决方案是使用 1 和多个队列编号但这对我没有帮助因为我需要在堆中存储 url 所以
如何更改 HTML5 表单验证消息中的图像？

Using 本文我正在更改一些 HTML5 表单验证弹出窗口的样式但是除了我添加的新红色 X 之外我的错误弹出窗口仍然在错误中显示默认的橙色感叹号如何去掉橙色感叹号见下图到目前为止我只在 Chrome 中进行测试这是我在那
仅显示 SQLite 中的视图

我怎样才能在 SQLite 中只看到视图 tables命令列出视图和表我只想查看视图而不是表格 Thanks 你可以做 SELECT sql FROM sqlite master WHERE type view 为了对其进行微调 head
匹配通配符严格，但找不到元素 'context:component-scan 的声明

我在尝试我的第一个 Spring 项目时遇到以下错误 Caused by org xml sax SAXParseException cvc complex type 2 4 c The matching wildcard is stric
django中如何获取上传的文件名

问题背景我是 Django 新手我正在尝试从客户端上传文件并保存它为此我创建了以下模型 from django db import models class UploadFile models Model uploadfile mod
双问号 (??) 运算符在 PHP 中意味着什么 [重复]

这个问题在这里已经有答案了我深入研究 Symfony 框架版本 4 代码并发现了这段代码 env SERVER APP ENV dev 我不确定这实际上是做什么的但我想它会扩展到类似以下内容 env SERVER APP ENV nu
指令中的 2 个“postLink”函数有什么区别？

从 angularjs 的文档来看定义指令时有一个postLink in compile and a postLink in link myModule directive directiveName function factory
SendInput() 不等于在 C++ 中手动在键盘上按键？

我想编写一个 C 代码来模拟按下键盘键 A Set up a generic keyboard event ip type INPUT KEYBOARD ip ki wScan 0 hardware scan code for key ip
字典理解中的三元表达式

我正在尝试反转字典在许多键具有相同值的情况下新键旧值应该与一组新值旧键相关联我解决了这个问题但我正在尝试使用字典理解和一些辅助方法进行重构 def has unique value k return d values cou
如何同时获取两个数组元素的可变引用？

fn change a mut i32 b mut i32 let c a a b b c fn main let mut v vec 1 2 3 change mut v 0 mut v 1 当我编译上面的代码时出现错误 error E
为什么 akka 的 spring 集成文档仅存在于 1.3.1 而不适用于后续版本

有一个关于集成方式的链接AKKA and Spring 或者更好地说如何使用Akka演员在spring环境 http doc akka io docs akka modules 1 3 1 modules spring html 其实有m
如何更改 R 中矩阵和数据框的暗名称

假设我创建了以下矩阵 gt x lt matrix 1 20000 nrow 100 gt x 1 10 1 10 1 2 3 4 5 6 7 8 9 10 1 1 101 201 301 401 501 601 701 801 901 2
可变长度数组的原型

我正在尝试编写一个函数该函数采用 c 中可变大小的数组 void sort int s int e int arr 它表示对于可变长度数组需要在函数声明中对其进行限制这意味着什么我使用 xcode 4 0 和 LLVM 编译器 2
MySQL 如何创建这个子查询？

我有以下表格餐桌农场 FARM ID Stock ID FARM TITLE Size FARM VALUE 2 1 AgriZone M 202 3 1 Cow Mill L 11 4

MySQL 如何创建这个子查询？

MySQL 如何创建这个子查询？ 的相关文章

随机推荐

热门标签

MySQL 如何创建这个子查询？的相关文章