問題描述

我想知道是否可以獲取查詢結(jié)果并將它們作為 CSV 字符串而不是作為一列單元格返回.

I am wondering if it is possible to take the results of a query and return them as a CSV string instead of as a column of cells.

基本上，我們有一個名為Customers 的表，還有一個名為CustomerTypeLines 的表，每個Customer 可以有多個CustomerTypeLines.當(dāng)我針對它運(yùn)行查詢時，當(dāng)我想檢查多種類型時會遇到問題，例如:

Basically, we have a table called Customers, and we have a table called CustomerTypeLines, and each Customer can have multiple CustomerTypeLines. When I run a query against it, I run into problems when I want to check multiple types, for instance:

    Select * 
      from Customers a 
Inner Join CustomerTypeLines b on a.CustomerID = b.CustomerID 
     where b.CustomerTypeID = 14 and b.CustomerTypeID = 66

...不返回任何內(nèi)容，因?yàn)榭蛻舨荒芡瑫r擁有兩者，很明顯.

...returns nothing because a customer can't have both on the same line, obviously.

為了使它工作，我必須向客戶添加一個名為 CustomerTypes 的字段，它看起來像 ,14,66,67, 這樣我就可以做一個 Where a.CustomerTypes like'%,14,%' 和 a.CustomerTypes 像 '%,66,%' 返回 85 行.

In order to make it work, I had to add a field to Customers called CustomerTypes that looks like ,14,66,67, so I can do a Where a.CustomerTypes like '%,14,%' and a.CustomerTypes like '%,66,%' which returns 85 rows.

當(dāng)然這很痛苦，因?yàn)槊看胃?CustomerTypeLines 表時，我都必須讓我的程序?yàn)樵摽蛻糁亟ù俗侄?

Of course this is a pain because I have to make my program rebuild this field for that Customer each time the CustomerTypeLines table is changed.

如果我可以在我的 where 中做一個子查詢來為我做這項(xiàng)工作，那就太好了，而不是像這樣返回結(jié)果:

It would be nice if I could do a sub query in my where that would do the work for me, so instead of returning the results like:

14
66
67

它會像 ,14,66,67,

這可能嗎?

推薦答案

您將在以逗號分隔的列表上執(zhí)行 LIKE 查詢時遇到各種問題.我知道，我去過那里.

You're going to run into all kinds of problems doing a LIKE query on a comma-delimited list. I know, I've been there.

例如，如果您搜索 '%,14,%'，如果 14 是列表中的第一項(xiàng)或最后一項(xiàng)會怎樣?(我意識到您指定了額外的前導(dǎo)和尾隨逗號，但 COALESCE 方法不提供這些.)

For example, if you search for '%,14,%', what happens if 14 is the first or last item in the list? (I realize you specify extra leading and trailing commas, but the COALESCE method doesn't supply those.)

這個怎么樣:

Select * from Customers a 
Inner Join CustomerTypeLines b 
on a.CustomerID = b.CustomerID 
WHERE a.CustomerID in 
    (SELECT customerID from CustomerTypeLines
     WHERE CustomerTypeID = 14)
AND a.CustomerID in
    (SELECT customerID from CustomerTypeLines
     WHERE CustomerTypeID in 66)

編輯以解決過快閱讀問題的問題！

這篇關(guān)于水平顯示查詢結(jié)果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！