显示已为该类别中的多部电影颁发的客户 [英] Display the customer who has issued for more than one movie from that category
问题描述
实际上这是在mysql中,我无法选择mysql标签。
waq显示customerid,cusname,contactno,发给每个客户的电影数量
和类别。显示已发出超过
一部电影的客户从该caregory.display电话号码+ 91-987-654-3210。
在下面的链接中找到ER图
https://drive.google.com/open?id=0Bxich9-8Q8B5dVhXS25nWEZtOUE&authuser=0
Customer_master
CUS001 AMIT 9876543210 ADD1 2012-02-12 21
CUS002 ABDHUL 8765432109 ADD2 2012-02-12 21
CUS003 GAYAN 7654321098 ADD3 2012-02-12 21
CUS004 RADHA 6543210987 ADD4 2012-02-12 21
CUS005 GURU ADD5 2012-02-12 21
CUS006 MOHAN 4321098765 ADD6 2012-02-12 21
CUS007 NAME7 3210987654 ADD7 2012-02-12 21
CUS008 NAME8 2109876543 ADD8 2013-02-12 21
CUS009 NAME9 ADD9 2013-02-12 21
CUS010 NAM10 9934567890 ADD10 2013 -02-12 21
CUS011 NAM11 9875678910 ADD11 2013-02-12 21
Customer_issue_details
IS001 CUS001 MV001 2012-05-13 2012-05-13 2012-05-13
IS002 CUS001 MV001 2012-05-01 2012-05-16 2012-05-16
IS003 CUS002 MV004 2012-05-06 2012-05-06 2012-05-06
IS004 CUS002 MV004 2012-04-03 2012 -04-16 2012-04-20
IS005 CUS002 MV009 2012-04-04 2012-04-16 2012-04-20
IS006 CUS003 MV002 2012-03-30 2012-04-15 2012- 04-20
IS007 CUS003 MV003 2012-04-20 2012-05-05 2012-05-05
IS008 CUS003 MV005 2012-04-21 2012-05-07 2012-05-25
IS009 CUS003 MV001 2012-04-22 2012-05-07 2012-05-25
IS010 CUS003 MV009 2012-04-22 2012-05-07 2012-05-25
IS011 CUS003 MV010 2012- 04-23 2012-05-07 2012-05-25
IS012 CUS003 MV010 2012-04-24 2012-05-07 2012-05-25
IS013 CUS003 MV008 2012-04-25 2012-05 -07 2012-05-25
IS014 CUS004 MV007 2012-04-26 2012-05-07 2012-05-25
IS015 CUS004 MV006 2012-04-27 2012-05-07 2012-05- 25
IS016 CUS004 MV006 2012-04-28 2012-05-07 2012-05-25
IS017 CUS004 MV001 2012- 04-05 2012-05-07 2012-05-25
IS018 CUS010 MV008 2012-04-24 2012-05-07 2012-05-25
IS019 CUS011 MV009 2012-04-27 2012-05 -07 2012-05-25
Movie_Master
MV001 DIEHARD 2012 -05-13中文4 2HRS U / A ACTION DIR1 L1 L2 100
MV002 MATRIX 2012-05-13中文4 2HRS行动DIR2 L1 L2 100
MV003 INCEPTION 2012-05-13中文4 2HRS U / A ACTION DIR3 L1 L2 100
MV004黑暗骑士2012-05-13中文4 2HRS行动DIR4 L1 L2 100
MV005 OFFICE S 2012-05-13中文4 2HRS U / A COMEDY DIR5 L1 L2 100
MV006死亡之战2012-05-13中文4 2HRS U / A COMEDY DIR6 L1 L2 100
MV007年轻英国2012-05-13中文4 2HRS U / A COMEDY DIR7 L1 L2 100
MV008 CAS 2012-05-13中文4 2HRS A ROMANCE DIR8 L1 L2 100
MV009 GWW 2012-05-13中文4 2HRS A ROMANCE DIR9 L1 L2 100
MV010 TITANIC 2012-05-13中文4 2HRS A ROMANCE DIR10 L1 L2 100
MV011注意事项2012-05-13中文4 2HRS A RO MANCE DIR11 L1 L2 100
从下面的评论中复制的其他信息
< pre lang =SQL> 选择 m.customer_id,customer_name,
cast(concat('' + 91 - ',substring(contact_no, 1 , 3 ),' - ',substring(contact_no, 4 , 3 ),' - ',
substring(contact_no, 7 )) as char ) as contact_no,
count(i.movie_id) as no_of_movies,movie_category
来自 customer_master m
join c ustomer_issue_details i
on m.customer_id = i.customer_id
join movies_master mv
on i.movie_id = mv.movie_id
group by m.customer_id
count(m.customer_id)> 1;
试试这个:
SELECT cud .CustomerID,cud.CountOfIssues,cum。< OtherFields>,' + 91-987-654-3210' 作为 EmergencyPhoneNo
FROM (
SELECT CustomerID,COUNT(MovieId)作为 CountOfIssues
FROM Customer_issue_details
GROUP BY CustomerID
HAVING CountOfIssues> 1
) AS cud INNER JOIN Customer_master AS cum ON cud.CustomerID = cum.CustomerID
用相应的字段列表替换cum。< OtherFields>
,例如:cum.Name,cum .SurName
等
如需了解更多信息,请参阅:
处理分组依据 [ ^ ]和 HAVING [ ^ ]
选择 [ ^ ]
ALIAS [ ^ ]
COUNT(X)
GROUP BY X
有COUNT(X )无论如何
....潜入SQL并且不仅仅是懒惰而且要求解决方案
Actually this is in mysql and i am not able to select mysql tag.
waq to display customerid,cusname,contactno,num of movies issued to each customer
and category. display the customer who has issued for more than
one movie from that caregory.display phone num as "+91-987-654-3210".
find the ER diagram in below link
https://drive.google.com/open?id=0Bxich9-8Q8B5dVhXS25nWEZtOUE&authuser=0
Customer_master
CUS001 AMIT 9876543210 ADD1 2012-02-12 21
CUS002 ABDHUL 8765432109 ADD2 2012-02-12 21
CUS003 GAYAN 7654321098 ADD3 2012-02-12 21
CUS004 RADHA 6543210987 ADD4 2012-02-12 21
CUS005 GURU ADD5 2012-02-12 21
CUS006 MOHAN 4321098765 ADD6 2012-02-12 21
CUS007 NAME7 3210987654 ADD7 2012-02-12 21
CUS008 NAME8 2109876543 ADD8 2013-02-12 21
CUS009 NAME9 ADD9 2013-02-12 21
CUS010 NAM10 9934567890 ADD10 2013-02-12 21
CUS011 NAM11 9875678910 ADD11 2013-02-12 21
Customer_issue_details
IS001 CUS001 MV001 2012-05-13 2012-05-13 2012-05-13
IS002 CUS001 MV001 2012-05-01 2012-05-16 2012-05-16
IS003 CUS002 MV004 2012-05-02 2012-05-06 2012-05-16
IS004 CUS002 MV004 2012-04-03 2012-04-16 2012-04-20
IS005 CUS002 MV009 2012-04-04 2012-04-16 2012-04-20
IS006 CUS003 MV002 2012-03-30 2012-04-15 2012-04-20
IS007 CUS003 MV003 2012-04-20 2012-05-05 2012-05-05
IS008 CUS003 MV005 2012-04-21 2012-05-07 2012-05-25
IS009 CUS003 MV001 2012-04-22 2012-05-07 2012-05-25
IS010 CUS003 MV009 2012-04-22 2012-05-07 2012-05-25
IS011 CUS003 MV010 2012-04-23 2012-05-07 2012-05-25
IS012 CUS003 MV010 2012-04-24 2012-05-07 2012-05-25
IS013 CUS003 MV008 2012-04-25 2012-05-07 2012-05-25
IS014 CUS004 MV007 2012-04-26 2012-05-07 2012-05-25
IS015 CUS004 MV006 2012-04-27 2012-05-07 2012-05-25
IS016 CUS004 MV006 2012-04-28 2012-05-07 2012-05-25
IS017 CUS004 MV001 2012-04-29 2012-05-07 2012-05-25
IS018 CUS010 MV008 2012-04-24 2012-05-07 2012-05-25
IS019 CUS011 MV009 2012-04-27 2012-05-07 2012-05-25
Movie_Master
MV001 DIEHARD 2012-05-13 ENGLISH 4 2HRS U/A ACTION DIR1 L1 L2 100
MV002 THE MATRIX 2012-05-13 ENGLISH 4 2HRS A ACTION DIR2 L1 L2 100
MV003 INCEPTION 2012-05-13 ENGLISH 4 2HRS U/A ACTION DIR3 L1 L2 100
MV004 DARK KNIGHT 2012-05-13 ENGLISH 4 2HRS A ACTION DIR4 L1 L2 100
MV005 OFFICE S 2012-05-13 ENGLISH 4 2HRS U/A COMEDY DIR5 L1 L2 100
MV006 SHAWN OF DEAD 2012-05-13 ENGLISH 4 2HRS U/A COMEDY DIR6 L1 L2 100
MV007 YOUNG FRANKEN 2012-05-13 ENGLISH 4 2HRS U/A COMEDY DIR7 L1 L2 100
MV008 CAS 2012-05-13 ENGLISH 4 2HRS A ROMANCE DIR8 L1 L2 100
MV009 GWW 2012-05-13 ENGLISH 4 2HRS A ROMANCE DIR9 L1 L2 100
MV010 TITANIC 2012-05-13 ENGLISH 4 2HRS A ROMANCE DIR10 L1 L2 100
MV011 THE NOTE BOOK 2012-05-13 ENGLISH 4 2HRS A ROMANCE DIR11 L1 L2 100
additional information copied from comment below
select m.customer_id,customer_name,
cast(concat('+91-',substring(contact_no,1,3),'-',substring(contact_no,4,3),'-',
substring(contact_no,7) ) as char)as contact_no,
count(i.movie_id) as no_of_movies,movie_category
from customer_master m
join customer_issue_details i
on m.customer_id = i.customer_id
join movies_master mv
on i.movie_id = mv.movie_id
group by m.customer_id
having count(m.customer_id) >1;
Try this:
SELECT cud.CustomerID, cud.CountOfIssues, cum.<OtherFields>, '+91-987-654-3210' As EmergencyPhoneNo FROM ( SELECT CustomerID, COUNT(MovieId) As CountOfIssues FROM Customer_issue_details GROUP BY CustomerID HAVING CountOfIssues>1 ) AS cud INNER JOIN Customer_master AS cum ON cud.CustomerID = cum.CustomerID
Replacecum.<OtherFields>
with corresponding field list, such as:cum.Name, cum.SurName
, etc.
For further information, please see:
HANDLING GROUP BY[^] and HAVING[^]
SELECT[^]
ALIAS[^]
COUNT (X)
GROUP BY X
HAVING COUNT(X) "whatever"
....dive into SQL and don't be only lazy and ask for the solution
这篇关于显示已为该类别中的多部电影颁发的客户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!