[sql server] 问题总结5- 根据子分类的要求，提取父分类

现在有一个统计问题，如下
列1 列2
A a
A b
A c
A d
A e
A f
A g
B b
B c
B d
B e
B f
B g
C c
C d
C e
C f
C g
C a
C m


求在列2中既有a也有b也有C的列1的个数  注意 是 3个都必须有的

 

 

----------------------------------------------

 

 

 

if object_id('test1') is not null drop table test1
create table test1
(
    [c1] varchar(10) not null,
    [c2] varchar(10) not null
);
insert into test1 
select 'A','a' union all
select 'A','b' union all
select 'A','c' union all
select 'A','d' union all
select 'A','e' union all
select 'A','f' union all
select 'A','g' union all
select 'B','b' union all
select 'B','c' union all
select 'B','d' union all
select 'B','e' union all
select 'B','f' union all
select 'B','g' union all
select 'C','c' union all
select 'C','d' union all
select 'C','e' union all
select 'C','f' union all
select 'C','g' union all
select 'C','a' union all
select 'C','m'

方法1：
select count(*) from (
select count(c1) as 'num' from
(
select distinct  * from test1 where c2 in('a','b','c')
) as a
group by a.c1 having(count(  c2)>=3)
) as b

drop table  test1

 

 方法2：

with cr
as
(
select 
(
       select  COUNT(distinct c2)  from test1 as t2 where t2.c1= t1.c1 and c2 in('a','b','c') 

)   as num
 from test1 as t1 group by c1

)

select count(*) as [count] from cr WHERE num >=3

 

 

==================================================================

 

 

 

 

表table_A:
  路线代码 起点 终点 起点桩号 终点桩号  
  Y440608 大湾 石柱 0.56 2.35  
  Y440608 石柱 观音桥 2.35 8.52
  Y440608 观音桥 芦溪 8.52 11.98
  Y440605 龙桥 磙子河 36.58 56.32

现在要写一条sql 让结果这样
  路线代码 起点 终点 起点桩号 终点桩号  
  Y440608 大湾 芦溪 0.56 11.98
  Y440605 龙桥 磙子河 36.58 56.32

就是按照[ 路线代码]分组后只取一条结果,这个结果是经过处理了的

 

--------------------------------------

 

 

 

create  table tab(路线代码 varchar(50),起点 varchar(50),终点 varchar(50),起点桩号 decimal(10,2),终点桩号 decimal(10,2))
insert into tab select 'Y440608' ,'大湾', '石柱' ,0.56 ,2.35 union all
 select 'Y440608', '石柱' ,'观音桥', 2.35, 8.52 union all
 select 'Y440608', '观音桥', '芦溪', 8.52 ,11.98 union all
 select 'Y440605', '龙桥' ,'磙子河' ,36.58, 56.32

 

select * from tab

select
 distinct [路线代码], 
(
select [起点] from tab as qt  where qt.[路线代码] =tt.[路线代码] and qt.[起点] not in
(select [终点] from tab where [路线代码]=tt.[路线代码]  )

)   AS '起点',
(
select [终点] from tab as qt  where qt.[路线代码] =tt.[路线代码] and qt.[终点] not in
(select [起点] from tab where [路线代码]=tt.[路线代码]  )

)   AS '终点',
(
select [起点桩号] from tab as qt  where qt.[路线代码] =tt.[路线代码] and qt.[起点] not in
(select [终点] from tab where [路线代码]=tt.[路线代码]  )

)   AS '起点桩号',

(
select [终点桩号] from tab as qt  where qt.[路线代码] =tt.[路线代码] and qt.[终点] not in
(select [起点] from tab where [路线代码]=tt.[路线代码]  )

)   AS '终点桩号'

FROM tab as tt