编写与MySQL查询等效的MSSQL(2019)查询

Question

我在我的项目中编写了以下MySQL查询，以选择所有具有匹配test_change表记录的测试表记录(以第一个为准(，用于数据显示。

我需要MSSQL等效查询，但由于我的知识有限，无法形成查询。我尝试了网站http://www.sqlines.com/online但我没有帮我。

这是MySQL查询：

SELECT
tests.*,
cases.title,
users.name,
statuses.label as status_label,
statuses.color_dark,
tc.id as change_id,
tc.created_on
FROM
tests
left join (
select
MIN(created_on) as created_on,
test_id,
id,
assignedto_id
from
test_changes
group by
test_id
) tc on tests.id = tc.test_id
LEFT JOIN users ON tc.assignedto_id = users.id
LEFT JOIN cases ON tests.case_id = cases.id
LEFT JOIN statuses ON tests.status_id = statuses.id
WHERE
tests.id is not null
AND tests.run_id IN (22)
AND (
tests.status_id = 3
or tests.status_id = 4
or (
tests.status_id != 3
and tc.created_on > 1620950399
)
)
GROUP BY
tests.id
ORDER BY
users.name DESC
LIMIT
15, 20

这是我尝试的MSSQL查询。。。

SELECT
tests.*,
cases.title,
users.name,
statuses.label as status_label,
statuses.color_dark,
tc.id as change_id,
tc.created_on
FROM
tests
left join (
select
MIN(created_on) as created_on,
status_id,
test_id,
id,
assignedto_id
from
test_changes
group by
test_id
) tc on tests.id = tc.test_id
LEFT JOIN users ON tc.assignedto_id = users.id
LEFT JOIN cases ON tests.case_id = cases.id
LEFT JOIN statuses ON tests.status_id = statuses.id
WHERE
tests.id is not null
AND tests.run_id IN (22)
AND (
tests.status_id = 3
or tests.status_id = 4
or (
tests.status_id != 3
and tc.created_on > 1620950399
)
)
GROUP BY
tests.id
ORDER BY
users.name DESC OFFSET 15 ROWS FETCH NEXT 20 ROWS ONLY

它正在引发以下错误。。。

列"test_changes.status_id"在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY中条款

有人能帮我解决错误并形成此MSSQL查询吗？

Answer 1

即使您在mysql中的第一个查询也会出现同样的错误，当您按分组时，您也无法选择未聚合或属于分组依据的列。

因此，您似乎也通过assigneto_id和test_id进行了分组：

select
MIN(created_on) as created_on,
status_id,
test_id,
--id,  <-- removed this column , looks not used in query
assignedto_id
from
test_changes
group by
test_id, status_id,assignedto_id -- < adding new columns to group by
) tc on ....

它可能不是你想要的，但它能让你了解的工作原理