I have 3 tables
我有3张桌子
- Customers( cid,name,address)
- Newspaper( nid, name , price)
- Weeklymag ( wid, name , price)
- Orders( id , cid , nid, wid)
报纸(nid,名称,价格)
Weeklymag(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
0
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
0
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
0
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
0
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
0
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
0
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.