我有一排表格，其中包含四个不同的数字字段：人，碎片，英里和包。所有这些字段现在对于每行都是0，因为还没有数据。这些行还具有两个重要字段，分别称为“ County”（县）和“ Underwater”（仅是1/0（是/否）），其值是纽约州的县之一。所以他们看起来像这样...
County Underwater People Debris Miles Bags
Richmond 0 0 0 0 0
Rockland 1 0 0 0 0
Nassau 0 0 0 0 0
Queens 1 0 0 0 0
Niagara 0 0 0 0 0
为了将所有这些分组在一起形成一个PHP页面，我使用了一个带有许多UNION的长SQL语句，并基本上按纽约市，长岛和纽约州北部各县的结果将结果分开，然后是另外两个针对水下而非非水下的查询，则总计。我认为使用一次长语句比对同一张表进行六个独立查询要有效，并且可以节省一些代码行。在所有数据均为0之前，我一直在使用测试编号，并且工作正常。现在所有数据都是0，结果集是全0的行，然后是NULL的行，仅此而已。由于我使用的是foreach PHP循环来解析数据，因此它与我的算法有些相似。我还注意到，如果仅将1行的数据（即使只有1个字段）更改为非零，那么我的结果集中将获得另一行。
谁能解释这种行为和解决方案，或者提供替代方案？如果需要，我可以更深入地介绍。
顺便说一下，这是有问题的陈述。备用墙...
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE county='The Bronx' OR county='Kings' OR county='New York' OR county='Queens' OR county='Richmond'
UNION
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE county='Nassau' OR county='Suffolk'
UNION
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE county='Albany' OR county='Allegany' OR county='Broome' OR county='Cattaraugus' OR county='Cayuga' OR county='Chautauqua' OR county='Chemung' OR county='Chenango' OR county='Clinton' OR county='Columbia' OR county='Cortland' OR county='Delaware' OR county='Dutchess' OR county='Erie' OR county='Essex' OR county='Franklin' OR county='Fulton' OR county='Genesee' OR county='Greene' OR county='Hamilton' OR county='Herkimer' OR county='Jefferson' OR county='Lewis' OR county='Livingston' OR county='Madison' OR county='Monroe' OR county='Montgomery' OR county='Niagara' OR county='Oneida' OR county='Onondaga' OR county='Ontario' OR county='Orange' OR county='Orleans' OR county='Oswego' OR county='Otsego' OR county='Putnam' OR county='Rensselaer' OR county='Rockland' OR county='Saratoga' OR county='Schenectady' OR county='Schoharie' OR county='Schuyler' OR county='Seneca' OR county='Steuben' OR county='StLawrence' OR county='Sullivan' OR county='Tioga' OR county='Tompkins' OR county='Ulster' OR county='Warren' OR county='Washington' OR county='Wayne' OR county='Westchester' OR county='Wyoming' OR county='Yates'
UNION
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE underwater = '0' AND county != ''
UNION
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE underwater = '1' AND county != ''
UNION
SELECT SUM( people ) AS people, SUM( debris ) AS debris, SUM( miles ) AS miles, SUM( bags ) AS bags
FROM `table`
WHERE county != '' AND fname != '' AND lname != ''
最佳答案
使用UNION ALL代替UNION。 UNION运算符将抛出两个结果集之间的重复行，就像SELECT DISTINCT对单个结果集所做的一样。 （或也选择县名，因为这将导致结果行包含不同的数据。）
要从查询中消除NULL行（这是空数据集上诸如SUM()的聚合函数的结果），只需执行以下操作：
SELECT * FROM (
your query here
) AS x
WHERE x.people IS NOT NULL
或者，将查询中每次出现的
SUM(x)更改为COALESCE(SUM(x), 0)。关于php - 在UNION中具有6个SELECT的SQL语句仅返回2行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7049198/