First off, apologies for a long post. It's really more simple than it looks ;-)
首先，为一个长职位道歉。它真的比它看起来更简单;
I'm trying to do something that I think is conceptually simple, and I believe I'm most of the way there, but there's one last part that I can't implement without errors that I can't figure out how to fix.
我在尝试做一些我认为概念上很简单的事情，我相信我已经完成了大部分工作，但是还有最后一个部分我不能实现，没有错误，我无法找到如何修复的方法。
I have three related tables.
我有三个相关的表格。
Orders:
Each row is an Order with a unique ID, there will never be duplicates.
订单:每一行都是唯一ID的订单，不会有重复。
+---------+---------+
| OrderID | Name |
+---------+---------+
| 1 | Order 1 |
| 2 | Order 2 |
| 3 | Order 3 |
+---------+---------+
Order Details:
Relational table where each row is a product line on an order.
Order Details:关系表，其中每行是订单上的产品线。
+---------+-----------+
| OrderID | ProductID |
+---------+-----------+
| 1 | a |
| 2 | b |
| 2 | c |
| 3 | a |
| 3 | b |
| 3 | b |
+---------+-----------+
As you can see some orders have just one product (1), some will have multiple products (2) and some will have duplicate products (3).
正如你看到的一些订单只有一个产品(1)，一些将有多个产品(2)，一些将有重复的产品(3)。
Products
Each row is a product with a unique ID, there will never be duplicates.
产品每行都是具有唯一ID的产品，永远不会有重复。
+-----------+-------------+
| ProductID | Description |
+-----------+-------------+
| a | Chicken |
| b | Fish |
| c | Beef |
+-----------+-------------+
I want to return all rows from the Orders table and conditionally return some information about the related Products in one column.
我希望返回Orders表中的所有行，并有条件地在一列中返回一些关于相关产品的信息。
The condition is that I look at how many DISTINCT products each Order has. If it's just 1 then I want to return the Product Description value. If it's more than 1 then I want to return some placeholder text such as 'Multi'.
条件是我看每个订单有多少不同的产品。如果是1，那么我想返回产品描述值。如果大于1，那么我想返回一些占位符文本，比如'Multi'。
I think that I need to use CASE to get this working, but I can't figure it out.
我认为我需要用CASE来使它工作，但是我搞不清楚。
I can count the unique products successfully like this:
我能像这样成功地数出独一无二的产品:
SELECT
o.Name
,COUNT(DISTINCT d.ProductId) as 'Unique Products'
FROM Orders o
LEFT JOIN OrderDetails d ON o.OrderID = d.OrderID
LEFT JOIN Products p on d.ProductId = p.ProductId
GROUP BY o.Name
ORDER BY o.Name DESC
GO
Results are like this:
结果是这样的:
+---------+-----------------+
| Name | Unique Products |
+---------+-----------------+
| Order 1 | 1 |
| Order 2 | 2 |
| Order 3 | 2 |
+---------+-----------------+
What I want is this:
我想要的是:
+---------+-----------------+
| Name | Unique Products |
+---------+-----------------+
| Order 1 | Chicken |
| Order 2 | Multi |
| Order 3 | Multi |
+---------+-----------------+
I have been trying to use CASE which I believe I've gotten correct:
我一直在尝试用例，我相信我已经得到了正确的结果:
CASE WHEN (COUNT(DISTINCT d.ProductId)) > 1 THEN 'Multi' ELSE p.Description END AS 'Products'
However unless I add p.Description to GROUP BY then I get the error (which I understand):
除非我加上p。对组的描述，我得到了错误(我理解):
Column 'Product.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
列的产品。描述在选择列表中是无效的，因为它不包含在聚合函数或GROUP BY子句中。
But if I do add it the results aren't what I want, for example:
但是如果我把它相加，结果就不是我想要的了，例如:
+---------+----------+
| Name | Products |
+---------+----------+
| Order 1 | Chicken |
| Order 2 | Fish |
| Order 2 | Beef |
| Order 3 | Chicken |
| Order 3 | Fish |
| Order 3 | Fish |
+---------+----------+
When it should just say "Order 2 - Multi" on one row for example. This is the bit I don't understand.
例如，当它应该只在一行上写“Order 2 - Multi”。这是我不明白的一点。
If can get some help on this bit alone it would solve my problem and I'd accept the answer. However...
如果能在这一点上得到一些帮助，它将解决我的问题，我接受答案。然而……
Bonus Round
奖金轮
The above is fine and all, but if this bit is possible I'd accept this as an answer above the others.
以上这些都很好，但是如果这一点是可能的，我将接受这个作为比其他答案更高的答案。
Can I concatenate the product names? I've been looking at COALESCE and FOR XML PATH but I can't wrap my head around them at all so I don't even have any code to show.
我可以连接产品名称吗?我一直在研究合并和XML路径，但我根本无法完全理解它们，所以我甚至没有任何代码可以显示。
Results would look something like this:
结果是这样的:
+---------+--------------+
| Name | Products |
+---------+--------------+
| Order 1 | Chicken |
| Order 2 | Fish;Beef |
| Order 3 | Chicken;Fish |
+---------+--------------+
If you've made it this far I commend you! Thanks!
如果你做到了这一点，我推荐你!谢谢!
1 个解决方案
#1
5
You are pretty close. You just need some case logic and an aggregation function around the description:
你是相当接近。你只需要一些案例逻辑和一个围绕描述的聚合函数:
SELECT o.Name,
(CASE WHEN COUNT(DISTINCT d.ProductId) = 1
THEN MAX(p.description)
ELSE 'Multi'
END) as Descriptions
FROM Orders o LEFT JOIN
OrderDetails d
ON o.OrderID = d.OrderID LEFT JOIN
Products p
ON d.ProductId = p.ProductId
GROUP BY o.Name
ORDER BY o.Name DESC
The second part is a very different question. In SQL Server, you need to use an XML subquery:
第二部分是一个非常不同的问题。在SQL Server中，需要使用XML子查询:
select o.Name,
stuff((select distinct ',' + p.description
from OrderDetails d left join
Products p
on d.ProductId = p.ProductId
where o.OrderID = d.OrderID
for xml path (''), type
).value('.', 'nvarchar(max)'
), 1, 1, ''
) as descriptions
from Orders o
order by o.Name desc
#1
5
You are pretty close. You just need some case logic and an aggregation function around the description:
你是相当接近。你只需要一些案例逻辑和一个围绕描述的聚合函数:
SELECT o.Name,
(CASE WHEN COUNT(DISTINCT d.ProductId) = 1
THEN MAX(p.description)
ELSE 'Multi'
END) as Descriptions
FROM Orders o LEFT JOIN
OrderDetails d
ON o.OrderID = d.OrderID LEFT JOIN
Products p
ON d.ProductId = p.ProductId
GROUP BY o.Name
ORDER BY o.Name DESC
The second part is a very different question. In SQL Server, you need to use an XML subquery:
第二部分是一个非常不同的问题。在SQL Server中，需要使用XML子查询:
select o.Name,
stuff((select distinct ',' + p.description
from OrderDetails d left join
Products p
on d.ProductId = p.ProductId
where o.OrderID = d.OrderID
for xml path (''), type
).value('.', 'nvarchar(max)'
), 1, 1, ''
) as descriptions
from Orders o
order by o.Name desc