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

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

union phpmyadmin mysql

Nathan Fawcett · 技术社区 · 8 年前

我创建了以下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 回复 | 直到 8 年前

Shushil Bohara 8 年前

试试这个 :在使用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