久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

在 SQL 視圖中使用 COALESCE

Using COALESCE in SQL view(在 SQL 視圖中使用 COALESCE)
本文介紹了在 SQL 視圖中使用 COALESCE的處理方法,對(duì)大家解決問(wèn)題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧!

問(wèn)題描述

我需要從多個(gè)表創(chuàng)建一個(gè)視圖.視圖中的一列必須由表中的多行組成,作為帶有逗號(hào)分隔值的字符串.

I need to create a view from several tables. One of the columns in the view will have to be composed out of a number of rows from one of the table as a string with comma-separated values.

這是我想做的事情的一個(gè)簡(jiǎn)化示例.

Here is a simplified example of what I want to do.

Customers:
CustomerId int
CustomerName VARCHAR(100)

Orders:
CustomerId int
OrderName VARCHAR(100)

客戶和訂單之間存在一對(duì)多關(guān)系.所以給定這個(gè)數(shù)據(jù)

There is a one-to-many relationship between Customer and Orders. So given this data

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

這樣每個(gè)人都會(huì)出現(xiàn)在表格中,無(wú)論他們是否有訂單.

So that EVERYBODY shows up in the table, regardless of whether they have orders or not.

我有一個(gè)需要轉(zhuǎn)換為該視圖的存儲(chǔ)過(guò)程,但您似乎無(wú)法在視圖中聲明參數(shù)和調(diào)用存儲(chǔ)過(guò)程.關(guān)于如何將此查詢放入視圖中的任何建議?

I have a stored procedure that i need to translate to this view, but it seems that you cant declare params and call stored procs within a view. Any suggestions on how to get this query into a view?

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

推薦答案

EDIT:修改答案以包含視圖的創(chuàng)建.

EDIT: Modified answer to include creation of view.

/* 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,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

這篇關(guān)于在 SQL 視圖中使用 COALESCE的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!

【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

SQL trigger on Truncate(截?cái)鄷r(shí)的 SQL 觸發(fā)器)
sql search query with multiple optional search parameters(具有多個(gè)可選搜索參數(shù)的 sql 搜索查詢)
SQL Efficiency: WHERE IN Subquery vs. JOIN then GROUP(SQL 效率:WHERE IN 子查詢 vs. JOIN 然后 GROUP)
Retrieving XML element name using t-SQL(使用 t-SQL 檢索 XML 元素名稱)
Insert double quotes into SQL output(在 SQL 輸出中插入雙引號(hào))
Delete rows from CTE in SQL SERVER(從 SQL SERVER 中的 CTE 中刪除行)
主站蜘蛛池模板: 国产成人精品三级麻豆 | 97视频免费在线观看 | 日韩一级大片 | 日韩免费在线播放 | 日本黄色片视频 | 亚洲精品午夜 | 涩涩在线 | 免费视频久久 | 欧美一区二区三区在线观看 | 亚洲综合激情网 | 一级片免费观看 | 久久精品视频99 | 精品一二三区 | 午夜aaa| 综合色婷婷一区二区亚洲欧美国产 | 精品国产一区二区在线观看 | 日韩成人小视频 | 欧美一级片免费看 | 美女免费视频网站 | 亚洲视频三区 | 国产精品久久视频 | 久久久久久久网站 | av片在线看 | 视频一区二区在线观看 | 久久精品欧美一区二区 | 亚洲www| 偷拍一区二区 | 天天操天天看 | 国产成人91 | 一级黄色网 | 日韩精品视频在线免费观看 | 日韩国产精品视频 | 成人欧美在线 | 国产日韩精品视频 | 久草免费在线视频 | 日韩在线视频观看 | 懂色av一区二区夜夜嗨 | 人与拘一级a毛片 | 久久久精品一区二区三区 | 精品综合网 | 黄视频在线播放 |