在 SQL 语句中重用表达式
我正在使用 MySQL,并且我正在疯狂地尝试简化有点复杂的 SQL 语句。
查询是这样的:
SELECT `provider`.*,`products`.`placement`,`price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0') AS `price`
FROM `provider`
LEFT JOIN `products` ON `provider`.`id`=`products`.`web`
LEFT JOIN `price` ON `price`.`handle`=`provider`.`id`
WHERE `products`.`type`='$PRODUCT_TYPE'
AND `price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')>0
这个查询工作完美,但问题是我有一个重复的项目,我不知道如何简化它。我正在谈论的重复项目是:
`price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')
有什么想法可以简化它吗?谢谢
I'm using MySQL and I'm going mad trying to simplify a bit complex SQL sentence.
The query is this:
SELECT `provider`.*,`products`.`placement`,`price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0') AS `price`
FROM `provider`
LEFT JOIN `products` ON `provider`.`id`=`products`.`web`
LEFT JOIN `price` ON `price`.`handle`=`provider`.`id`
WHERE `products`.`type`='$PRODUCT_TYPE'
AND `price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')>0
This query is working perfect, but the problem is that I have a repeated item and I don't know how to simplify it. The repeated item I'm talking is:
`price`.`price`+
IFNULL((SELECT `price` FROM `price` WHERE `handle`=
(SELECT `group` FROM `group_provider` WHERE `provider_id`=`provider`.`id`)),'0')
Any idea to simplify it? Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
创建包含额外列的数据视图并查询该视图。创建视图后,查询就变得非常简单:
Create a view of your data that includes the extra column and query this view. Once you have created the view the query becomes very simple:
SELECT
在WHERE
子句之后求值,因此SELECT
中定义的列别名不可在WHERE
中使用>。MySQL 的不同寻常之处在于,它允许您在
having
子句中使用列别名,因此您可以重新设计查询,将谓词从where
子句移动到having
子句中。 >having 子句。或者,您可以使用以下定义创建视图或派生表
,并根据 Mark 的回答引用
SELECT
中的WHERE
子句中的列别名。我对 MySQL 查询优化器还不够了解,不知道这些方法是否会对性能产生影响。The
SELECT
is evaluated after theWHERE
clause so column aliases defined in theSELECT
are not available to use in theWHERE
.MySQL is unusual in that it allows you to use column aliases in the
having
clause though so you could possibly rework the query to move the predicate from thewhere
clause to ahaving
clause.Alternatively you could create a view or derived table with the following definition
and reference the column aliases in the
WHERE
clause in aSELECT
from that as per Mark's answer. I'm not sufficiently au fait with the MySQL query optimiser to know if either of these approaches would have performance implications.试试这个,它应该有效。
首先,您应该创建视图
为该视图创建查询选择
这只有在您使用 InnoDB 时才有效。
Try this, it should work.
First, you should create view
Create query select for that view
This only work, if you use InnoDB.