尝试编写一个MySQL连接查询

We have three tables:

我们有三个表:

p:

<`
+-----+-------+-------+--------+--------+
| pno | pname | color | weight | city   |
+-----+-------+-------+--------+--------+
| p1  | nut   | red   |     12 | London |
| p2  | bolt  | green |     17 | Paris  |
| p3  | screw | blue  |     17 | Rome   |
| p4  | screw | red   |     14 | London |
| p5  | cam   | blue  |     12 | Paris  |
| p6  | cog   | red   |     19 | London |
+-----+-------+-------+--------+--------+
`

s:


+-----+-------+--------+--------+
| sno | sname | status | city   |
+-----+-------+--------+--------+
| s1  | Smith |     20 | London |
| s2  | Jones |     10 | Paris  |
| s3  | Blake |     30 | Paris  |
| s4  | Clark |     20 | London |
| s5  | Adams |     30 | Athens |
+-----+-------+--------+--------+


sp:


+-----+-----+-----+
| sno | pno | qty |
+-----+-----+-----+
| s1  | p1  | 300 |
| s1  | p2  | 200 |
| s1  | p3  | 400 |
| s1  | p4  | 200 |
| s1  | p5  | 100 |
| s1  | p6  | 100 |
| s2  | p1  | 300 |
| s2  | p2  | 400 |
| s3  | p2  | 200 |
| s4  | p2  | 200 |
| s4  | p4  | 300 |
| s4  | p5  | 400 |
+-----+-----+-----+

What we need to accomplish: Let GRTQ be the total quantity of green and red parts of any kinds shipped by each supplier with supplier number Si. Obtain the table containing the tuples, where GRTQ > 300. List the result in the increasing order of the total quantities.

我们需要完成的是:让GRTQ是每个供应商以Si号发货的所有绿色和红色零部件的总数量。获取包含元组的表，其中GRTQ > 300。列出总数量增加的顺序。

Any bright ideas? I'll post some of my attempts shortly.

什么好主意吗?我将很快发布我的一些尝试。

2 个解决方案

#1

Here's your query:

这是你的查询:

SELECT s.sno, SUM(sp.qty) as GRTQ 
FROM s, p, sp 
WHERE s.sno = sp.sno AND p.pno = sp.pno AND (p.color = "red" OR p.color="green") 
GROUP BY s.sno, s.sname 
HAVING GRTQ > 300 
ORDER BY GRTQ ASC;

The resulting output is:
+-----+------+
| sno | GRTQ |
+-----+------+
| s4  |  500 |
| s2  |  700 |
| s1  |  800 |
+-----+------+

You can verify it by computing it yourself.

您可以通过自己计算来验证它。

i.e. the red or green parts are p1, p2, p4, p6

即红色或绿色部分是p1 p2 p4 p6。

Among these:

这些包括:

s4 supplied only p2 and p4, in quantities of 200 and 300, respectively. Total = 500

s4只提供p2和p4，数量分别为200和300。总= 500

s2 supplied only p1 and p2, in quantities of 300 and 400, respectively. Total = 700

s2仅供应p1和p2，数量分别为300和400。总= 700

s1 supplied all parts in these quantities: 300 + 200 + 200 + 100. Total = 800

s1提供了所有这些数量的零件:300 + 200 + 200 + 100。总= 800

#2

Try this:

试试这个:

SELECT
  s.sno,
  s.sname,
  SUM(sp.qty) AS `GRTQ`
FROM
  sp
INNER JOIN
  s
ON
  s.sno = sp.sno
INNER JOIN
  p
ON
  p.pno = sp.pno
WHERE
  (p.color = 'red' or p.color = 'green')
GROUP BY
  s.sno,
  s.sname
HAVING
  `GRTQ` > 300
ORDER BY
  `GRTQ` ASC

This is the output on your sample data:

这是您的样本数据的输出:

+------+-------+------+
| sno  | sname | GRTQ |
+------+-------+------+
| s1   | Smith |  800 |
| s2   | Jones | 1400 |
+------+-------+------+
2 rows in set (0.00 sec)

#1