mysql - 需要帮助创建 SQL 查询

Question

因此我有两个表:

 CREATE TABLE `workers` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `number` int(7) NOT NULL,
  `percent` int(3) NOT NULL,
  `order` int(7) NOT NULL,
      PRIMARY KEY (`id`)
 );
 CREATE `data` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `workerId` int(7) NOT NULL,
  PRIMARY KEY (`id`)
 );

我想返回第一个 worker (按 order ASC 排序)他在表中的行数 data 乘以 percent(来自表 workers)/100 小于 number(来自表 workers。

我试过这个查询:

SELECT workers.id, COUNT(data.id) AS `countOfData`
FROM `workers` as workers, `data` as data
WHERE data.workerId = workers.id
   AND workers.percent * `countOfData` < workers.number
LIMIT 1

但是我得到了错误:

#1054 - Unknown column 'countOfData' in 'where clause'

Answer 1

这应该有效:

SELECT A.id
FROM workers A
LEFT JOIN (SELECT workerId, COUNT(*) AS Quant
            FROM data
            GROUP BY workerId) B
ON A.id = B.workerId
WHERE (COALESCE(Quant,0) * `percent`)/100 < `number`
ORDER BY `order`
LIMIT 1