如何从SQL开发人员db中的表中获取帐户，该开发人员db在同一帐户上有多个包。 [英] How to fetch the account from table in SQL developer db which has multiple packages on same account.

问题描述

我想从数据库中获取帐户，该数据库具有除电视之外的多个包，例如

表T1

账户套餐

111电视

111宽带

111手机

112电视

112宽带

112 STB

112手机

115固定电话

115宽带

115 STB

115手机

如何从上表中获取不包含的账户有包电视吗？



我尝试过：

从T1中选择账户，项目，其中项目不在（'电视'）
和账户不在（从T1选择不同的账户，其中item =（'TV'））; 

解决方案

帖子中的问题与标题不符，所以希望以下其中一项是你实际所追求的。



要获得没有电视套餐的帐户 - 你的尝试非常接近 -

 选择  DISTINCT 帐户来自 T1 其中帐户 NOT   IN （ select   distinct 帐户来自 T1 其中 item =（'  TV'））; 

要获取具有多个包的帐户 -

 选择帐户来自 T1  GROUP   BY 帐户 HAVING  COUNT（item）>  1  

以表格格式获取每个帐户的包裹 -

  SELECT  *  FROM  
（选择帐户，项目 FROM  T1）qry 
 PIVOT（count（Item） for  Item  IN （电视，宽带，移动，机顶盒，固定电话））pvt 

这将产生以下结果

 账户电视宽带移动STB固定电话 
 111 1 1 1 0 0 
 112 1 1 1 1 0 
 115 0 1 1 1 1 

显然，如果你想列出不要的账户然后只添加

  WHERE  TV =  0  

要以逗号分隔列表的形式获取每个帐户的包，请执行以下操作：

  SELECT   DISTINCT  a.Ac count，
 Packages = STUFF（（ SELECT  ' ，' + b.Item  FROM  T1 b 
  WHERE  a.Account = b.Account  FOR  XML路径（' '））， 1 ， 1 ，' '）
  FROM  T1 a 

或者如果可以使用STUFF / FOR XML尝试

  SELECT 帐户， CASE   WHEN  TV =  1  那么 '  TV，'  ELSE   <跨度class =code-string>'  END  + 
  CASE   WHEN  broadband =  1  那么 '  Broadband，'  ELSE  ' '  END  + 
  CASE   WHEN  mobile =  1  那么 '  Mobile，'  ELSE  ' '  END  + 
  CASE   WHEN  STB =  1   THEN  '  STB，'  ELSE  ' '  END  + 
  CASE   WHEN 固定电话=  1  那么 ' 固定电话，'   ELSE  ' ' < span class =code-keyword> END   AS 包
  FROM  
（选择帐户，项 FROM  T1）qry 
 PIVOT（count（Item）  for 项 IN （电视，宽带，手机，机顶盒，固定电话））pvt 

给出结果

账户套餐
 111电视，宽带，手机，
 112电视，宽带，手机，机顶盒，
 115宽带，移动，机顶盒，固定电话，

i want to fetch account from the db which has multiple packages other than TV e.g.
Table T1
account Packages
111 TV
111 broadband
111 mobile
112 TV
112 broadband
112 STB
112 mobile
115 Landline
115 broadband
115 STB
115 mobile
How to fetch from the above table the account which does not have package TV ??

What I have tried:

Select Account,Item from T1 where item not in('TV')
AND Account NOT IN (select distinct Accountfrom T1 where item= ('TV')); 

解决方案

The question in the post does not match the title, so hopefully one of the following is what you are actually after.

To get the Account that does not have package "TV" - your attempt was very close by the way -

Select DISTINCT Account from T1 where Account NOT IN (select distinct Account from T1 where item= ('TV')); 

To fetch the accounts that have multiple packages -

Select Account from T1 GROUP BY Account HAVING COUNT(item) > 1

To get the packages for each account in a table format -

SELECT * FROM
( select Account, Item FROM T1 ) qry
PIVOT (	count(Item) for Item IN (TV,broadband, mobile, STB, Landline) ) pvt

which would give the following result

Account TV   broadband mobile  STB     Landline
111	1	1	1	0	0
112	1	1	1	1	0
115	0	1	1	1	1

and obviously if you want to list Accounts that don't have TV in that format then just add

WHERE TV = 0

To get the packages for each Account as a comma separated list do this

SELECT DISTINCT a.Account,
	Packages = STUFF((SELECT ',' + b.Item FROM T1 b
                WHERE a.Account = b.Account FOR XML Path('')),1,1,'')
FROM T1 a

OR if you can't use STUFF/FOR XML try

SELECT Account, CASE WHEN TV = 1 THEN 'TV,' ELSE '' END + 
				CASE WHEN broadband = 1 THEN 'Broadband,' ELSE '' END +
				CASE WHEN mobile = 1 THEN 'Mobile,' ELSE '' END +
				CASE WHEN STB = 1 THEN 'STB,' ELSE '' END + 
				CASE WHEN Landline = 1 THEN 'Landline,' ELSE '' END AS Packages
FROM
( select Account, Item FROM T1 ) qry
PIVOT (	count(Item) for Item IN (TV,broadband, mobile, STB, Landline) ) pvt

which gives results

Account Packages
111	TV,Broadband,Mobile,
112	TV,Broadband,Mobile,STB,
115	Broadband,Mobile,STB,Landline,

这篇关于如何从SQL开发人员db中的表中获取帐户，该开发人员db在同一帐户上有多个包。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！