我有下表。
PBID | PBName |pdivID
-----------------------
1 | Jaya PB | 1
2 | SP PB | 1
表:PollingDivision-pdID是主键。
----------------
pdID |pdName
------------------
1 | Homagama
2 | Maharagama
表格:ElectionOfficial -EoId是主键。
----------------
EoID |EoName
---------------
1 | Sam
2 | Ram
3 | Feston
表格:ElectelecID是主键。
--------------------------------------
elecID | ElecName
--------------------------------------
1 | Presidential Election 2013
2 | General Election 2014
表格:PollingBoothElection-pbID和elecID列均构成主键
--------------------------------
pbID | elecID |numofEOs
--------------------------------
1 | 1 | 2
2 | 1 | 3
表:PollingBoothElectionOfficial。所有三列组合在一起构成一个主键。
----------------------------------
pbID | elecID | eOfficialID
----------------------------------
1 | 1 |1
1 | 1 |2
我想选择一个特定PollingDivision的所有PollingBooth名称,其中特定选举中每个PollingBooth的eoID数量少于特定选举所允许的选举官员人数。
换句话说,我想选择所有PollingBooth名称,其中给定pbID和elecID的PollingBoothElectionofficial表中所有eOfficialID的计数都小于相同eleID和pbID的PollingBoothElection的EOs值,我想这样做特定PollingDivision的所有投票站。
这是我尝试过的。
SELECT PB.PBName, COUNT(PBEO.eOfficialID)
FROM PollingBoothElection PBE
INNER JOIN PollingBooth PB ON PBE.pbID=PB.PBID
INNER JOIN PollingDivision PD ON PB.pdivID=PD.pdID
INNER JOIN PollingBoothElectionOfficial PBEO ON PBE.elecID=PBEO.elecID
AND PBE.pbID=PBEO.pboothID
WHERE PBE.elecID=1 AND PD.pdName='Homagama' AND PBE.numOfEO>(SELECT COUNT(PBEO.eOfficialID))
GROUP BY PB.PBName;
但这说我不能在where子句中包含聚合函数。因此,如何检查PollingBoothElectionOfficial表中特定选举的每个投票站的选举官员人数,不大于PollingBoothElection表中指定的允许选举官员人数。
有什么方法可以使用IF ELSE语句来实现?
任何帮助都会很棒。提前致谢。
如错误所述,您不能使用aggregate
function inwhere
子句来过滤结果集。
将条件从Where clause
移至Having Clause
用于搜索agroup
或an的条件aggregate
SELECT PB.PBName,
Count(PBEO.eOfficialID)
FROM PollingBoothElection PBE
INNER JOIN PollingBooth PB
ON PBE.pbID = PB.PBID
INNER JOIN PollingDivision PD
ON PB.pdivID = PD.pdID
INNER JOIN PollingBoothElectionOfficial PBEO
ON PBE.elecID = PBEO.elecID
AND PBE.pbID = PBEO.pboothID
WHERE PBE.elecID = 1
AND PD.pdName = 'Homagama'
GROUP BY PB.PBName,PBE.numOfEO
HAVING Count(PBEO.eOfficialID) < PBE.numOfEO
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句