sql-server – 在SQL视图中使用COALESCE

这是一个简单的例子,我想做什么.

Customers:
CustomerId int
CustomerName VARCHAR(100)

Orders:
CustomerId int
OrderName VARCHAR(100)

客户与订单之间存在一对多的关系.所以给出这个数据

Customers
1 'John'
2 'Marry'

Orders
1 'New Hat'
1 'New Book'
1 'New Phone'

我想要这样看待：

Name     Orders
'John'   New Hat,New Book,New Phone
'Marry'  NULL

所以每个人都会出现在表中,无论他们是否有订单.

我有一个存储过程,我需要翻译到这个视图,但似乎你不能声明参数和调用存储过程在视图中.关于如何将这个查询转换成视图的任何建议？

CREATE PROCEDURE getCustomerOrders(@customerId int)
AS
   DECLARE @CustomerName varchar(100)
   DECLARE @Orders varchar (5000)

   SELECT @Orders=COALESCE(@Orders,'') + COALESCE(OrderName,'') + ',' 
   FROM Orders WHERE CustomerId=@customerId

   -- this has to be done separately in case orders returns NULL,so no customers are excluded
   SELECT @CustomerName=CustomerName FROM Customers WHERE CustomerId=@customerId

   SELECT @CustomerName as CustomerName,@Orders as Orders

解决方法

/* Set up sample data */
create table Customers (
    CustomerId int,CustomerName VARCHAR(100)
)

create table Orders (
    CustomerId int,OrderName VARCHAR(100)
)

insert into Customers
    (CustomerId,CustomerName)
    select 1,'John' union all
    select 2,'Marry'

insert into Orders
    (CustomerId,OrderName)
    select 1,'New Hat' union all
    select 1,'New Book' union all
    select 1,'New Phone'
go

/* Create the view */       
create view OrderView as    
    select c.CustomerName,x.OrderNames
        from Customers c
            cross apply (select stuff((select ',' + OrderName from Orders o where o.CustomerId = c.CustomerId for xml path('')),1,'') as OrderNames) x
go

/* Demo the view */
select * from OrderView
go 

/* Clean up after demo */
drop view OrderView
drop table Customers
drop table Orders
go

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!