Sql server
设我有一张表,字段名称为id,name,school,type
假设有数据如下,其中name列,type列有重复数据,type为1、2、3
1,name1,school1,2
2,name1,school2,2
3,name2,school2,2
4,name2,school2,3
...
想经过查询得到以下数据:
1,对于name相同,但type不同的记录取出来
2,对于name相同,但type相同的记录只留一条
上面两个sql如何写啊?先谢谢了。
设我有一张表,字段名称为id,name,school,type
假设有数据如下,其中name列,type列有重复数据,type为1、2、3
1,name1,school1,2
2,name1,school2,2
3,name2,school2,2
4,name2,school2,3
...
想经过查询得到以下数据:
1,对于name相同,但type不同的记录取出来
2,对于name相同,但type相同的记录只留一条
上面两个sql如何写啊?先谢谢了。
insert sch select 1,'name1','school1',2
union all select 2,'name1','school2',2
union all select 3,'name2','school2',2
union all select 4,'name2','school2',3
select DISTINCT names,type from schdrop table sch
1,name1,school1,2
2,name1,school2,2
3,name2,school2,2
4,name2,school2,3select * from #a b
where type=(select top 1 type from #a where name=b.name)你这两SQL有何区别?
insert sch select 1,'name1','school1',2
union all select 2,'name1','school2',2
union all select 3,'name2','school2',2
union all select 4,'name2','school2',3
select * from sch where id in( select max(id) from sch group by names,type)drop table sch
from tablename
group by name,type
create table sch (id int,names varchar(200),school varchar(200), type int)
insert sch select 1,'name1','school1',2
union all select 2,'name1','school2',2
union all select 3,'name2','school2',2
union all select 4,'name2','school2',3
--1,对于name相同,但type不同的记录取出来
select names,type from sch
group by names,type
having count(*)<=1
--2,对于name相同,但type相同的记录只留一条
select names,type from sch
group by names,type
select a.* from sch a ,sch b where a.id<>b.ID and a.names=b.names and a.type<>b.type
2,对于name相同,但type相同的记录只留一条
select a.* from sch a ,sch b where a.id<>b.ID and a.names=b.names and a.type=b.type
假设有数据如下,其中name列,type列有重复数据,type为1、2、3
1,name1,school1,2
2,name1,school2,2
3,name2,school2,2
4,name2,school2,3
...
想经过查询得到以下数据:
1,对于name相同,但type不同的记录取出来
select * from tableName A
inner join (
select name,min(type) type from tableName group by name
)B
on A.name=B.name
where A.type<>B.type
2,对于name相同,但type相同的记录只留一条
上面两个sql如何写啊?select name, type from tableName group by name,type
设我有一张表,字段名称为id,name,school,type
假设有数据如下,其中name列,type列有重复数据,type为1、2、3
1,name1,school1,2
2,name1,school2,2
3,name2,school2,2
4,name2,school2,3
...
想经过查询得到以下数据:
1,对于name相同,但type不同的记录取出来
select * from tablename
where (name,type) in (select name,type from tablename
group by name,type having count(id)=1)2,对于name相同,但type相同的记录只留一条
这里就有点不明白了,你要求结果是显示4个字段,还是只显示name,type字段呢?
如是后面的,则用
select name,type from tablename
group by name,type having count(id)>1
delete sch where id in(select min([id]) from sch as a where names=a.names and type=a.type)