MySQL，两个表显示来自两个表的信息 [英] MySQL, two tables displaying information from both tables

问题描述

我目前正在学习 SQL，我有一个问题.我有两张桌子叫做vehicul"和proprietate.第一个表(vehicul)看起来像这样:

I am learning SQL currently and I have a question. I have two tables called "vehicul" and proprietate. The first table (vehicul) looks like this:

   id   nr_vehicul  marca   id_marca    tip     culoare     capacitate_cilindrica
    1   DJ-01-AAA   Mercedes    1       CLK 350 negru       3500
    2   DJ-01-BBB   Mercedes    1       S 500   silver      5000
    3   DJ-01-CCC   Mercedes    1       ML 550  alb         5500
    4   DJ-01-DDD   BMW         2       325     galben      2500
    5   DJ-01-EEE   BMW         2       X5      negru       350

第二个表(私有)看起来像这样:

And the second table (proprietate) looks like this:

id serie_buletin    cnp        nr_vehicul data_cumpararii pret
1   AK162332    2006036035087   DJ03AAA     2014-05-01  35000
2   AK162332    2006036035087   DJ03BBB     2014-05-02  90000
3   AK176233    6548751520125   DJ03CCC     2014-05-03  55000
4   BZ257743    6548751520125   DJ03DDD     2014-05-04  25000
5   BZ257743    2006036035087   DJ03EEE     2014-05-05  63000

我想显示第一个表中的marca"列和第二个表中的price"列，但像这样.

I want to display the column "marca" from the first table and the column "price" from the second, but like this.

 marca   |  pret
Mercedes | 180000
  BMW    |  88000

基本上我有三辆奔驰车和两辆宝马车，我怎样才能一次显示奔驰车，然后将这三辆汽车的价格汇总起来?

Basically I have three Mercedes cars and two BMW cars, how can I display Mercedes one time and the prices of those three summed up?

推荐答案

你需要根据 marca 字段和 sum pret

You need to join two tables and GROUP them based on marca field and sum pret

select marca, sum(pret)
from table1 as t1, table2 as t2
where t1.id=t2.id
group by marca

这里我假设 id 字段正在连接两个表，(但正如我从您的样本数据中看到的，它实际上并不相互关联)

Here I'm assuming that id field is joining two tables, (but as I can see from your sampel data it isn't relating to each-other actually)

编辑

我认为您缺少 table2 中的 id_marca 字段.如果它在那里，那么它将加入该列，如下例所示:

I think you are missing id_marca field in table2. If its there then it would join to that column as below example:

select marca, sum(pret)
from table1 as t1, table2 as t2
where t1.id_marca=t2.id_marca
group by id_marca;

这篇关于MySQL，两个表显示来自两个表的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！