如何将select语句与计算值合并为新列?
问题描述:
在我的SQL Server的代码,我有这样的select语句如何将select语句与计算值合并为新列?
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name
from NewHire a
join Position b on a.Position_ID = b.Position_ID
join WorkPeriod c on a.hireID = c.HireID
where a.Archived = 0 and c.InquiryID is not null
order by a.HireID DESC, a.HireLastName, a.HireFirstName
而且我想一个新列添加到它。然而,这个列不是表格中的一列,它只是用来存储我从现有列中计算得出的float
。
我得到的数字是这样计算的: @acc
是上述select语句中的a.HireID
。
CAST((select COUNT(*) from Hire_Response WHERE HireID = @acc AND (HireResponse = 0 OR HireResponse = 1)) as FLOAT)/
CAST((select COUNT(*) from Hire_Response WHERE HireID = @acc) as FLOAT)
我该怎么做? 谢谢。
答
您可以使用GBN为你做的代码对你other question作为一个子查询
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name,
d.percentage
from NewHire a
inner join Position b on a.Position_ID = b.Position_ID
inner join WorkPeriod c on a.hireID = c.HireID
left join (select NH.HireID, ISNULL(1E0 * COUNT(CASE WHEN HireResponse IN (0,1) THEN HR.HireID END)/NULLIF(COUNT(HR.HireID), 0) , 0) AS percentage
from NewHire NH LEFT JOIN Hire_Response HR ON NH.HireID = HR.HireID
group by NH.HireID) d
ON a.HireID = d.HireID
where a.Archived = 0 and c.InquiryID is not null
order by a.HireID DESC, a.HireLastName, a.HireFirstName
...这将列percentage
添加到当前的查询。这可能会得到改善,但它应该给你一个上帝的想法,你可以如何将你的问题的结果放在一起。
请不要在同一个问题上发布多个网站。如有需要,我们可以提出问题。另外,请不要在DBA.SAE上发布这样的基本问题。 – JNK 2013-05-14 16:26:23