有没有方法(不使用JOIN)在2列上使用WHERE
子句(OR)IN
子查询?目前,我正在做
WHERE 'col1' IN
(
SELECT id FROM table
) OR 'col2' IN
(
SELECT id FROM table
)
我相信我可以做得更好:)。我也尝试过WHERE ('col1', 'col2') IN <subquery>
,但MySQL说:Operand should contain 2 column(s)
谢谢你的帮助。
编辑:我所说的"不加入"是指我已经准备好加入很多次了:http://pastebin.com/bRfD21W9,正如您所看到的,子查询在另一个表上。
SELECT *
FROM table
WHERE
(col_1, col_2) NOT IN
(SELECT col_1, col_2 FROM table)
这样重写代码,就可以开始了:
WHERE ('col1', 'col2') IN
(
SELECT id, id FROM table
)
我知道你不同意JOIN
,但这只是另一种方法。如果对你有用,请参阅join
和friends
。。
SELECT `timeline`.`action`, `timeline`.`data`, `timeline`.`tlupdate`,
u1.`id` AS ufrom_id, u1.`username` AS ufrom_username, u1.`firstname` AS ufrom_firstname, u1.`lastname` AS ufrom_lastname, u1.`picture` AS ufrom_picture,
u2.`id` AS uto_id, u2.`username` AS uto_username, u2.`firstname` AS uto_firstname, u2.`lastname` AS uto_lastname, u2.`picture` AS uto_picture,
m.`id` AS m_id, m.`name` AS m_name, m.`alternative_name` AS m_altname, m.`tiny_img` AS m_tiny, m.`normal_img` AS m_normal
FROM `timeline`
JOIN `users` u1 ON u1.`id` = `timeline`.`user_id_from`
JOIN `users` u2 ON u2.`id` = `timeline`.`user_id_to`
JOIN `friends` f on f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`
JOIN `movies` m ON m.`id` = `timeline`.`movie_id`;
更新:
当你使用inner join
时,你也可以这样做,以避免完整结果集的条件。
JOIN `friends` f on ((f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`) and f.idol_id = ?)
您可以使用DISTINCT
或使用GROUP BY
来获得唯一的结果。
manurajhada的答案很好。如果你仍然决定避免加入,你可以:
SELECT ... FROM (SELECT id FROM table) subquery, other_table
WHERE other_table.col1 = subquery.id OR other_table.col2 = subquery.id
这应该使MySQL使用联接缓冲区来保存子查询的结果。
select * from table1 WHERE (col1, col2) in (select subquerycol1, subquerycol2 from subquery_table where some_condition=1)
您可以尝试以下查询:
select name from user_details where groupid IN ( select g_id from user_groups ) OR cityid IN ( select c_id from user_cities );
希望得到帮助。
我也面临类似的问题
SELECT COUNT(*) FROM msg_old WHERE idfrom IN
(SELECT olduserid FROM temp_user)
OR
idto IN (SELECT olduserid FROM temp_user)
我想出了解决方案
SELECT * FROM msg_old
INNER JOIN temp_user
ON msg_old.IDFROM = temp_user.olduserid
OR msg_old.IDTO = temp_user.olduserid
是的,你可以。这很容易。您必须在子查询中选择和where子句中提到的相同数量的列。