时间戳

首页 > 解决方案 > 如何简化包含计算的 mssql 查询

sql - 如何简化包含计算的 mssql 查询

问题描述

我有 3 个表，其中包含我试图获取计数然后进行计算的数据。我有一个有效的查询，但它是重复的。

SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as 'Num_To_Go',
(SELECT COUNT(*) from place_been where people.person_id=person_id) as 'Num_Visited',
((​SELECT​ COUNT(*)​ ​FROM​ place_been ​WHERE​ people.person_id=person_id)​ /​ (​SELECT COUNT(*) ​FROM​ place_to_go ​WHERE​ people.person_id=person_id))​ ​*​ 100 ​AS​ ​'Perc_Visited'
FROM people;

我正在尝试完成的是没有重复的子查询来计算百分比。为此我在语法错误中所做的任何更改都变得非常令人沮丧。

以为我可能已经能够使用

SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as 'Num_To_Go',
(SELECT COUNT(*) from place_been where people.person_id=person_id) as 'Num_Visited',
(CONVERT(DECIMAL(3,0), 'Num_To_Go'))/(CONVERT(DECIMAL(3,0), 'Num_Visited')​) ​*​ 100 ​AS​ ​'Perc_Visited'
FROM people;

但这会导致将数据类型 varchar 转换为 numeric

任何指针将不胜感激。

标签： sqlsql-server

解决方案

我会使用APPLY：

SELECT person_id, Num_To_Go, Num_Visited, (Num_To_Go * 1.0 / Num_Visited) * 100 AS Perc_Visited
FROM people p OUTER APPLY
     ( SELECT COUNT(*) AS Num_To_Go
       FROM place_to_go pg
       WHERE P.person_id = pg.person_id
     ) pg OUTER APPLY
     ( SELECT COUNT(*) AS Num_Visited
       FROM place_been pb
       WHERE p.person_id = pb.person_id
     ) pb;

推荐阅读