sql子查询问题

Question

我很少说英语。

我有一个 sql 子查询错误

数据库：MySQL
表类型：MyISAM

以下我的sql查询

SELECT
(SELECT sum(`total`) FROM `staff_history` WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) AS `input`,
(SELECT sum(`total`) FROM `staff_history` WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`,
(`input` - `output`) AS `balance`
FROM `staff_history` AS `table_1` WHERE `staff_id` = '2';

我收到这样的错误

Error code 1054, SQL status 42S22: Unknown column 'input' in 'field list'

你能帮我解决这个问题吗？

Answer 1

您无法使用已有的字段名称，因为它们在此范围内不可用。

您可以复制整个表达式

SELECT
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) AS `input`,
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`,
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) 
-
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `balance`
FROM `staff_history` AS `table_1` WHERE `staff_id` = '2';

查询优化器可以很好地处理这个问题，但它不太可维护，因此您也可以将整个查询放在子查询中：

SELECT
  x.`input`,
  x.`output`,
  x.`input` - x.`output` as `balance`
FROM
  (SELECT
    (SELECT sum(`total`) 
    FROM `staff_history` 
    WHERE `type` = 'Giriş' AND staff_id =  table_1.staff_id) AS `input`,
    (SELECT sum(`total`) 
    FROM `staff_history` 
    WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`
  FROM 
    `staff_history` AS `table_1` 
  WHERE `staff_id` = '2') x;

Answer 2

我提供1条SQL语句，1条表扫描：

select sum(case when type = 'Giriş' then total else 0 end) as input
      ,sum(case when type = 'Çıkış' then total else 0 end) as output
      ,sum(case when type = 'Giriş' then total else 0 end) - 
       sum(case when type = 'Çıkış' then total else 0 end) as balance
  from staff_history
 where staff_id = 2
   and type in('Giriş', 'Çıkış');