mysql - SQL 查询产生错误的计数结果

Question

我有以下 SQL 查询

SELECT
  DISTINCT
  count("SiteTree_Live"."ID") 
FROM  
  "SiteTree_Live"
  LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
  LEFT JOIN "TourPage_Live" ON "TourPage_Live"."ID" = "SiteTree_Live"."ID"
  LEFT JOIN "DepartureDate" ON "DepartureDate"."TourID" = "SiteTree_Live"."ID"
WHERE 
  ("SiteTree_Live"."Locale" = 'en_AU')
  AND ("SiteTree_Live"."ClassName" IN ('TourPage'))
  AND ("DepartureDate"."DepartureDate" LIKE '2012-11%')

但它产生了错误的计数作为查询结果。此查询假定返回的预期结果总数不应超过 245，但目前，它返回的结果超过大约“4569”。

那是因为“DepartureDate”表上的 JOIN，因为当我从“DepartureDate”表中删除连接时，查询返回预期结果。

我需要对我的查询进行哪些修改以计算“SiteTree_Live”、“ID”和“DepartureDate”、“TourID”之间的 Macthes 而只计算“SiteTree_Live”、“ID”计数而不包括出发日期？

欢迎提出任何建议:)

答案

SELECT 
COUNT(DISTINCT SiteTree_Live.ID) 
FROM 
"SiteTree_Live" LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID" 
LEFT JOIN "TourPage_Live" ON "TourPage_Live"."ID" = "SiteTree_Live"."ID" 
LEFT JOIN "DepartureDate" ON "DepartureDate"."TourID" = "SiteTree_Live"."ID" 
WHERE 
("SiteTree_Live"."Locale" = 'en_AU') 
AND ("SiteTree_Live"."ClassName" IN ('TourPage'))
AND ("DepartureDate"."DepartureDate" LIKE '2013-03%')

似乎给了我正确的结果。感谢@Michael Berkowski 的提示

Answer 1

小更正:如果 DepartureDate 是日期类型，那么 LIKE '2013-03% 会强制将其强制转换为字符类型(这是一个mysql feature) 因此，DepartureDate 上的任何索引都不会被使用，IIRC。最好使用普通范围查询:

SELECT 
  COUNT(DISTINCT stl.ID) 
FROM 
  SiteTree_Live stl
LEFT JOIN 
  DepartureDate dd ON dd.TourID = stl.ID 
WHERE 
  stl.Locale = 'en_AU'
  AND stl.ClassName = 'TourPage'
  AND dd.DepartureDate >= '2013-03-01'
  AND dd.DepartureDate < '2013-04-01'
   ;