sql查询从同一列中获取一行的多个值
发布于 2021-05-10 20:57:40
如果每个员工有多个联系方式,我将获得多行,但我只希望每位员工列出他们的姓,名,部门名称,电子邮件和电话。
所以它应该像这样
First Name Last Name Division Email Phone
Test Guy Exec test@gmail.com 555-5555
这是我所拥有的,但不起作用:
SELECT sr.LastName, sr.FirstName, dd.Name,
Email = (select sc.ContactValue FROM StaffContactInformation as sc
INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
where sc.ContactTypeID = 3 and roster.ID = sr.ID),
Phone = (SELECT sc1.ContactValue FROM StaffContactInformation as sc1
INNER JOIN StaffRoster as roster on sc1.StaffID = roster.ID
where sc1.ContactTypeID = 1)
FROM StaffRoster as sr
left join dictDivisions as dd on sr.DivisionID = dd.Id
left join StaffContactInformation as sci on sr.ID = sci.StaffID
inner join dictStaffContactTypes as dsct on sci.ContactTypeID = dsct.ID
where (sr.Active = 1 and sr.isContractor = 0 )
ORDER BY sr.LastName, sr.FirstName
我用以下查询解决了它:
SELECT sr.LastName, sr.FirstName, dd.Name,
Email = (select sc.ContactValue FROM StaffContactInformation as sc
INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
where sc.ContactTypeID = 3 and roster.ID = sr.ID),
Phone = (SELECT sc.ContactValue FROM StaffContactInformation as sc
INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
where sc.ContactTypeID = 1 and roster.ID = sr.ID)
FROM StaffRoster as sr
left join dictDivisions as dd on sr.DivisionID = dd.Id
where (sr.Active = 1 and sr.isContractor = 0 )
ORDER BY sr.LastName, sr.FirstName
关注者
0
被浏览
129
1 个回答
-
如果要选择
email
,则phone
在子查询中,这两个联接可能是不必要的:left join StaffContactInformation as sci on sr.ID = sci.StaffID inner join dictStaffContactTypes as dsct on sci.ContactTypeID = dsct.ID
由于它们,您得到的行与特定人的联系人一样多。
最终查询可能类似于:
SELECT sr.LastName, sr.FirstName, dd.Name, Email = ( select sc.ContactValue FROM StaffContactInformation as sc INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID where sc.ContactTypeID = 3 and roster.ID = sr.ID ), Phone = ( SELECT sc1.ContactValue FROM StaffContactInformation as sc1 INNER JOIN StaffRoster as roster on sc1.StaffID = roster.ID where sc1.ContactTypeID = 1 ) FROM StaffRoster as sr left join dictDivisions as dd on sr.DivisionID = dd.Id where (sr.Active = 1 and sr.isContractor = 0 ) ORDER BY sr.LastName, sr.FirstName
推荐阅读
-
在一行中显示一列的多个值(SQL Oracle)
2021-06-15 关注 0 浏览172 1答案
-
如何使用sql查询在同一列中减去两行的值?
2021-04-20 关注 0 浏览94 1答案
-
SQL-如何从同一列中选择多个表并联接多个行?
2021-04-15 关注 0 浏览59 1答案
-
SQL查询从多个列中获取多个最大值
2021-06-15 关注 0 浏览96 1答案
-
从查询结果中获取一列值的列表
2021-01-29 关注 0 浏览63 1答案
-
将值从同一表中的一列更新到SQL Server中的另一列
2021-05-10 关注 0 浏览94 1答案
-
SQL查询以获取与另一列的MAX值对应的列值?
2021-02-02 关注 0 浏览126 1答案
-
GROUP BY同一列中的多个值
2021-06-04 关注 0 浏览133 1答案
-
SQL-从另一个表中获取一列以加入此查询
2021-04-09 关注 0 浏览77 1答案
-
SQL根据一列中的最大值从多列中选择不同的行
2021-07-06 关注 0 浏览112 1答案