同表比较选择(mysql)

inn6fuwd 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(487)

我是mysql的新手，我试着做一个简单的选择，但我想不出来。
这就是我所拥有的：

I got this in a Table named Control:
| CODE | OFFICE |
|  1   |   usa  |
|  2   |   usa  |
|  3   |   usa  |
|  4   |   usa  |
|  5   |   usa  |
|  1   | china  |
|  3   | china  |
|  4   | china  |

And I need get this:

| CODE | OFFICE |
|  2   |   usa  |
|  5   |   usa  |

然后，选择code，office where the code still not registered with office=china。
我必须做一个self-join或者类似的东西，或者使用group-by语句？我被困在这里。。。我真的很感激任何帮助。

mysql select statements

来源：https://stackoverflow.com/questions/53310244/comparative-select-on-the-same-table-mysql

2条答案

按热度按时间

sqougxex1#

我想这是可行的

create table Test(id integer, code integer, office varchar(100));

insert into Test(id, code, office) values(1, 1, "usa"),(2, 2, "usa"),(3, 3, "usa"),(4, 4, "usa"),(5, 5, "usa"),(6, 1, "china"),(7, 3, "china"),(8, 4, "china");

select * from Test;

Select * from Test where code NOT IN (select code from Test where office =   "china");

您必须使用子查询。

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

niwlg2el2#

您可以在上执行“自左联接” Code ，并仅考虑在右侧未找到匹配项的行，即右侧值 IS NULL ```
SELECT
tleft.*
FROM Control AS tleft
LEFT JOIN Control AS tright
ON tright.Code = tleft.Code AND
tright.Office = 'china'
WHERE tleft.Office = 'usa' AND
tright.Code IS NULL -- this filters no matching code found in china

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

我来回答

同表比较选择(mysql)

2条答案

相关问题

热门标签

最新问答