如何一起查询我的两个表。 [英] How to query my two tables together.

问题描述

我有一张名为门票的桌子



id姓名PartId

1棒3

2 wonder 2

3棒2





和另一张名为

的表格表< br $>


partId金额

1 300

2 500

3 600
4 700



上表是我的表格外观的演示。通过查询这两张桌子我想要达到的目的是这个





名称总计TotalAmount

很棒2 1100 < br $>




注意：我的总金额是通过将PartId2和3的金额加在一起得到的。我希望这能很好地解释我的挑战。我如何使用sql实现这一点？



我尝试过：



i试图在我的查询中分组但没有得到理想的结果

i have a table called ticket

id Name PartId
1 great 3
2 wonder 2
3 great 2


and another table called
Table part

partId Amount
1 300
2 500
3 600
4 700

The table above is a demo of how my table looks like. What i want to achieve by querying both table is this


Name total TotalAmount
Great 2 1100


Note: My total amount is gotten by adding the amount for PartId "2 and 3" together. I hope this explains my challenge very well. How do i acheive this using sql?

What I have tried:

i have tried to group By in my query but not getting desired result

推荐答案

你需要使用GROUP BY和JOIN来获取信息：

You need to use a GROUP BY and a JOIN to get the info:

SELECT t.Name, SUM(p.Amount) FROM Ticket t
JOIN Part p ON t.PartId = p.partId
GROUP BY t.Name

选择tk.Name，count（tk.Name）为total，sum（amount）totalamount from @ticket tk

加入@Part pt on tk.partid = pt.partid

group by tk.Name;

select tk.Name,count(tk.Name) as total,sum(amount) totalamount from @ticket tk
join @Part pt on tk.partid=pt.partid
group by tk.Name;

这篇关于如何一起查询我的两个表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！