如何使用SQL SELECT作为字段名

Question

我试图使用select作为列名来存储get total成员，但是我没有成功地获得正确的sql语句的解决方案。这是我的数据库表结构和我使用的sql。

Sql

SELECT 
name, 
SELECT
(
(COALESCE(SUM(adult),0) + COALESCE(SUM(elders),0))
) as total 
FROM members 
LEFT JOIN company 
ON company.companyId = members.companyId
GROUP BY company.companyId

我的表

company table
+----------------------+
| companyId |  Name    |
+-----------+----------+
|  1        | Company1 |
|  2        | Company2 |
+-----------+----------+
members table
+--------------------------------------------+
|  id  | companyId | gender | adult | elders |
+------+-----------+--------+-------+--------+
|   1  |     1     |  male  |  200  |   700  |                                      
|   1  |     1     | female |  300  |    50  |                                      
|   1  |     2     |  male  |  100  |   500  |                                      
|   1  |     2     | female |  900  |   800  |                                      
+------+-----------+--------+-------+--------+
expected Results table
+-------------------------------------------------+
| companyId |  Name    | adult | elders | Total   |
+-----------+----------+-------+--------+---------+
|  1        | Company1 | 500   |  750   |  1250   |
|  1        | Company2 | 1000  |  1300  |  2300   |
+-----------+----------+-------+--------+---------+

Thanks in advance

Answer 1

从members开始的LEFT JOIN是不合适的，除非成员可能缺乏公司。我就用普通的JOIN。

则需要算术聚合:

SELECT c.companyId, c.name,
SUM(m.adults + m.elders) as total,
SUM(m.adults) as adults, SUM(m.elders) as elders
FROM members m JOIN 
company c
ON c.companyId = m.companyId
GROUP BY c.companyId, c.name;

如果下列条件之一为真，并且希望避免过滤出行，则可以使用LEFT JOIN:

• 部分成员没有有效的companyId.
• 部分公司members没有行