英文:
Creating a SQL command for unique items in one column, but similar items in another?
问题
我SQL知识有点有限，不过我想要承认的是，我试图查询一个单独的表。在那个表中，我想要返回并分组具有相同“名称”（列）的项目，但仅在它们的“地址”（列）不匹配时。我一直在尝试查找现有的SO问题，因为我相信有人面临过这个问题，但我的搜索没有产生良好的结果。
期望的结果是，我可以返回“Tom”的结果，因为他有不止一个地址，而且它们不匹配。
英文:
My SQL knowledge is a bit more limited than I'd like to admit. What I'm attempting to do is query a single table. In that table I want to return and group items with the same "name" (column), but only if their "address" (column) DOESN'T match. I have been trying to find existing SO questions, because I'm sure someone has faced this same issue, but my searches haven't yielded good results.
ID Name Address
--- --------- ------------
1 Tom 123 Fake St.
2 Paul 81 Second Ave.
3 Mark 1001 Market St.
4 Tom 123 Fake St.
5 Tom 903 Castle St.
6 Pete 14 Circle Dr.
The expectation would be that I could return the results for "Tom" for both of his addresses, because he has more than 1, and because they don't match.
ID Name Address
--- --------- ------------
1 Tom 123 Fake St.
4 Tom 123 Fake St.
5 Tom 903 Castle St.
答案1
得分: 1
这将为您提供具有不同地址的名称列表。
select name
from (
select name, count(distinct address) as c
from table_you_did_not_name
group by name
) x
where x.c > 1
这将为您提供您所请求的结果。
select *
from table_you_did_not_name
where name in (
select name
from (
select name, count(distinct address) as c
from table_you_did_not_name
group by name
) x
where x.c > 1
)
英文:
This will give you a list of names with different address
select name
from (
select name, count(distinct address) as c
from table_you_did_not_name
group by name
) x
where x.c > 1
This will give you the results you asked for
select *
from table_you_did_not_name
where name in (
select name
from (
select name, count(distinct address) as c
from table_you_did_not_name
group by name
) x
where x.c > 1
)
答案2
得分: 0
您可以使用相关子查询来计算条目的数量。
SELECT
DISTINCT `Name`
FROM address a1
WHERE (SELECT COUNT(*) FROM address a2 WHERE a1.name = a2.name) > 1
AND (SELECT COUNT(DISTINCT `Address`) FROM address a2 WHERE a1.name = a2.name) > 1
或者使用MySQL 8版本的查询：
WITH CTE as
(SELECT name,
COUNT(*) count_,
COUNT(DISTINCT `Address`) count_a
FROM address
GROUP BY name)
SELECT
DISTINCT `Name`
FROM CTE WHERE count_ > 1 AND count_a > 1
英文:
You can use correlated subqueries to caclulate the umber of entires
SELECT
DISTINCT `Name`
FROM address a1
WHERE (SELECT COUNT(*) FROM address a2 WHERE a1.name = a2.name) > 1
AND (SELECT COUNT(DISTINCT `Address`) FROM address a2 WHERE a1.name = a2.name) > 1
| Name |
|---|
| Tom |
Or a MySql 8 Version of that
WITH CTE as
(SELECT name,
COUNT(*) count_,
COUNT(DISTINCT `Address`) count_a
FROM address
GROUP By name)
SELECT
DISTINCT `Name`
FROM CTE WHERE count_ > 1 AND count_a > 1
| Name |
|---|
| Tom |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论