如何在sql中对两列不同的表进行求和

I created two tables book and cd

我创建了两本表和CD

**Books** **Book_id** **author** **publisher** **rate** angular2 132 venkat ts 1900 angular 160 venkat ts 1500 html 5 165 henry vk 1500 html 231 henry vk 2500 css 256 mark adobe 1600 java 352 john gulberg 4500 c# 450 henry adobe 1600 jsp 451 henry vk 2500 ext js 555 kv venkat w3 5102 html 560 kv venkat gulberg 5000 java2 561 john gulberg 9500 java8 651 henry vk 1650 js 654 henry ts 2500 java 777 babbage adobe 5200 phython 842 john ts 1500 spring 852 henry w3 6230 spring 895 mark tut 4250 ext js 965 henry gulberg 4500

**书籍** ** Book_id ** **作者** **出版商** **率** angular2 132 venkat ts 1900 angular 160 venkat ts 1500 html 5 165 henry vk 1500 html 231 henry vk 2500 css 256 mark adobe 1600 java 352 john gulberg 4500 c＃450 henry adobe 1600 jsp 451 henry vk 2500 ext js 555 kv venkat w3 5102 html 560 kv venkat gulberg 5000 java2 561 john gulberg 9500 java8 651 henry vk 1650 js 654 henry ts 2500 java 777 babbage adobe 5200 phython 842 john ts 1500 spring 852 henry w3 6230 spring 895 mark tut 4250 ext js 965 henry gulberg 4500

book_id              Cd_name           Cd_price
132                  angular2          500
132                  angular1          600
132                  angular basics    600
132                  angular expert    900
160                  begineer_course   1200
160                  angular_templates 500
165                  html_tutorials    900
165                  bootstrap         1000
256                  css styles        650
256                  expert css        900
555                  extjs             1200
555                  exjs_applications 500
777                  core java         2500
777                  java swing        4500
777                  java tutorials    1500
842                  phython           650
852                  spring            900
852                  spring mvc        900

In the above two tables i want to join the books,author,cd_name and the total cost of book and cd for each id.

在上面的两个表中，我想加入书籍，作者，cd_name以及每个id的book和cd的总成本。

Expected Output

预期产出

Books       Book_id     author  cd_name       total price
angular2    132         venkat  angular2         2400
angular2    132         venkat  angular basics   2100
angular2    132         venkat  angular expert   2800
java        777         babbage core java        7700

Like the above result i need to get the total cost for all the books and cd

像上面的结果我需要得到所有书籍和CD的总成本

2 个解决方案

#1

In case not all books had a CD:

如果并非所有书籍都有CD：

SELECT A.Books
       , A.Book_ID
       , A.Author
       , B.CD_Name
       , A.rate+COALESCE(B.Cd_price,0) AS TOTAL_PRICE
FROM BOOK A
LEFT JOIN CD B ON A.BOOK_ID = B.BOOK_ID

Question's author evidenced that "The table name is book not Books"

问题的作者证明“表名是书不是书”

I used originally BOOKS as a "suggestion" because (usually) tables name are plural.

我原来使用BOOKS作为“建议”，因为（通常）表名是复数。

#2

Try this

尝试这个

select books, b.book_id, author, cd_name, (b.rate+c.cd_price) as total_price from book b
join cd c on b.book_id = c.book_id

#1