SQL AdventureWorks 按城市按性别统计员工数量

Question

我想按性别统计城市，如下所示；

City    GenderFCount  GenderMCount
Redmond 10             20   

这是我的查询，在 AdventureWorks 数据库中获取城市和性别

select Gender,City from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID

如果可能的话，您能否以多种方式展示解决方案，例如“PIVOT”、通过sql函数(UDF)、存储过程或其他方式。

谢谢

Answer 1

这是 PIVOT 查询，您可以将其转储到存储过程或 udf

select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable

UDF 示例

CREATE FUNCTION fnPivot()
RETURNS TABLE

AS
RETURN (
select City, F as GenderFCount, M as GenderMCount
 from(
select Gender,City
from HumanResources.Employee as t1
    inner join HumanResources.EmployeeAddress as t2
    on t1.EmployeeID = t2.EmployeeID
    inner join Person.Address as t3
    on t2.AddressID = t3.AddressID
    ) AS pivTemp
PIVOT
(   count(Gender)
    FOR Gender IN ([F],[M])
) AS pivTable
)
GO

现在你可以这样调用它

 SELECT * FROM dbo.fnPivot()