嵌套 MySQL 查询？

发布于 2024-10-20 19:19:57 字数 354 浏览 3 评论 0原文

我有这个运行良好的 MySQL 语句：

SELECT claim_items.item_id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id

但是我想要做的是获取 MySQL 语句中生成的 per_item 字段的总和。是否可以通过嵌套语句来做到这一点？我知道我可以创建一个视图然后执行它，但更喜欢在一个语句中执行它。

感谢您的帮助，非常感谢！

原文

I have this MySQL statement which works fine:

SELECT claim_items.item_id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id

However what I want to do is to get the sum of the per_item field generated in the MySQL statement. Is it possible to do a nested statement to do that? I know I could create a view and then do it, but would prefer to do it in one statement.

Thanks for your help, much appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

扭转时空 2024-10-27 19:19:57

select t.id, SUM(t.per_item)
from 
(
SELECT claim_items.item_id as id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id
) t
group by t.id

希望这会有所帮助。

select t.id, SUM(t.per_item)
from 
(
SELECT claim_items.item_id as id, claim_items.quantity*items_rate.rate as per_item
FROM claim_items, items_rate 
WHERE claim_items.claim_id = 1 AND claim_items.item_id = items_rate.items_id
) t
group by t.id

Hope this should help.

回复收藏 0 原文

°如果伤别离去 2024-10-27 19:19:57

如果您只想要所有行的总和，您可以这样做（使用显式连接以获得更好的可读性）：

SELECT SUM( claim_items.quantity * items_rate.rate )
FROM claim_items
JOIN items_rate ON ( items_rate.items_id = claim_items.item_id )
WHERE claim_items.claim_id = 1

If you just want the sum over all rows you can do it like that (using explicit joins for better readability):

SELECT SUM( claim_items.quantity * items_rate.rate )
FROM claim_items
JOIN items_rate ON ( items_rate.items_id = claim_items.item_id )
WHERE claim_items.claim_id = 1

回复收藏 0 原文

~没有更多了~