mysql - MYSQL:联合运算符添加“总计”行

我创建了下面的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

现在，我希望能够有另一行包含totalLeaseRent的总数。像这样：

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- 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

关于mysql - MYSQL:联合运算符添加“总计”行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47530614/