显示已为该类别中的多部电影颁发的客户 [英] Display the customer who has issued for more than one movie from that category

查看：109
本文介绍了显示已为该类别中的多部电影颁发的客户的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

实际上这是在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.movi​​e_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.movi​​e_id = mv.movi​​e_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



Replace cum.<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屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆