代码之家 › 专栏 › 技术社区 › Nathan Fawcett

MYSQL:用于添加“total”行的联合运算符

union phpmyadmin mysql

Nathan Fawcett · 技术社区 · 7 年前

我创建了以下SQL查询来计算未来12个月内租赁的总收入。

SELECT DISTINCT apartment.addressLine1, lease.monthlyRent, lease.duration,  lease.roomNumber, lease.monthlyRent*lease.duration AS totalLeaseRent
    FROM `lease`
    INNER JOIN apartment on (lease.roomNumber) = (apartment.roomNumber)
    left Join tenantLease on tenantLease.leaseID = lease.leaseID
    WHERE tenantLease.live = 1 AND lease.duration <= 12

这将返回以下结果:

AddressLine1    monthlyRent Duration(months)  roomNumber    totalLeaseRent
Chlorine Gardens1200        9                 GF02          10800
May Road        800         12                GF03          9600
Beech Hill      900         8                 BG06          7200
Ash Avenue      1000        12                AA04          12000

我现在希望能够有另一行,包括总租金的总和。这样地:

AddressLine1    monthlyRent Duration(months)  roomNumber    totalLeaseRent
    Chlorine Garden1200     9                 GF02          10800
    May Road        800         12            GF03          9600
    Beech Hill      900         8             BG06          7200
    Ash Avenue      1000        12            AA04          12000
    TOTAL                                                   *total*

我尝试了以下代码,但不断收到错误代码: 1222-使用的SELECT语句具有不同的列数

SELECT DISTINCT apartment.addressLine1, lease.monthlyRent, lease.duration,  lease.roomNumber, lease.monthlyRent*lease.duration AS totalLeaseRent
FROM `lease`
INNER JOIN apartment on (lease.roomNumber) = (apartment.roomNumber)
left Join tenantLease on tenantLease.leaseID = lease.leaseID
WHERE tenantLease.live = 1 AND lease.duration <= 12

UNION ALL
SELECT 'Total', SUM(lease.monthlyRent * lease.duration)
FROM lease

如何运行此查询? 谢谢

1 回复 | 直到 7 年前

Shushil Bohara 7 年前

试试这个 :在使用set操作时,我们必须遵循以下三点

1-UNION中的每个SELECT语句必须具有相同的列数

2-列还必须具有类似的数据类型

3-每个SELECT语句中的列的顺序也必须相同

SELECT DISTINCT apartment.addressLine1, 
    lease.monthlyRent, 
    lease.duration,  
    lease.roomNumber, 
    lease.monthlyRent*lease.duration AS totalLeaseRent
FROM `lease`
INNER JOIN apartment on (lease.roomNumber) = (apartment.roomNumber)
left Join tenantLease on tenantLease.leaseID = lease.leaseID
WHERE tenantLease.live = 1 AND lease.duration <= 12
UNION ALL
SELECT 'Total', NULL, NULL, NULL, SUM(lease.monthlyRent * lease.duration)
FROM lease

推荐文章

Palendrone · MySQL使用UNION在两列上排序

2 年前

Jean · 来自不同sql表的个性化php输出

7 年前

Ksdmg · 如何返回具有串联标识符的列

7 年前

Balthasar · 联合查询中按类型统计记录

7 年前

Royal Flush · 在php中使用sql查询在web应用程序中显示报告

7 年前

kitchenprinzessin · Python-如何通过键组合两个词典

7 年前

Nathan Fawcett · MYSQL:用于添加“total”行的联合运算符

7 年前

frank · 如何基于sas中的特定列合并2个表

7 年前

Stefano Maviega · SQL-如何在一行中显示两行中具有不同值的相同字段

7 年前

Sab · SQL无法绑定多部分标识符“”。并集+连接

7 年前