从基于另一个数组的数组中删除行
从基于另一个数组的数组中删除行
提问于 2018-10-15 07:04:49
我完全被难住了，我尝试了我找到的不同的解决方案，但都没有奏效。这就是我的问题：
我有两个结构完全不同的表。一个表用于用户，另一个表用于块。目标是从用户搜索中删除显示在BLOCKS表中的所有项目。
我曾尝试通过使用NOT IN的SQL查询来实现这一点，但它只排除了其中一项。
我也尝试过在PHP中使用array_diff，但我仍然在用户搜索结果中看到被屏蔽的用户。
我不知道表结构是否必须匹配，或者我的SQL是否太复杂。有什么建议吗？
表结构
> +----------+---------+-----------+
|username |zipcode |birthdate |
+----------+---------+-----------+
|tester55 |72758 |1999-09-09 |
+----------+---------+-----------+
|tester86 |60608 |1983-05-10 |
+----------+---------+-----------+
|iosuser1 |10011 |1975-12-19 |
+----------+---------+-----------+
|iosuser5 |10011 |1975-12-21 |
+----------+---------+-----------+
|tester150 |10511 |1975-12-21 |
+----------+---------+-----------+
Blocks table
+----------+---------+-----------+
|blocker |blockeduser
+----------+---------+-----------+
|tester86 |tester55 | |
+----------+---------+-----------+
|iosuser5 |tester55 | |
+----------+---------+-----------+
|tester150 |tester55 | |
+----------+---------+-----------+
Zip Code table
+----------+---------+-----------+
|zipcode |city
+----------+---------+-----------+
|72758 |Rogers | |
+----------+---------+-----------+
|60608 |Chicago | |
+----------+---------+-----------+编辑:根据@TomC的反馈更新查询
SELECT
*
FROM
(
SELECT
zipcodes.zip,
zipcodes.city,
zipcodes.state,
users.id,
users.username,
users.ava,
users.gender,
users.race,
users.headline,
users.marital,
users.height,
users.bodytype,
users.religion,
users.education,
users.occupation,
users.politics,
users.kids,
users.wantkids,
users.favdrink,
users.drink,
users.smoke,
users.interests,
users.aboutme,
users.seekingGender,
users.seekingdistance,
users.seekingrace,
users.seekingmarital,
users.seekingminage,
users.seekingmaxage,
users.seekingminheight,
users.seekingmaxheight,
users.seekingbodytype,
users.seekingreligion,
users.seekingeducation,
users.seekingoccupation,
users.seekingpolitics,
users.seekingkids,
users.seekingwantkids,
users.seekingdrink,
users.seekingsmoke,
users.birthdate,
YEAR(CURRENT_TIMESTAMP) - YEAR(users.birthdate) -(
RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(users.birthdate, 5)
) AS age,
3959 * ACOS(
COS(RADIANS(zipcodes.latitude)) * COS(RADIANS(center.latitude)) * COS(
RADIANS(zipcodes.longitude) - RADIANS(center.longitude)
) + SIN(RADIANS(zipcodes.latitude)) * SIN(RADIANS(center.latitude))
) AS distance
FROM
users AS seeker
JOIN zipcodes AS center
ON
center.zip = seeker.zip
JOIN users ON seeker.zip = users.zip
JOIN zipcodes ON zipcodes.zip = users.zip
WHERE
seeker.username = 'tester55' AND seeker.username <> users.username AND users.gender = seeker.seekingGender AND seeker.gender = users.seekingGender AND users.seekingmarital LIKE seeker.seekingmarital AND users.bodytype LIKE seeker.seekingbodytype AND users.religion LIKE seeker.seekingreligion AND users.education LIKE seeker.seekingeducation AND users.occupation LIKE seeker.seekingoccupation AND users.politics LIKE seeker.seekingpolitics AND users.kids LIKE seeker.seekingkids AND users.wantkids LIKE seeker.seekingwantkids AND users.drink LIKE seeker.seekingdrink AND users.smoke LIKE seeker.seekingsmoke AND users.race LIKE seeker.seekingrace AND seeker.seekingminheight <= users.height AND seeker.seekingmaxheight >= users.height AND users.birthdate >= DATE_SUB(
NOW(), INTERVAL seeker.seekingmaxage YEAR) AND users.birthdate <= DATE_SUB(
NOW(), INTERVAL seeker.seekingminage YEAR) AND NOT EXISTS(
SELECT
*
FROM
blocks
WHERE
where blocks.blockeduser=seeker.username and blocks.blocker=users.username
)
) selections
WHERE
distance < selections.seekingdistance
ORDER BY
distance回答 2
Stack Overflow用户
回答已采纳
发布于 2018-10-15 07:38:02
您没有必要声明这些表具有完全不同的结构，这就是数据库的全部思想。您可以按照@EmaniAzevedo的建议使用left join，但是您需要根据您的要求检查两个列，否则您将使用not exists。
我更喜欢not exists，因为我认为它更清晰。
select * from users
where not exists(
select * from blocks where Users.username = Blocks.blocker or users.username=Blocks.blockeduser
)编辑:由于您现在已经添加了您的查询，this not exists应该简单地作为一个附加子句，并使用一个AND来添加子句。我也不明白你为什么要用HAVING，我认为那也应该是一个AND。
第二次编辑:这是去掉令人困惑的括号后的整个查询。现在它从查找的用户开始，查找同一zip中的所有其他用户，然后查找所有其他匹配的用户。可以对别名用户表执行此操作，而不必使用嵌套查询。
顺便说一句，它检查用户是否在拦截器表的任何一侧-如果这是不正确的，请相应地修改。
select * from (
SELECT zipcodes.zip, zipcodes.city, zipcodes.state,
users.id, users.username, users.ava, users.gender, users.race, users.headline, users.marital, users.height, users.bodytype, users.religion, users.education,
users.occupation, users.politics, users.kids, users.wantkids, users.favdrink, users.drink, users.smoke, users.interests, users.aboutme, users.seekingGender,
users.seekingdistance, users.seekingrace, users.seekingmarital, users.seekingminage, users.seekingmaxage, users.seekingminheight, users.seekingmaxheight,
users.seekingbodytype, users.seekingreligion, users.seekingeducation, users.seekingoccupation, users.seekingpolitics, users.seekingkids, users.seekingwantkids,
users.seekingdrink, users.seekingsmoke, users.birthdate,
YEAR(CURRENT_TIMESTAMP) - YEAR(users.birthdate) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(users.birthdate, 5)) as age,
3959 * acos(cos(radians(zipcodes.latitude)) *
cos(radians(center.latitude)) *
cos(radians(zipcodes.longitude ) -
radians(center.longitude)) +
sin(radians(zipcodes.latitude)) *
sin(radians(center.latitude))) AS distance
from users as seeker
join zipcodes as centre on centre.zip=seeker.zip
JOIN users ON seeker.zip = users.zip
join zipcodes on zipcodes.zip=users.zip
where seeker.username = 'tester55' and seeker.username<>users.username AND users.gender = seeker.seekingGender AND seeker.gender=users.seekingGender
AND users.seekingmarital LIKE seeker.seekingmarital AND users.bodytype LIKE seeker.seekingbodytype AND users.religion LIKE seeker.seekingreligion
AND users.education LIKE seeker.seekingducation and users.occupation LIKE seeker.seekingoccupation AND users.politics LIKE seeker.seekingpolitics
AND users.kids LIKE seeker.seekingkids AND users.wantkids LIKE seeker.seekingwantkids AND users.drink LIKE seeker.seekingdrinker
AND users.smoke LIKE seeker.seekingsmoker AND users.race LIKE seeker.seekingrace AND seeker.seekingminheight <= users.height AND seeker.seekingmaxheight >= users.height
AND users.birthdate >= DATE_SUB(NOW(), INTERVAL seeker.seekingmaxage YEAR) AND users.birthdate <= DATE_SUB(NOW(), INTERVAL seeker.seekingminage YEAR)
and not exists(
select * from blocks where blocks.blocker=users.username and blocks.blockeduser=seeker.username
)
) selections
where distance < selections.seekingdistance
ORDER BY distance编辑:使用原始样本数据，包括表的defs和insert，是上述代码的一个真正简化的版本。我将您的所有示例用户更新为相同的zip，并将其用作唯一的标准。如果这对你不起作用，请确保你被阻止的用户实际上是正确匹配的--例如，没有尾随空格。
create table users (username varchar(20),zipcode varchar(10),birthdate date)
insert users values ('tester55','10011','1999-09-09')
,('tester86','10011','1983-05-10')
,('iosuser1','10011','1975-12-19')
,('iosuser5','10011','1975-12-21')
,('tester150','10011','1975-12-21')
create table Blocks(blocker varchar(20),blockeduser varchar(20))
insert Blocks values ('tester86','tester55'),('iosuser5','tester55'),('tester150','tester55')
create table ZipCode(zipcode varchar(10), city varchar(20))
insert zipcode values ('72758','Rogers'),('60608','Chicago')
select users.*
from users seeker
join users on users.zipcode=seeker.zipcode and users.username<>seeker.username
where seeker.username='tester55'
and not exists(select * from blocks where blocks.blocker=users.username and blocks.blockeduser=seeker.username)结果：(已排除所有屏蔽tester55的用户)
username zipcode birthdate
iosuser1 10011 1975-12-19Stack Overflow用户
发布于 2018-10-15 07:21:23
假设这个结构是正确的：
MariaDB [test]> SELECT * FROM `Users`;
+----------+---------+------------+
| username | zipcode | birthdate |
+----------+---------+------------+
| tester55 | 72758 | 1999-09-09 |
| tester86 | 60608 | 1983-05-10 |
| iosuser1 | 10011 | 1975-12-19 |
| iosuser5 | 10011 | 1975-12-21 |
+----------+---------+------------+
4 rows in set (0.00 sec)和
MariaDB [test]> SELECT * FROM `Blocks`;
+----------+-------------+
| blocker | blockeduser |
+----------+-------------+
| tester86 | tester55 |
| iosuser5 | tester55 |
+----------+-------------+
2 rows in set (0.00 sec)您可以使用以下查询：
MariaDB [test]> SELECT `Users`.* FROM `Users` LEFT JOIN `Blocks` ON `Users`.`username` = `Blocks`.`blocker` WHERE `Blocks`.`blocker` IS NULL;
+----------+---------+------------+
| username | zipcode | birthdate |
+----------+---------+------------+
| tester55 | 72758 | 1999-09-09 |
| iosuser1 | 10011 | 1975-12-19 |
+----------+---------+------------+
2 rows in set (0.00 sec)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52807840
复制相关文章
相似问题
腾讯云开发者
