mysql - 很难在 SQL 查询中放置 2 个 WHERE

Question

我的 SQL 查询有什么问题？

它不断在 WHERE building_id=:building_id 处返回以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':building_id AND WHERE NOT EXISTS ( SELECT 1 FROM ts_roompref ' at line 5: SELECT COUNT(*) totalCount FROM ts_room WHERE building_id=:building_id AND WHERE NOT EXISTS ( SELECT 1 FROM ts_roompref JOIN ts_request ON ts_roompref.request_id = ts_request.roompref_id AND day_id = 1 AND period_id = 1 WHERE ts_room.id = ts_roompref.room_id)

这是我的代码:

SELECT 
  COUNT(*) totalCount 
FROM 
  ts_room 
WHERE building_id=:building_id AND
WHERE 
  NOT EXISTS (
    SELECT 1
    FROM ts_roompref 
      JOIN ts_request 
      ON ts_roompref.request_id = ts_request.roompref_id
      AND day_id = 1 
      AND period_id = 1
    WHERE 
      ts_room.id = ts_roompref.room_id)

我这里有一个 SQL 表 - http://sqlfiddle.com/#!2/30297/8

Answer 1

只需删除第二个 WHERE 关键字即可。

由于 :buildingid，这不会在 SQL Fiddle 中编译。

Here's a working example .