现在有一个统计问题，如下列1 列2A aA bA cA dA eA fA gB bB cB dB eB fB gC cC dC eC fC gC aC m求在列2中既有a也有b也有C的列1的个数 注意 是 3个都必须有的
----------------------------------------------
if object_id('test1') is not null drop table test1create table test1( [c1] varchar(10) not null, [c2] varchar(10) not null);insert into test1 select 'A','a' union allselect 'A','b' union allselect 'A','c' union allselect 'A','d' union allselect 'A','e' union allselect 'A','f' union allselect 'A','g' union allselect 'B','b' union allselect 'B','c' union allselect 'B','d' union allselect 'B','e' union allselect 'B','f' union allselect 'B','g' union allselect 'C','c' union allselect 'C','d' union allselect 'C','e' union allselect 'C','f' union allselect 'C','g' union allselect 'C','a' union allselect 'C','m'
方法1：select count(*) from (select count(c1) as 'num' from (select distinct * from test1 where c2 in('a','b','c')) as agroup by a.c1 having(count( c2)>=3)) as bdrop table test1
方法2：
with cras(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
