MySQL查询聚合MAX和子查询

Question

有没有办法从 select 内部的子查询中获取值？

stop_link表

id | duid | domain_id 
1  | 1    | 1
2  | 2    | 1
3  | 1    | 2
4  | 2    | 2
5  | 3    | 1

我想要的结果(假设domain_id = 2)

duid    | domain_id 
3       | 2

查询(不起作用):

INSERT INTO stop_link (
              duid,

              domain_id) 
SELECT
  IFNULL(MAX(sl.duid), 0) + 1 AS duid,
  sl.domain_id 
FROM
  stop_link sl
WHERE sl.domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1)

查询有效，但我希望避免设置变量:

SET @domain_id = (SELECT sd.id FROM stop_domain sd LIMIT 1);
SELECT
  IFNULL(MAX(sl.duid), 0) + 1 AS duid
  , @domain_id
FROM
  stop_link sl
WHERE sl.domain_id = @domain_id;

Answer 1

你的意思是这样的吗:

/*Your example Table*/

DECLARE @T  
TABLE(ID INT,duid INT,domain_id INT)

INSERT INTO @T
VALUES
(1 , 1    , 1 ),
(2  , 2    , 1),
(3  , 1    , 2),
(4  , 2    , 2),
(5  , 3    , 1)

--The query
SELECT domain_id,Isnull(max(duid),0)+1 [newId]
FROM @T
GROUP BY domain_id