如何将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)
我该怎么做? 谢谢。
答
这应该这样做
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name,
CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireID AND
(HireResponse = 0 OR HireResponse = 1)) as FLOAT)/
CAST((select case when COUNT(*) = 0 then 1 else COUNT(*) end from Hire_Response WHERE HireID = a.HireID) as FLOAT) as mySpecialColumn
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
答
您需要为该列添加别名。 这是假设你的计算是正确的参考你需要的列。
select
(Calculated Column expression) as CalculatedColumn,
*
from
TableName
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name,
(CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireIDAND (HireResponse = 0 OR HireResponse = 1)) as FLOAT)/
CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HireID) as FLOAT)) as CalculatedColumn
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
答
你只需要计算添加到您的select语句,因为我已经把下面,我也alaised与列名的计算为您提供:
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name,
CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HierID AND
(HireResponse = 0 OR HireResponse = 1)) as FLOAT)/
CAST((select COUNT(*) from Hire_Response WHERE HireID = a.HierID) as FLOAT) AS [Calculation]
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
+0
如果被零除,它需要默认为0。 – omega 2013-05-14 16:32:38
答
艰难地试图找出没有数据结构的例子的目标,但在这里。我添加了一个新的JOIN,和GROUP BY子句:
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name,
sum(case when hr.HireResponse in (0, 1) then 1.00 else 0.00 end) as Numerator,
count(hr.*) as Denominator,
sum(case when hr.HireResponse in (0, 1) then 1.00 else 0.00 end)/count(hr.*) as Fraction
from
NewHire a join
Position b on a.Position_ID = b.Position_ID join
WorkPeriod c on a.hireID = c.HireID inner join
Hire_Response hr on a.HireID = hr.HireID
where
a.Archived = 0
and c.InquiryID is not null
group by
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
a.DateArchived,
b.Position_Name
order by
a.HireID DESC, a.HireLastName, a.HireFirstName
如果是零的鸿沟,它需要默认为0。 – omega 2013-05-14 16:31:12
@omega我按你的意见我的答案更新。它应该避开零差错。 – Becuzz 2013-05-14 16:52:58