MySQL如何创建此子查询? [英] MySQL How do I create this subquery?
本文介绍了MySQL如何创建此子查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下表格
餐桌农场
+---------+--------+-------------------+-----------+------------+
| 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
+-----------+---------+---------+------------+------------+
需要计算:
- 类型最多只能包含三个值:{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
进一步的说明
- 仅拥有至少一个
GATE的股票，完全满意可以得到最高100分- 示例:
Q4具有3组，每组6个GATES；只需满足一组GATE(具有分数). - 存在的点必须与它所涉及的特定情况相乘示例:
Q4的情况为{H，M，L}，表示H = 70； M = 20； L = 10这将导致(70 * 100％)+(20 * 50％)+(10 * 100％)= 90 (回头看一下结果表注释)
2.
- 示例:
- 即使未完全满足要求，仍应考虑并考虑点数.如果尚未完全满足登机口的要求，则应保留获得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 |
+---------+-------------------+-------+
因为origin具有一个stock，该stock由3个不同的farms组成，而这些farms分别具有6个gates.只要将一个gate集(在数值上匹配gates)评分为某些值，我们就可以认为完全找到了整个STOCK.这是stock可以视为100的唯一方法.
此外，重申一下，STOCK Q4的情况为:{H，M，L}，并且所有gate (4)都在某种程度上被发现. gate 4的分数(100％* H)+(50％* M)+(100％* L)等于(70 * 100％)+(20 * 50％)+(10 * 100％)= 90
因此:(从上方拍摄)
| CA | Q4 | 90 |
QED
所以我需要帮助的是创建子查询/子选择以使此计算有效.我在下面的SQL小提琴链接中设置了上述场景中的所有内容(以及我一直在处理的查询).
非常感谢stackoverflow社区.
解决方案
这是我一直在处理的查询.但是，结果与您在问题中发布的结果略有不同:
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
结果:
+-------------+-------------+------------+ | ORIGIN_NAME | STOCK_TITLE | FINALSCORE | +-------------+-------------+------------+ | US | P1 | 93 | | CA | P3 | 90 | | MX | Q4 | 100 | | MX | B3 | 100 | +-------------+-------------+------------+
让我知道这是否奏效.
I have the following tables
Table Farm
+---------+--------+-------------------+-----------+------------+
| 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 |
+---------+--------+-------------------+-----------+------------+
Table Gate
+---------+---------+------------+
| 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 |
+---------+---------+------------+
Table Origin
+--------+----------+
| ORI_ID | ORI_NAME |
+--------+----------+
| 1 | US |
| 2 | CA |
| 3 | MX |
+--------+----------+
Table Stock
+--------+--------+-------------------+
|Stock_ID| ORI_ID | Stock_TITLE |
+--------+--------+-------------------+
| 1 | 1 | P1 |
| 2 | 2 | P3 |
| 3 | 3 | Q4 |
| 4 | 3 | B3 |
+--------+--------+-------------------+
Table Results
+-----------+---------+---------+------------+------------+
| 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 |
+-----------+---------+---------+------------+------------+
Annotated Result table: Same as above ^
+-----------+---------+---------+------------+------------+
| 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
+-----------+---------+---------+------------+------------+
Computations required:
- Type can have only three values at most: {H, M, L};
- When all values are present, they are graded as followed: H=70 M=20 L=10
ALL unique casese are
Case {H,M} : H=80 M=20
- Case {M,L} : M=60 L=40
- Case {H,L} : H=90 L=10
- Case {H} : H=100
- Case {M} : M=100
- Case {L} : L=100
- Case {H,M,L} : H=70 M=20 L=10
Further Explanation
- Only Stock with with atleast one
GATE, fully satisfied can get 100 points max- Example:
Q4has 3 sets of 6GATES; Only oneGATEset has to be satsified (have a score present). - The points present must be Multiplied against the particular case it pertains Example:
Q4has case {H,M,L} which means H=70; M=20; L=10 This would results in (70*100%)+(20*50%)+(10*100%)=90 (look back above at the Result table annotations)
2.
- Example:
- Points should still be considered and accounted for even when a gate hasn't been fully satisfied. The gate with MAX points earned should be kept when no gates have been fully satisfied. (Will provide further explanation if not understood)
If we Perform a query to make sense of the tables and data it will look like below
+---------+-----------+---------------+-----------+---------+-----------+---------+
| 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 |
+---------+-----------+---------------+-----------+---------+-----------+---------+
Desire Results
+---------+-------------------+-------+
| Origin | Stock | score |
+---------+-------------------+-------+
| US | P1 | 100 |
| US | P3 | 100 |
| CA | Q4 | 90 |
| MX | B3 | 93 |
+---------+-------------------+-------+
Since origin has a stock which consists of 3 different farms and those farms have 6 gates each. As long as one gate-set (numerically matching gates) is scored to SOME value we can consider the entire STOCK found completely. This is the only way a stock can be considered 100.
Moreover and to reiterate, STOCK Q4 has case: {H,M,L} and all of gate (4) was found to some degree. gate 4 has the score (100% * H) + (50% * M) + (100% * L) which equals (70*100%) + (20*50%) + (10*100%) = 90
Hence: (Taken from above)
| CA | Q4 | 90 |
QED
So what I need help with is creating the subquery/subselect to do make this computation work. I set up everything in the scenario above (along with a query in progress that I've been working with) in the SQL fiddle link below.
Thanks greatly stackoverflow community.
> The above problem in SqlFiddle can be found here <
解决方案
Here is the query I've been working on. However, the results are slighly different from the ones you've posted in your question:
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 | +-------------+-------------+------------+
Let me know if this did the trick.
这篇关于MySQL如何创建此子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
