100分求一sql语句~~

[castlooo]

CREATE TABLE TableTmp(
[a] [varchar] (50),
[b] [varchar] (50)
)
GO

insert into TableTmp(a,b) values('a','1');
insert into TableTmp(a,b) values('b','1');
insert into TableTmp(a,b) values('c','1');
insert into TableTmp(a,b) values('d','1');
insert into TableTmp(a,b) values('a','5');
insert into TableTmp(a,b) values('b','5')
insert into TableTmp(a,b) values('a','6')
insert into TableTmp(a,b) values('b','6')
insert into TableTmp(a,b) values('g','7')
insert into TableTmp(a,b) values('b','7')
insert into TableTmp(a,b) values('g','8')
insert into TableTmp(a,b) values('b','8')
insert into TableTmp(a,b) values('c','8')
select * from TableTmp;
a b
----------------------------
a 1
b 1
c 1
d 1
a 5
b 5
a 6
b 6
g 7
b 7
g 8
b 8
c 8
-------------------------------
按照列b分组，暂且按照b的值命名集合
最大的集合为

a 1
b 1
c 1
d 1

而且 包含集合5中a的元素
a 5
b 5
和集合6
c 6
d 6

要求有包含关系的。
结果如下
5 1
6 1
7 8
意思是集合1 包含集合5 和集合6
8 包含7
CREATE TABLE TableTmp(
[a] [varchar] (50),
[b] [varchar] (50)
)
GO

insert into TableTmp(a,b) values('a','1');
insert into TableTmp(a,b) values('b','1');
insert into TableTmp(a,b) values('c','1');
insert into TableTmp(a,b) values('d','1');
insert into TableTmp(a,b) values('a','5');
insert into TableTmp(a,b) values('b','5')
insert into TableTmp(a,b) values('a','6')
insert into TableTmp(a,b) values('b','6')
insert into TableTmp(a,b) values('g','7')
insert into TableTmp(a,b) values('b','7')
insert into TableTmp(a,b) values('g','8')
insert into TableTmp(a,b) values('b','8')
insert into TableTmp(a,b) values('c','8')
select * from TableTmp;
a b
----------------------------
a 1
b 1
c 1
d 1
a 5
b 5
a 6
b 6
g 7
b 7
g 8
b 8
c 8
-------------------------------
按照列b分组，暂且按照b的值命名集合
最大的集合为

a 1
b 1
c 1
d 1

而且 包含集合5中a的元素
a 5
b 5
和集合6
c 6
d 6

要求有包含关系的。
结果如下
5 1
6 1
7 8
意思是集合1 包含集合5 和集合6
8 包含7
我的表中


救我， 谢谢！

[-狙击手-]

SF,看看

[-狙击手-]

set nocount on
CREATE TABLE TableTmp( 
[a] [varchar] (50), 
[b] [varchar] (50)  
)  
GO 

insert into TableTmp(a,b) values('a','1'); 
insert into TableTmp(a,b) values('b','1'); 
insert into TableTmp(a,b) values('c','1'); 
insert into TableTmp(a,b) values('d','1'); 
insert into TableTmp(a,b) values('a','5'); 
insert into TableTmp(a,b) values('b','5') 
insert into TableTmp(a,b) values('a','6') 
insert into TableTmp(a,b) values('b','6') 
insert into TableTmp(a,b) values('g','7') 
insert into TableTmp(a,b) values('b','7') 
insert into TableTmp(a,b) values('g','8') 
insert into TableTmp(a,b) values('b','8') 
insert into TableTmp(a,b) values('c','8') 
go
select * from tabletmp
where b = 
(select top 1 b
from tabletmp
group by b
order by count(1) desc ) 



drop table tabletmp

/*
a                                                  b                                                  
-------------------------------------------------- -------------------------------------------------- 
a                                                  1
b                                                  1
c                                                  1
d                                                  1

*/

[qiuming0306]

要什么样的结果！没看懂！

[liangCK]

select * from tabletmp
where b =
(select top 1 b
from tabletmp
group by b
order by count(1) desc )

[青锋-SS]

[Quote=引用 1 楼 happyflystone 的回复:]
SF,看看
[/Quote]

[flairsky]

select * from @table
where b =
(select top 1 b
from @table
group by b
order by count(1) desc )

[kk19840210]

[Quote=引用 5 楼 sdhylj 的回复:]
引用 1 楼 happyflystone 的回复:
SF,看看
[/Quote]

[fengyunxnc]

sf 是什么意思...我也看看...

[-狙击手-]

结果 是啥

[昵称被占用了]

CREATE TABLE TableTmp( 
[a] [varchar] (50), 
[b] [varchar] (50)  
)  
GO 

insert into TableTmp(a,b) values('a','1'); 
insert into TableTmp(a,b) values('b','1'); 
insert into TableTmp(a,b) values('c','1'); 
insert into TableTmp(a,b) values('d','1'); 
insert into TableTmp(a,b) values('a','5'); 
insert into TableTmp(a,b) values('b','5') 
insert into TableTmp(a,b) values('a','6') 
insert into TableTmp(a,b) values('b','6') 
insert into TableTmp(a,b) values('g','7') 
insert into TableTmp(a,b) values('b','7') 
insert into TableTmp(a,b) values('g','8') 
insert into TableTmp(a,b) values('b','8') 
insert into TableTmp(a,b) values('c','8') 
go

--查询
select distinct a.b,b.b
from TableTmp a,TableTmp b
where a.b<>b.b
and a.a=b.a
and (select count(1) from TableTmp where b=a.b)>(select count(1) from TableTmp where b=b.b)
and (select count(1) from TableTmp c where b=b.b and a<>b.a and not exists (
  select 1 from TableTmp where a=c.a and b=a.b)
)=0

--结果
b                                                  b                                                  
-------------------------------------------------- -------------------------------------------------- 
1                                                  5
1                                                  6
8                                                  7

（所影响的行数为 3 行）

[昵称被占用了]

结果与搂主的反了

--查询
select distinct b.b,a.b
from TableTmp a,TableTmp b
where a.b<>b.b
and a.a=b.a
and (select count(1) from TableTmp where b=a.b)>(select count(1) from TableTmp where b=b.b)
and (select count(1) from TableTmp c where b=b.b and a<>b.a and not exists (
  select 1 from TableTmp where a=c.a and b=a.b)
)=0

[castlooo]

结果如下
5 1
6 1
7 8
不好意思 发了两遍

[-狙击手-]

集合6
c 6
d 6

－－－

这是哪来的

[sdlwhgw]

--应该是这个吧？
a                                                  1
b                                                  1
c                                                  1
d                                                  1
g                                                  8
b                                                  8
c                                                  8

[castlooo]

[Quote=引用 11 楼 Haiwer 的回复:]
结果与搂主的反了



SQL code--查询
select distinct b.b,a.b
from TableTmp a,TableTmp b
where a.b<>b.b
and a.a=b.a
and (select count(1) from TableTmp where b=a.b)>(select count(1) from TableTmp where b=b.b)
and (select count(1) from TableTmp c where b=b.b and a<>b.a and not exists (
select 1 from TableTmp where a=c.a and b=a.b)
)=0
[/Quote]


没反 ，就这个
能不能给解释下?

[昵称被占用了]

语句就别解释了吧

基本按照你的思路，用sql语句描述出来

[昵称被占用了]

and (select count(1) from TableTmp where b=a.b)>(select count(1) from TableTmp where b=b.b)
就是集合a>集合B的意思

and (select count(1) from TableTmp c where b=b.b and a <>b.a and not exists (
select 1 from TableTmp where a=c.a and b=a.b)
)=0
就是集合B中不能出现集合a外的数据

[castlooo]

ok
结贴，给分
海阔天空，谢了！