SQL:在Select as subquery中选择

Question

我得到了以下语句：

select 
product_name as ShortestLength = (select top 1 product_name, len(fact_name) Value_Length 
from table 
order by Value_Length, fact_name ASC)

返回此输出：

shortestlength
PS

我想把这个结果添加到另一个精选声明中：

select
'Product' as Column_Name,
avg(case when product is null then 1.000 else 0 end) * 100 as PctMissing,
count(product) as TotalCount,
count(distinct product) as UniqueCount
from 
table

因此结果将是：

总计数唯一计数<15>PS

column_name	缺少	最短长度
产品	5.100	1181186

您可以使用条件聚合：

select 'Product' as Column_Name,
avg(case when t.product is null then 1.000 else 0 end)*100 as PctMissing,
count(t.product) as TotalCount,
count(distinct t.product) as UniqueCount,
max(case when seqnum = 1 then product_name end) as shortest_length
from (select t.*,
row_number() over (order by len(fact_name), fact_name) as seqnum
from table t 
) t

这假设两个table引用实际上是同一个表。

您只需使用第一个查询作为子查询来代替select语句中的列：

select
'Product' as Column_Name,
avg(case when product is null then 1.000 else 0 end)*100 as PctMissing,
count(product) as TotalCount,
count(distinct product) as UniqueCount,
(select top 1 product_name from table order by Value_Length, fact_name ASC) as ShortestLength 
from table