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+子串怎么做？ + 替换？

我不太擅长 SQL 希望能够变得更好我在尝试执行某些表操作时遇到一些麻烦我希望能够从下面的 ProgUID 列中选择子字符串就像是 SUBSTRING table ProgUID 3 12 这将为我提供 ProgUID P CAMVE
MySQL 服务器未启动

当我做 mysql u root p并输入my password这就是我得到的错误 2002 HY000 无法通过套接字 var run mysqld mysqld sock 连接到本地 MySQL 服务器 2 所以我输入 systemc
mySQL MATCH 跨多个表

我有一组 4 个表我想对其进行搜索每个都有全文索引查询可以使用每个索引吗 CREATE TABLE categories id int 5 unsigned NOT NULL auto increment display order
如何将 MySQL 数据库更改为 UTC？

我使用的是 Windows 7 对数据库方面的东西有点陌生我尝试在 Google 上搜索如何将系统时区更改为 UTC 但文档有些高级我不太确定如何更改此字段在 my ini 文件的 mysqld 部分下添加以下行 default t
当“修复表”查询在 mysql 中不起作用时该怎么办？

我收到此错误表的存储引擎不支持修复当我尝试使用查询修复表时repair table tbl college master 表是 innodb 类型但我不知道我收到此错误 See 手册 http dev mysql com doc re
MySql 最后插入 ID，连接器 .net

我正在使用 MySql Connector net 我需要获取最后一个查询生成的插入 id 现在我假设返回值是MySqlHelper ExecuteNonQuery应该是最后一个插入id 但它只返回1 我正在使用的代码是 int inse
Python：如何使用生成器来避免 sql 内存问题

我有以下方法来访问 mysql 数据库并且查询在服务器中执行我无权更改有关增加内存的任何内容我对生成器很陌生并开始阅读更多有关它的内容并认为我可以将其转换为使用生成器 def getUNames self globalUserQu
查询中列的顺序重要吗？

当从 MySQL 表中选择列时与表中的顺序相比选择列的顺序是否会影响性能不考虑可能覆盖列的索引例如您有一个包含行 uid name bday 的表并且有以下查询 SELECT uid name bday FROM table M
如何使用 MySQL 的 LOAD DATA LOCAL INFILE 在导入 CSV 时将字符串日期更改为 MySQL 日期格式

我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中下面是一个 SQL 语句示例 LOAD DATA LOCAL INFILE file csv INTO TAB
PHP 绑定“bigint”数据类型（MySQLi 准备好的语句）

studentId 57004542323382 companyOfferId 7 sql INSERT INTO studentPlacement companyOfferId studentId VALUES if stmt db gt
Hibernate 对集合的查询过滤器

我想执行以下查询 from Item i where i categoryItems catalogId catId 然而这会产生以下异常非法尝试取消引用集合所以我用谷歌搜索找到了这个 Hibernate 论坛帖子https for
Mysql 检索所有有限制的行

我想检索特定用户的所有行限制为 0 x 所以我只是想问是否有任何方法可以检索 mysql 中的所有行而不调用返回 x 的 count id 的方法而不重载现有函数该函数在查询中根本没有限制与我们的 string Relace 功能
如何在Mysql中仅将不同的值从一个表复制到另一个表？

我有一个大约 2 5GB 的 MySql 数据库表 A 具有以下列 anoid query date item rank url 我刚刚创建了另一个仅包含列的表 b query and date 我想在查询列中插入所有不同的记录及其各自
无法通过套接字“/var/lib/mysql/mysql.sock”连接到本地 MySQL 服务器 (2)

当我尝试连接 mysql 时出现以下错误 Can t connect to local MySQL server through socket var lib mysql mysql sock 2 这个错误有解决办法吗其背后的原因可能是什
使用 PHP 的 MySQL 连接字符串

我正在尝试通过本地计算机连接到托管在我的服务器上的数据库我的服务器有cPanel 11 它是一个典型的共享服务器由CentOS提供支持安装了PHP和MySQL 准确地说我在同一台服务器上持有经销商帐户我想在不同帐户或域之间访问数据
Flask-login：无法理解它是如何工作的

我试图理解如何Flask Login https flask login readthedocs org en latest works 我在他们的文档中看到他们使用预先填充的用户列表我想使用数据库存储的用户列表但是我不明白其中的一些
在 PHP 字符串中格式化 MySQL 代码

是否有任何程序 IDE 可以在 PHP 字符串中格式化 MySQL 代码例如我使用 PHPStorm IDE 但它无法做到这一点它对 PHP 和 MYSQL 执行此操作但不适用于 php 字符串内的 MYSQL 我已准备好使用新的
一次从多个表中删除行

我正在尝试将 2 个查询合并为一个这样的查询 result db gt query DELETE FROM menu WHERE name new or die db gt error result db gt query DELETE F
映射 mysql 中同一个表的多个值

您好我必须使用另一个表中的值 id 获取文本值表 1 包含值 ID 表 2 包含名称和值 ID 表 1 SEVERITY OCCURENCE DETECTABILITY 2 3 4 表 2 id name value 1 Very Hi
如何修改现有表以添加时区

我有一个包含 500 多个表的大型应用程序我必须将应用程序转换为时区感知当前应用程序使用new java util Date GETDATE 与服务器的时区即没有任何时区支持我已将这项任务分为几个步骤以便于开发我确定的第一个步骤

随机推荐

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 如何创建这个子查询？的相关文章