mySQL中的嵌套select语句

Question

我有一个现有的SQL查询，它在SQL Server中工作得很好，我想在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')
   )
)

这应该返回在EmployeeID表中InOut = True比InOut = False多的任何TimeClock。

谢谢。

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)))

如果这三个人都有相似的表现，那么归根结底是个人偏好。