在SQL查询中缺少运算符

问题描述：

我想从MS Access查询棒球数据库，但是我的SQL语句一直卡在缺少的运算符上。下面是我通过SQL查询：在SQL查询中缺少运算符

SELECT 
    Master.nameFirst, Master.nameLast, 
    Master.nameFirst + ' ' + Master.nameLast as FULLNAME, 
    (SUM(Batting.H) + SUM(Batting.BB) + SUM(Batting.HBP))/(SUM(Batting.AB) + SUM(Batting.BB) + SUM(Batting.HBP)) as OBP, 
    SUM(Batting.H)/(SUM(Batting.H) + SUM(Batting.BB) + SUM(Batting.HBP)) as HitP, 
    (SUM(Batting.2B) + SUM(Batting.3B) + SUM(Batting.HR))/SUM(Batting.H) as ExtraP, 
    SUM(Batting.HR)/(SUM(Batting.2B) + SUM(Batting.3B) + SUM(Batting.HR)) as HRP, 
    SUM(Batting.3B)/(SUM(Batting.2B) + SUM(Batting.3B)) as TriplePerc, 
    SUM(Batting.HBP)/(SUM(Batting.BB) + SUM(Batting.HBP)) as HBPP 
FROM 
    Master 
INNER JOIN 
    Batting ON Master.playerID = Batting.playerID 
WHERE 
    (Batting.playerID IN (SELECT playerID 
          FROM Master 
          WHERE (nameFirst = 'Ryan' AND nameLast = 'Howard'))

丢失的操作错误似乎把重点放在这个单一的表达：

(SUM(Batting.2B) + SUM(Batting.3B) + SUM(Batting.HR))/SUM(Batting.H) as ExtraP

但老实说，我看不出有什么不妥的地方。我错过了什么？

为什么不通过在条件解决之前删除条件来简化查询？如果你仍然不解决问题，那就问一个问题。 –

真的是你的专栏名称吗？这应该是问题。你正在使用哪个DBMS？ –

请添加 - （1）数据库+版本的相关标签（2）相关表格的DDL（想想支持您的容易程度） –

答

而且，只是为了让一个建议，如果你使用的别名，并格式化你的SQL，你会发现你的代码更容易阅读，理解和调试：

SELECT m.nameFirst, m.nameLast, 
    m.nameFirst + ' ' + m.nameLast FULLNAME, 
    (SUM(b.H) + SUM(b.BB) + SUM(b.HBP))/
    (SUM(b.AB) + SUM(b.BB) + SUM(b.HBP)) OBP, 
    SUM(b.H)/(SUM(b.H) + SUM(b.BB) + SUM(b.HBP)) HitP, 
    (SUM(b.2B) + SUM(b.3B) + SUM(b.HR))/SUM(b.H) ExtraP, 
    SUM(b.HR)/(SUM(b.2B) + SUM(b.3B) + SUM(b.HR)) HRP, 
    SUM(b.3B)/(SUM(b.2B) + SUM(b.3B)) TriplePerc, 
    SUM(b.HBP)/(SUM(b.BB) + SUM(b.HBP)) HBPP 
FROM Master m JOIN Batting b 
    ON b.playerID = m.playerID 
    and b.playerId IN 
     (SELECT playerID FROM Master 
     WHERE(nameFirst = 'Ryan' 
      AND nameLast = 'Howard')

它是C＃字符串的一部分。对穷人格式化抱歉！ – jDave1984

我认为你错过了所有'AS xxx'的计算？看看'SUM（b.3B））TriplePerc，' –

这些“as”在SQL中是可选的。 –

答

这是四线，如：

SUM(Batting.H)/(SUM(Batting.H) + SUM(Batting.BB) + SUM(Batting.HBP)) as HitP,

应该是：

SUM(Batting.H)/((SUM(Batting.H) + SUM(Batting.BB) + SUM(Batting.HBP)) as HitP,

或：

SUM(Batting.H)/(SUM(Batting.H) + SUM(Batting.BB) + SUM(Batting.HBP) as HitP,

编辑

噢，我的，那些都错了。这是丢失的右括号 - 或者说省略掉第一个左括号：

WHERE 
    (Batting.playerID IN (SELECT playerID 
          FROM Master 
          WHERE (nameFirst = 'Ryan' AND nameLast = 'Howard'))

也许我已经为足球做好准备了，但是你确定'应该'的语法吗？（）对现在看起来不平衡？ –

嗯，我不知道计算的目的是什么，所以第一个_将H与（H + BB + HBP）分开，而第二个_与H分开H，然后加上BB + HBP_。但最后一个没有什么意义，我现在看到了。所以“应该”似乎对我来说是正确的。 – Gustav

我做老'保持parens计数，为每个'加'1（'和减去1'）。希望以零结束。我得到一个额外的'（'与'你应该'和'或'：'我错过了什么？:) –

在SQL查询中缺少运算符

相关推荐