• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

子查询与组中的每个值 – 由

sql 来源:dg3 8次浏览

我有问题选择由GROUP BY子句生成的多个值。 我尝试建立什么,我有一个简单的例子:子查询与组中的每个值 – 由

Table CUSTOMERS Table PRODUCTS   Table ORDERS 
ID | NAME   ID | DESCR | PROMO   ID_P | ID_C 
---+---------  ---+-------+-------  -----+----- 
1 | Alice   1 | prod1 | gold   1 | 1 
2 | Bob   2 | prod2 | gold   2 | 3 
3 | Charlie  3 | prod3 | silver   1 | 2 
        4 | prod4 | silver   3 | 1 

从这个我LIK加入每一个产品和每一个客户在一个单元

Results 
PROMO | products  | CUSTOMERS 
-------+--------------+-------------------- 
gold | prod1, prod2 | Alice, Bob, Charlie 
silver | prod3  | Alice 

喜欢的东西:

SELECT PRODUCTS.PROMO 
    , CONCAT(PRODUCTS.DESCR) 
    , STUFF(
     (SELECT '/' + CUSTOMERS.NAME 
      FROM CUSTOMERS 
      WHERE CUSTOMERS.ID = ORDERS.ID 
      FOR XML PATH ('')) 
     , 1, 3, '') 
FROM  PRODUCTS 
INNER JOIN ORDERS 
ON PRODUCTS.ID = ORDERS.ID_P 
WHERE PRODUCTS.ID < 3 
GROUP BY PRODUCTS.PROMO 

这可以在SQL中实现吗?


===========解决方案如下:

您可以使用它。

DECLARE @CUSTOMERS Table(ID INT, NAME VARCHAR(20)) 
INSERT INTO @CUSTOMERS VALUES 
(1 ,'Alice'),    
(2 ,'Bob'),    
(3 ,'Charlie')    

DECLARE @PRODUCTS TABLE (ID INT, DESCR VARCHAR(10), PROMO VARCHAR(10)) 
INSERT INTO @PRODUCTS VALUES 
(1 ,'prod1','gold'), 
(2 ,'prod2','gold'), 
(3 ,'prod3','silver'), 
(4 ,'prod4','silver') 

DECLARE @ORDERS TABLE ( ID_P INT, ID_C INT) 
INSERT INTO @ORDERS VALUES 
( 1 ,1), 
( 2 ,3), 
( 1 ,2), 
( 3 ,1) 


;WITH CTE AS (
SELECT O.*, P.PROMO, P.DESCR, C.NAME FROM @ORDERS O 
INNER JOIN @PRODUCTS P ON O.ID_P = P.ID 
INNER JOIN @CUSTOMERS C ON O.ID_C = C.ID 
) 
SELECT DISTINCT T.PROMO, 
    STUFF(Product.Descrs,1,1,'') products, 
    STUFF(Customer.Names,1,1,'') CUSTOMERS 
FROM CTE T 
    CROSS APPLY (SELECT DISTINCT ',' + T1.DESCR FROM CTE T1 WHERE T.PROMO = T1.PROMO FOR XML PATH('')) AS Product(Descrs) 
    CROSS APPLY (SELECT DISTINCT ',' + T1.NAME FROM CTE T1 WHERE T.PROMO = T1.PROMO FOR XML PATH('')) AS Customer(Names) 

结果:

PROMO  products  CUSTOMERS     
---------- -------------- ------------------------- 
gold  prod1,prod2 Alice,Bob,Charlie 
silver  prod3   Alice 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)