Sql客户用所有产品查询

I have 3 tables

我有3张桌子

Customers( cid,name,address)
Newspaper( nid, name , price)

报纸(nid,名称,价格)

Weeklymag ( wid, name , price)

Weeklymag(wid,名称,价格)

Orders( id , cid , nid, wid)

订单(id,cid,nid,wid)

Now one customer has more than two newspaper or magazine . I am looking for query for following result

现在一个客户有两个以上的报纸或杂志。我正在寻找以下结果的查询

john--news1,news2,weeklymag1

King--news1

Jessel--news2,mag1,weeklymag2

Smith--new4,mag3

Or do I need procedure to create this report ??

或者我是否需要创建此报告的程序?

Thanks for your time and answers .

谢谢你的时间和答案。

3 个解决方案

#1

Use the GROUP_CONCAT FUNCTION

使用GROUP_CONCAT FUNCTION

SELECT c.name, CONCAT(GROUP_CONCAT(n.name SEPARATOR ','), IF(COUNT(DISTINCT n.id)>0 AND COUNT(DISTINCT m.id) >0,',',''), GROUP_CONCAT(m.name SEPARATOR ',')) mags
FROM customers c
LEFT JOIN orders o ON o.cid = c.cid
LEFT JOIN newspaper n ON n.nid = o.nid
LEFT JOIN weeklymag m ON m.wid = o.wid
GROUP BY c.cid

#2

You could do this using GROUP CONCAT:

您可以使用GROUP CONCAT执行此操作:

SELECT c.cid, c.name, CONCAT(GROUP_CONCAT(n.name), ', ', GROUP_CONCAT(w.name))
FROM customers c
LEFT JOIN orders o ON c.cid = o.cid
LEFT JOIN newspaper n ON o.nid = n.nid
LEFT JOIN weeklymag w ON o.wid = w.wid
GROUP BY c.cid, c.name

#3

SELECT C.NAME ,

SELECT C.NAME,

    STUFF((SELECT DISTINCT' '+ N.NAME + ' | '
                FROM NEWSPAPER N   
                JOIN ORDERS O ON O.NEWS_ID = N.ID 
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as NEWS
     ,
     STUFF((SELECT DISTINCT' '+ w.name +' | '
                FROM weekly w   
                JOIN ORDERS O ON O.Weekly_id = w.id  
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as WEEKLY
    ,STUFF((SELECT DISTINCT' '+ m.name +' | '
                FROM monthly m   
                JOIN ORDERS O ON o.monthly_id  = m.id  
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as MAG

FROM CUSTOMER C

来自CUSTOMER C.

#1