SQL查询后删除重复的列 [英] Remove duplicate column after SQL query

问题描述

我有这个查询，但是我得到了houseid的两列:

I have this query but I'm getting two columns of houseid:

我怎么只能得到一个?

How do I only get one?

SELECT vehv2pub.houseid, vehv2pub.vehid, vehv2pub.epatmpg, 
       dayv2pub.houseid, dayv2pub.trpmiles
FROM vehv2pub, dayv2pub
WHERE vehv2pub.vehid >= 1
      AND dayv2pub.trpmiles < 15
      AND dayv2pub.houseid = vehv2pub.houseid;

而且，如何获得epatmpg的平均值?那么查询将只返回值吗?

And also, how do I get the average of the epatmpg? So the query would just return the value?

推荐答案

最优雅的方法是使用

The most elegant way would be to use the USING clause in an explicit join condition:

SELECT houseid, v.vehid, v.epatmpg, d.houseid, d.trpmiles
FROM   vehv2pub v
JOIN   dayv2pub d USING (houseid)
WHERE  v.vehid >= 1
AND    d.trpmiles < 15;

这样，即使您使用SELECT *，列houseid也仅在结果中一次.

This way, the column houseid is in the result only once, even if you use SELECT *.

每个文档:

USING是一种简写形式:使用逗号分隔的列表 列名，这是联接表必须具有的共同名称，并形成一个 连接条件，指定每对这些列对的相等性. 此外，对于每个 等价于成对的输入列，然后是每个表中其余的列.

USING is a shorthand notation: it takes a comma-separated list of column names, which the joined tables must have in common, and forms a join condition specifying equality of each of these pairs of columns. Furthermore, the output of JOIN USING has one column for each of the equated pairs of input columns, followed by the remaining columns from each table.

要获取所选行的平均值epatmpg:

To get the average epatmpg for the selected rows:

SELECT avg(v.epatmpg) AS avg_epatmpg
FROM   vehv2pub v
JOIN   dayv2pub d USING (houseid)
WHERE  v.vehid >= 1
AND    d.trpmiles < 15;

如果dayv2pub中存在多个匹配项，则派生表可以在联接后在vehv2pub中的每一行中保存多个实例. avg()基于派生表.

If there are multiple matches in dayv2pub, the derived table can hold multiple instances of each row in vehv2pub after the join. avg() is based on the derived table.

这篇关于SQL查询后删除重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！