分配给用例语句并使用聚合进行分组

ni65a41a 于 2022-10-22 发布在其他

关注(0)|答案(1)|浏览(140)

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

sql-server

来源：https://stackoverflow.com/questions/74072345/assignment-to-use-case-statement-and-group-by-with-an-aggregate

1条答案

按热度按时间

hjqgdpho1#

不清楚底部案例在做什么，但您可以通过在组中复制案例，甚至可以使用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

赞(0）回复(0）举报 2022-10-22

我来回答

分配给用例语句并使用聚合进行分组

1条答案

相关问题

热门标签

最新问答