mySQL 条件选择(第 2 部分)

Question

我原来的问题在这里:mySQL conditional select?

现在，在根据我的真实状态调整 Gordon Linoff 提出的第二个代码后，查询应如下所示:

SELECT id
FROM leads t JOIN
        (SELECT "OKQ" as status, 86400 as diff UNION ALL
         SELECT "PI" as status, 3600 as diff UNION ALL
         SELECT "CBK" as status, 60 as diff UNION ALL
         SELECT "NRP" as status, 3600 as diff UNION ALL
         SELECT "NEW" as status, 1 as diff
        ) s
ON t.status = s.status
WHERE t.time > date_sub(now(), interval s.diff second)
ORDER BY RAND() LIMIT 1

现在这个查询抛出一个错误:

ErreurColumn 'status' in field list is ambiguous

Answer 1

您的查询(如所写)不会产生该错误。如果所有列名称都是限定的，那么您将不会收到错误(唯一不限定的名称是 id)。

据推测，您的 SELECT 中的列数不仅仅是 id。您应该SELECT t.* 或另一种方法是使用USING 子句:

SELECT  . . .
FROM leads t JOIN
     (SELECT 'OKQ' as status, 86400 as diff UNION ALL
      SELECT 'PI' as status, 3600 as diff UNION ALL
      SELECT 'CBK' as status, 60 as diff UNION ALL
      SELECT 'NRP' as status, 3600 as diff UNION ALL
      SELECT 'NEW' as status, 1 as diff
     ) s
     USING (status)
WHERE t.time > date_sub(now(), interval s.diff second)
ORDER BY RAND()
LIMIT 1;