mysql - 在子查询中查找差异结果​​在 mysql 错误中返回超过 1 行

Question

我有一个名为 tbl_populations 的表，其中包含以下字段:

pk_populationid,residence,value,aspect,gender,fk_tbl_states_stateid

我正在尝试计算两个方面的行差异

例如

SELECT fk_tbl_states_stateid, value - (
SELECT value
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND gender = '0'
AND aspect = '2' ) AS difference
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND gender = '0'
AND aspect = '1'

它工作正常，因为它返回一行

为了获取多个数据，即我想检索所有性别值，我已从条件中删除了性别。

SELECT fk_tbl_states_stateid,gender, value - (
SELECT value
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND aspect = '2' ) AS difference
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND aspect = '1'  

我收到子查询返回超过 1 行错误。

如何获得所有结果？

Answer 1

SELECT Table11.fk_tbl_states_stateid,Table1.Gender,Table1.value - table2.Value as Diff
From
(SELECT *
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND aspect = '2' ) Table1,
(SELECT value,Gender
FROM tbl_populations
WHERE fk_tbl_states_stateid = '16'
AND fk_tbl_districts_districtid = '0'
AND residence = '0'
AND aspect = '1' ) Table2
Where Table1.Gender = Table2.Gender