where存在具有内部where条件的sql？

mbyulnm0 于 2021-06-18 发布在 Mysql

关注(0)|答案(4)|浏览(264)

我有以下要求，更确切地说是sql参数化查询：

select * from `products` 
where (exists (select * from `productslocation` where `products`.`Id` = `productslocation`.`Product_Id` and `Country_Id` = ?) and 
exists (select * from `productprices` where `products`.`Id` = `productprices`.`Products_Id` and `Price` >= ?) 
and `Organization_Id` = ? and `name` like ? or `Description` like ?) and `Status` = ?

此查询仅检索存在的产品 country , price 使用运算符 WHERE EXISTS .
如果找到了它就会回来 TRUE 对于第一个子查询，则处理剩余的两个查询 WHERE . 第一个子查询返回 TRUE ，第二次给予 TRUE ，因为子查询也是正确的。 result = TRUE * TRUE = TRUE . 但这是错误的结果。
问题是，需要使用两个intern查询来查询结果的来源 WHERE EXISTS .
是不是意味着我需要更换 WHERE EXISTS 在 JOIN'S ? 或者可以修改上面的查询吗？

sql mysql

来源：https://stackoverflow.com/questions/53660701/where-exists-sql-with-internal-where-condition

4条答案

按热度按时间

ugmeyewa1#

您正在查找筛选的查询 products 在各种标准上，有些涉及到指称关系。
假设你有 1-1 与引用表的关系，您应该能够使用 JOIN 学生：

select *
from 
    `products` 
    inner join `productslocation` 
        on `products`.`Id` = `productslocation`.`Product_Id` and `productslocation`.`Country_Id` = ?
    inner join `productprices` 
        on  `products`.`Id` = `productprices`.`Products_Id` and `productprices`.`Price` >= ?
where
    `products`.`Organization_Id` = ? 
    and `products`.`name` like ? 
    and `products`.`Description` like ?
    and `products`.`Status` = ?

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

kxe2p93d2#

这样不是更好吗？试试这个：

select * from `products` inner join productprices on `products`.`Id` = `productprices`.`Products_Id`
INNER JOIN `productslocation` ON `products`.`Id` = `productslocation`.`Product_Id`
where (`Organization_Id` = ? and `name` like ? or `Description` like ?) and `Status` = ? 
AND `productprices`.`Price` >= ?
and `productslocation`.Country_Id` = ?

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

gzszwxb43#

您遗漏了or语句的括号：

select * from `products` 
where (exists (select * from `productslocation` where `products`.`Id` = `productslocation`.`Product_Id` and `Country_Id` = ?) and 
exists (select * from `productprices` where `products`.`Id` = `productprices`.`Products_Id` and `Price` >= ?) 
and `Organization_Id` = ? and (`name` like ? or `Description` like ?)) and `Status` = ?

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

arknldoa4#

你的括号和 OR s和 AND 他们的说法很复杂。
试试这个：

select * from `products` 
    where 
    (
    exists (select * from `productslocation` where `products`.`Id` = `productslocation`.`Product_Id` and `Country_Id` = ?) and 
    exists (select * from `productprices` where `products`.`Id` = `productprices`.`Products_Id` and `Price` >= ?) and 
    `Organization_Id` = ? and (`name` like ? or `Description` like ?) and `Status` = ?
    )

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

我来回答

where存在具有内部where条件的sql？

4条答案

相关问题

热门标签

最新问答