分配给用例语句，并通过聚合进行分组

Question

SELECT count(AccountNumber),
CustomerType = CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL
THEN 'Store with contact'
when personid IS NOT NULL AND StoreID IS NULL
THEN 'store'
when personid IS NULL AND StoreID IS NOT NULL
THEN 'Person' 
ELSE 'Error'
end

FROM Sales.Customer AS c
LEFT JOIN Person.Person AS P
ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.Store ON
c.StoreID = Store.BusinessEntityID 
Group by CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL 
THEN concat(store.name,'-',CONCAT(lastname,',',firstname,'',CASE WHEN middleName IS 
NULL 
THEN ''  WHEN len(middlename) = 1 THEN concat(middlename,'.') when len(middlename)>1
then middlename ELSE 'Error'
end))     when personid IS NOT NULL AND StoreID IS NULL
THEN Store.Name
when personid IS NULL AND StoreID IS NOT NULL
THEN CONCAT(lastname,',',firstname,'',CASE WHEN middleName IS NULL 
THEN '' 
WHEN len(middlename) = 1 
THEN concat(middlename,'.') 
when len(middlename)>1
then middlename
ELSE 'Error'
End

Answer 1

不清楚底部案例在做什么，但您可以通过在组中复制案例，甚至可以使用CROSS APPLY来减少重复

SELECT count(AccountNumber)
,CustomerType = CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
ELSE 'Error'
end
FROM Sales.Customer AS c
LEFT JOIN Person.Person AS P ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.Store ON c.StoreID = Store.BusinessEntityID 
Group By CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
ELSE 'Error'
end

或者使用交叉应用

SELECT count(AccountNumber)
,CustomerType 
FROM Sales.Customer AS c
LEFT JOIN Person.Person AS P ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.Store ON c.StoreID = Store.BusinessEntityID 
Cross Apply ( values ( CASE WHEN personid IS NOT NULL AND StoreID IS NOT NULL THEN 'Store with contact'
when personid IS NOT NULL AND StoreID IS NULL     THEN 'store'
when personid IS NULL AND StoreID IS NOT NULL     THEN 'Person' 
ELSE 'Error'
end
) ) D(CustomerType)
Group By CustomerType