T-SQL如何GROUP BY两个字段并连接另一个

问题描述：

我试图在两个字段上组合，并返回第三个字段，该字段是前两个字段的共同值，产品ID。这里是我的数据：T-SQL如何GROUP BY两个字段并连接另一个

ProductID Currency Price Territory 
1   USD  6.99 US 
1   EUR  4.99 GR 
1   EUR  4.99 HU 
1   EUR  4.99 LT 
2   USD  7.99 US 
2   EUR  5.99 GR 
2   EUR  5.99 HU

我想结果回来是这样的：

ProductID Currency Price Territories 
1   USD  6.99 US 
1   EUR  6.99 GR, HU, LT

我可以拉Currenty和价格列，但不能连接它们共有的领土：

SELECT Currency, Price 
FROM TerritoryPricing 
WHERE ProductID = 1 
GROUP BY Currency, Price

如何连接区域？

Google：“SQL Server聚合字符串连接” –

看这篇文章http://stackoverflow.com/questions/3368942/grouped-string-aggregation-listagg-for-sql-server – are

http://sqlperformance.com/2014/08/t-sql-queries/sql-server-groupped-concatenation –

答

您可以使用SQL的XML处理使用SQL 2000和以上生成连接列表：

create table #TerritoryPricing (ProductID int, Currency varchar(3), Price decimal(10,2), Territory varchar(2)) 
insert into #TerritoryPricing values (1,'USD',6.99,'US') 
insert into #TerritoryPricing values (1,'EUR',4.99,'GR') 
insert into #TerritoryPricing values (1,'EUR',4.99,'HU') 
insert into #TerritoryPricing values (1,'EUR',4.99,'LT') 
insert into #TerritoryPricing values (2,'USD',7.99,'US') 
insert into #TerritoryPricing values (2,'EUR',5.99,'GR') 
insert into #TerritoryPricing values (2,'EUR',5.99,'HU') 

SELECT Currency, Price, 
    SUBSTRING(
     (SELECT (',' + ltrim(rtrim(Territory))) 
     FROM #TerritoryPricing t2 
     WHERE t1.Currency = t2.Currency 
      and t1.Price = t2.Price 
      and ProductID = 1 
     ORDER BY t2.Territory 
     FOR XML PATH('') 
     ), 2, 8000) Territories 
FROM #TerritoryPricing t1 
WHERE ProductID = 1 
GROUP BY Currency, Price

这很好，谢谢。我唯一需要添加的是子查询WHERE子句中的“WHERE ProductID = 1”。谢谢！ –

答

可以使用的毗连：

SELECT ProductID, Currency, Price, CONCAT(Territory) 
GROUP BY territory 
FROM employee_tbl;

，或者创建一个函数colace像这样

CREATE FUNCTION [dbo].[terr] 
(
    @territoryID int 
) 
RETURNS varchar(max) 
AS 
BEGIN 
    declare @output varchar(max) 
    select @output = COALESCE(@output + ', ', '') + territory 
    from TerritoryPricing 
    where territoryid = @territoryID 

    return @output 
END 

GO 

SELECT UserID, [dbo].terr(territoryID) 
FROM TerritoryPricing 
GROUP BY territory 

GO

答

测试此代码

DECLARE @S VARCHAR(8000) 
SELECT 
    Currency, 
    Price , 
    Territories = (Select @S = @S + Territory FROM TerritoryPricing as T2 WHERE T1.Currency = T2.Currency AND T1.Price = T2.Price) , 
    Empty = (select @S ='') 
FROM TerritoryPricing as T1 
WHERE ProductID = 1 
GROUP BY Currency, Price

T-SQL如何GROUP BY两个字段并连接另一个

相关推荐