从具有相同列的两个表得到的连接结果，避免了重复

mwngjboj 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(323)

我有两个不同数据的表，我想连接结果，以便从这两个表中获取数据，避免重复。
表1：

Seasons  | Colors
win 17   | grey
win 17   | blue
win 18   | red
win 18   | black

表2：

Seasons  | Sizes | Weights
win 17   | 48    |   8
win 17   | 47    |   7
win 17   | 48    |   5
win 17   | 47    |   9
win 18   | 42    |   2
win 18   | 43    |   3
win 18   | 42    |   4
win 18   | 43    |   1

加入类似于：

Seasons  | Sizes | Weights | Colors
win 17   | 48    |   8    |  grey
win 17   | 47    |   7    |  grey
win 17   | 48    |   5    |  grey
win 17   | 47    |   9    |  grey
win 17   | 48    |   8    |  blue
win 17   | 47    |   7    |  blue
win 17   | 48    |   5    |  blue
win 17   | 47    |   9    |  blue
win 18   | 42    |   2    |  red
win 18   | 43    |   3    |  red
win 18   | 42    |   4    |  red
win 18   | 43    |   1    |  red
win 18   | 42    |   2    |  black
win 18   | 43    |   3    |  black
win 18   | 42    |   4    |  black
win 18   | 43    |   1    |  black

我试过了

SELECT
  Seasons,
  Sizes,
  Weights,
  Colors
FROM (SELECT
  Seasons,
  Colors
FROM Table1) c
CROSS JOIN (SELECT
  Sizes,
  Weights
FROM table2) s
WHERE c.Seasons = s.Seasons
GROUP BY Seasons,
         Colors,
         Sizes,
         Weights;

但我在同一季节有不同尺寸和代码的重复颜色

sql mysql

来源：https://stackoverflow.com/questions/48093384/join-result-from-two-tables-having-same-colomn-avoiding-duplicate

1条答案

按热度按时间

bt1cpqcv1#

试试这个：你可以试试 JOIN 与 DISTINCT 但我不明白你为什么要用这样一种方式来存储数据，你可以用更好的方式来规范它。

SELECT DISTINCT t1.Seasons, 
    t2.Sizes,
    t2.Weights,
    t1.Colors
FROM table1 t1
INNER JOIN table2 t2 ON t1.Seasons = t2.Seasons

赞(0）回复(0）举报 2021-06-21

我来回答

从具有相同列的两个表得到的连接结果，避免了重复

1条答案

相关问题

热门标签

最新问答