sql查询有两个表，需要在其中一个表中搜索特定的和多个列值

zpqajqem 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(280)

我需要编写一个包含下面两个示例表的复杂查询，我正在努力理解如何正确构造查询。
表格结构如下，并附有示例数据：

Table 1:
ID | Type | Size
A123 | Block | Medium
C368 | Square | Large
X634 | Triangle | Small
K623 | Square | Small

Table 2:
ID | Code | Description | Price
A123 | C06 | Sensitive Material | 99.99
A123 | H66 | Heavy Grade | 12.76
A123 | U74 | Pink Hue | 299.99

C368 | H66 | Heavy Grade | 12.76
C368 | G66 | Green Hue | 499.99
C368 | C06 | Sensitive Material | 99.99
C368 | K79 | Clear Glass | 59.99

X634 | G66 | Green Hue | 499.99
X634 | K79 | Clear Glass | 59.99
X634 | Z63 | Enterprise Class | 999.99

K623 | K79 | Clear Glass | 59.99
K623 | G66 | Green Hue | 499.99
K623 | X57 | Extra Piping | 199.99

查询应基于 Type 列，然后在 ID 表2的第列。查询的目标是搜索表1中具有特定属性的所有ID Code 表2中的列组合。
对于type=square和both（code=g66和code=k79），最终输出应该是这样的表：

ID 
C368
K623

应该返回这两个id，因为它们在上面的伪查询中都有两个选项代码。
如何使用这两个表来组合这个结果？下面是我编写的两个初始查询，它们都不会产生正确的结果。如您所见，我尝试了in运算符和=/和/或运算符。
尝试1（似乎适用于一个代码，但不是>1个代码）：

select distinct ID
from
(
SELECT shapes.ID, details.code, details.description
FROM db.table2 details 
JOIN db.table1 shapes
ON details.VIN = shapes.VIN
WHERE shape.type='Square'
) src
where code IN ("G66", "K79")
-- where code = "G66" AND code = "K79" (Produces zero results)
-- where code = "G66" OR code = "K79" (Produces incorrect results)

尝试2（似乎适用于一个代码，但不是>1个代码）：

SELECT distinct ID
FROM db.table2 details
WHERE ID IN
(
    SELECT ID FROM db.table1 shapes 
    WHERE shapes.type='Square' 
) AND code IN ("G66", "K79")
-- AND code = "G66" AND code = "K79" (produces zero results)
-- AND code = "G66" OR code = "K79" (Produces incorrect results)

谢谢

sql mysql Join

来源：https://stackoverflow.com/questions/62434528/sql-query-with-two-tables-and-need-to-search-one-of-the-tables-for-specific-and

3条答案

按热度按时间

g6ll5ycj1#

每当您遇到与“i need the ids from this table where is one row that value a and another row that value b and both rows that the same id”类似的问题时，您需要选择与您的条件匹配的所有行，对它们进行分组，然后对它们进行计数，并且只使用具有匹配计数的行：

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79')
GROUP BY t2.id
HAVING COUNT(*) = 2

如果有一些虚假的结果，比如两行是g66，没有行是k79，那么这个简单的计数将失败。相反，我们可以使用min和max查看值（如果是2）：

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79')
GROUP BY t2.id
HAVING MIN(t2.code) = 'G66' AND MAX(t2.code) = 'K79'

它的工作原理是因为按字母顺序g66小于k79，所以g66将是最小的一个
如果我们有3个必须指定的值，我们可以做一些技巧，比如把所有的代码都变成一个数字，并要求求和。我将使用基数2：

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79', 'X99')
GROUP BY t2.id
HAVING SUM(CASE t2.Code WHEN 'G66' THEN 1 WHEN 'K79' THEN 2 WHEN 'X99' THEN 4 END) = 7

如果我们将它们Map到1、2和4，那么生成7的唯一方法（如果值是唯一的）就是每个值都有一个。如果可能有7个g66而其他的都没有，给出一个虚假的结果，那么我们可能必须单独计算它们：

SELECT t2.id
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
WHERE t1.type = 'square' and t2.code IN ('G66', 'K79', 'X99')
GROUP BY t2.id
HAVING 
  SUM(CASE t2.code WHEN 'G66' THEN 1 ELSE 0 END) = 1 AND
  SUM(CASE t2.code WHEN 'K79' THEN 1 ELSE 0 END) = 1 AND
  SUM(CASE t2.code WHEN 'X99' THEN 1 ELSE 0 END) = 1

赞(0）回复(0）举报 2021-07-26

pobjuy322#

我是这么想的。
用where子句过滤t1中的数据，然后连接t2，两次，每次都按条件过滤，使g66和k79在同一个表中（两个不同的连接）

Select t1.ID 
from t1 
inner join t2 as t2_G66 on t1.ID = t2_G66.ID
inner join t2 as t2_K79 on t1.ID = t2_K79.ID
where t1.Type = 'Square' and 
    t2_G66.Code = 'G66' and 
    t2_K79.Code = 'K79'

这是小提琴

赞(0）回复(0）举报 2021-07-26

qjp7pelc3#

因为表1和表2是按id关联的，所以在获取了具有我们要查找的类型的相关行之后，不能对表1中的id使用内部联接吗？
我想我们可以先做一个查询来获取所有具有相关类型的行，然后运行一个 INNER JOIN 获取具有我们关心的id的共享行。
最后，我们可以按代码列对结果进行分组？
也许像这样的办法行得通

SELECT * FROM db.table1 WHERE db.table1.type = "whatever"
INNER JOIN db.table2 ON db.table1.id = db.table2.id
GROUP BY db.table2.code HAVING COUNT(*) >= 1
AND db.table2.code IN ("code_1, code_2, code_3")

我刚刚开始sql，所以我希望这是希望！
p、我意识到我并没有涵盖你们的条件，让代码成为你们关心的代码子集的一员。所以我认为这可能管用。

赞(0）回复(0）举报 2021-07-26

我来回答

sql查询有两个表，需要在其中一个表中搜索特定的和多个列值

3条答案

相关问题

热门标签

最新问答