数据库 ms sql 2008 我想实现这样的效果
create table table1
(
id int,
name varchar(10)
)insert into table1 values(1,'aa')
insert into table1 values(1,'bb')
insert into table1 values(2,'aa')
insert into table1 values(2,'bb')
insert into table1 values(2,'aa')
insert into table1 values(3,'bb')
insert into table1 values(3,'bb')
insert into table1 values(3,'aa')
insert into table1 values(4,'aa')
insert into table1 values(4,'bb')
insert into table1 values(4,'cc')
insert into table1 values(4,'aa')我要得到name 类型小于等于两种的id
如 最后就要
1
2
3
create table table1
(
id int,
name varchar(10)
)insert into table1 values(1,'aa')
insert into table1 values(1,'bb')
insert into table1 values(2,'aa')
insert into table1 values(2,'bb')
insert into table1 values(2,'aa')
insert into table1 values(3,'bb')
insert into table1 values(3,'bb')
insert into table1 values(3,'aa')
insert into table1 values(4,'aa')
insert into table1 values(4,'bb')
insert into table1 values(4,'cc')
insert into table1 values(4,'aa')我要得到name 类型小于等于两种的id
如 最后就要
1
2
3
create table table1
(
id int,
name varchar(10)
)insert into table1 values(1,'aa')
insert into table1 values(1,'bb')
insert into table1 values(2,'aa')
insert into table1 values(2,'bb')
insert into table1 values(2,'aa')
insert into table1 values(3,'bb')
insert into table1 values(3,'bb')
insert into table1 values(3,'aa')
insert into table1 values(4,'aa')
insert into table1 values(4,'bb')
insert into table1 values(4,'cc')
insert into table1 values(4,'aa')
goselect id
from (select distinct id,name from table1)t
group by id
having count(*) <= 2drop table table1
/*
id
-----------
1
2
3(3 行受影响)
select id
from table1
group by id
having count(distinct name) <= 2--看差了。
create table table1
(
id int,
name varchar(10)
)insert into table1 values(1,'aa')
insert into table1 values(1,'bb')
insert into table1 values(2,'aa')
insert into table1 values(2,'bb')
insert into table1 values(2,'aa')
insert into table1 values(3,'bb')
insert into table1 values(3,'bb')
insert into table1 values(3,'aa')
insert into table1 values(4,'aa')
insert into table1 values(4,'bb')
insert into table1 values(4,'cc')
insert into table1 values(4,'aa')SELECT * FROM table1SELECT COUNT(ID) IC, NAME FROM table1 GROUP BY name
/*
结果 IC NAME
6 AA
5 BB
1 CC
*/