mysql - mySQL 中的嵌套选择语句

Question

我有一个在 SQL Server 中运行良好的现有 SQL 查询，我想在 MySQL 中运行。我已经稍微清理了一下，并修改了列/表名称等的反引号，但无法使其正常工作。

这里是有问题的陈述:

SELECT `EmployeeID`
FROM `Employee`
WHERE
(
   (
      SELECT COUNT(*) AS A
      FROM `TimeClock`
      WHERE (`EmployeeID` = `Employee.EmployeeID`)
         AND (`InOut` = 'True')
   ) > (
      SELECT COUNT(*) AS B
      FROM `TimeClock` AS `TimeClock_1`
      WHERE (`EmployeeID` = `Employee.EmployeeID`)
         AND (`InOut` = 'False')
   )
)

这应该返回 TimeClock 中 InOut = True 多于 InOut = False 的所有 EmployeeID > 表格。

谢谢。

Answer 1

这应该为您提供 EmployeeId 值，其中 InOut True 的值多于 False

SELECT e.EmployeeId
FROM Employee AS e
INNER JOIN
(
   SELECT tc.EmployeeID
      , SUM(CASE
               WHEN tc.InOut = 1 THEN 1 ELSE 0
            END) AS InOutTrue
      , SUM(CASE
               WHEN tc.InOut = 0 THEN 1 ELSE 0
            END) AS InOutFalse
   FROM TimeClock AS tc
   GROUP BY tc.EmployeeId
) AS t ON e.EmployeeId = t.EmployeeId
WHERE t.InOutTrue > t.InOutFalse

我想出的两个备选方案在我的非常小 未索引的测试数据上表现出相似的性能。我建议您根据自己的数据测试它们，看看它们是否有任何不同:

这个几乎相同，但使用总记录数而不是 InOutFalse。

SELECT e.EmployeeId
FROM Employee AS e
INNER JOIN
(
   SELECT tc.EmployeeID
      , COUNT(1) AS RecordCount
      , SUM(CASE
               WHEN tc.InOut = 1 THEN 1 ELSE 0
            END) AS InOutTrue
   FROM TimeClock AS tc
   GROUP BY tc.EmployeeId
) AS t ON e.EmployeeId = t.EmployeeId
WHERE t.InOutTrue > RecordCount - t.InOutTrue

我不确定 MySQL 是否需要我在这里使用的 CAST，但 SQL Server 需要。 (这就是我测试的内容)它无法在 BIT 列上执行 SUM。

SELECT e.EmployeeId
FROM Employee AS e
INNER JOIN TimeClock AS tc ON e.EmployeeId = tc.EmployeeId
GROUP BY e.EmployeeId
HAVING SUM(CAST(tc.InOut AS INT)) > (COUNT(1) - SUM(CAST(tc.InOut AS INT)))

如果这三者的表现都相似，则归结为个人偏好。