MySQL-从多个表中选择，可能没有联接? [英] MySQL - selecting from multiple tables, possibly without joins?

问题描述

自从我需要帮助已经有一段时间了，但是今天我在这里基本上是从您的知识中获得帮助.目前，我非常困扰一个非常烦人的SQL问题，如下所示.

It's been a while since I needed help, but today I'm here to basically get assistance from your knowledge. I'm currently quite stuck on a very annoying SQL problem, which is the following.

我有两个桌子. Painteditems和specialitems.两个表都有唯一的列名(painteditemid，specialitemid等)，但是两个表共享相似的值.我想从两个表中获取结果.

I have two tables. Painteditems, and specialitems. Both tables have unique column names (painteditemid, specialitemid etc), yet both tables share similar values. I want to get results from both tables.

假设这是我的设置:

PaintedItems

PaintedItems

• paintedItemName
• paintedItemColor
• 可见

SpecialItems

SpecialItems

• specialItemName
• specialItemColor
• 可见

我使用了以下查询:

SELECT *
FROM `painteditems` AS pa,
     `specialitems` AS sp
WHERE (pa.`visible` = 1
       OR sp.`visible` = 1)
  AND (pa.`painteditemname` = 'itemname1'
       OR sp.`specialitemname` = 'itemname1')
  AND (pa.`painteditemcolor` = 'black'
       OR sp.`specialitemcolor` = 'black')

结果是:

Showing rows 0 - 29 ( 259,040 total, Query took 39.4352 sec)

即使两个表总共只包含10.000行.添加此操作无济于事:

even though both tables contain only 10.000 rows altogether. Adding this did nothing:

GROUP BY pa.`painteditemid`, sp.`specialitemid`

仍然有260k行.我应该如何处理?

Still 260k rows. How should I approach this?

谢谢.

固定间距，代码块

推荐答案

肯定听起来您想要在两个表之间使用UNION.现在，您得到的是笛卡尔积，这就是为什么结果如此之大的原因:

Sure sounds like you want a UNION between the two tables. Right now, you are getting a cartesian product which is why the results are so large:

select *, 'painted' Source
from painteditems
where visible = 1
    and painteditemname = 'itemname1'
    and painteditemcolor = 'black'
union all
select *, 'special' Source
from specialitems
where visible = 1
    and specialitemname = 'itemname1'
    and specialitemcolor = 'black'

您将需要用列名称替换SELECT *.另外，两个查询中的列数和数据类型必须匹配.

You will need to replace the SELECT * with your column names. Also the number of columns and datatypes must match in both queries.

UNION ALL将返回两个表中的所有行，如果只需要DISTINCT行，则需要使用UNION

UNION ALL will return all rows from both tables, if you only want DISTINCT rows then you will want to use UNION

这篇关于MySQL-从多个表中选择，可能没有联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！