这个问题在这里已有答案:
我有这个数据库:
我需要为 each 客户端获取以下数据:
-
客户名称
-
Contract 名称
-
项目
-
从当月第一天到当月最后一天记录项目时间的员工
-
本月每位员工记录的总小时数
-
员工比率
-
每位员工的总费用(即员工费率x员工工时)
-
每个 Contract 的结算联系人[姓名,地址]
到目前为止我有以下查询,但我需要实现MySQL的GROUP_CONCAT()的MSSQL版本
SELECT
Cl.LegalName AS ClientNames,
Cr.ContractDesc AS ContractNames,
P.ProjectName AS ProjectNames,
( E.FirstName + ' ' + E.LastName ) AS EmployeeNames,
SUM( WH.HoursWorked ) AS TotalHours,
( SUM( WH.HoursWorked ) * BR.Rate ) AS TotalCharges,
( Ca.FirstName + Ca.LastName + ', ' + Ca.AddrLine1 ) AS BillingContacts
FROM Clients Cl
JOIN Contracts Cr
ON( Cl.ClientID = Cr.ClientID )
JOIN Projects P
ON( Cr.ContractID = P.ContractID )
JOIN EmployeesProjects EP
ON( P.ProjectID = EP.ProjectID )
JOIN Employees E
ON( EP.EmpID = E.EmpID )
JOIN WorkHours WH
ON( E.EmpID = WH.EmpID )
JOIN BillingRates BR
ON( E.TitleID = BR.TitleID ) AND ( E.Level = BR.Level )
JOIN ContractsContacts CC
ON( Cr.ContractID = CC.ContractID )
JOIN Contacts Ca
ON( CC.ContactID = Ca.ContactID )
WHERE WH_Month = 4
AND WH_Year = 2013
当我开始关注this示例时,我到达此处并停止,因为我意识到我无法从其他子查询(ProjectNames)引用表别名(Cr) .
SELECT
Cl.LegalName AS ClientNames,
(
SELECT ContractDesc + ', '
FROM Contracts Cr
WHERE Cl.ClientID = Cr.ClientID
FOR XML PATH('')
) ContractNames,
(
SELECT ProjectName + ', '
FROM Projects P
WHERE Cr.ContractID = P.ContractID
FOR XML PATH('')
) ProjectNames
FROM Clients Cl
我到底该怎么做呢?
3 回答
试试 CROSS APPLY 来做这个:
例如:
这样做
STRING_AGG几乎等同于MySQL中的GROUP_CONCAT . 请参阅Microsoft的官方文档here
请注意,STRING_AGG不允许文本类型,因此如果要在文本字段上连接,则需要转换为NVARCHAR .
例: