select id1, id2,id4,name ,age from 表1 group by id1,id2,id4,name,age
select ditinct ID1,ID2,ID4,name,age from tableName
create table #tb ( ID1 int, ID2 int, ID3 int, ID4 int, [name] varchar(20), age int ) insert into #tb select 1,2,3,4,'zzw',50 insert into #tb select 1,2,4,4,'zzw',50 insert into #tb select 1,2,5,4,'zzw',50 insert into #tb select 1,3,3,4,'zzw',50 insert into #tb select 1,6,3,4,'zzw',50 insert into #tb select 1,6,6,4,'zzw',50select ID1,ID2,ID4,[name],age from #tb t where not exists (select * from #tb where ID2=t.ID2 and ID3>t.ID3)
create table #tb ( ID1 int, ID2 int, ID3 int, ID4 int, [name] varchar(20), age int ) insert into #tb select 1,2,3,4,'zzw',50 insert into #tb select 1,2,4,4,'zzw',50 insert into #tb select 1,2,5,4,'zzw',50 insert into #tb select 1,3,3,4,'zzw',50 insert into #tb select 1,6,3,4,'zzw',50 insert into #tb select 1,6,6,4,'zzw',50select ID1,ID2,ID4,[name],age from #tb t where not exists (select * from #tb where ID2=t.ID2 and ID3>t.ID3) ID1 ID2 ID4 name age ----------- ----------- ----------- -------------------- ----------- 1 2 4 zzw 50 1 3 4 zzw 50 1 6 4 zzw 50(3 行受影响)
DECLARE @T TABLE (ID1 int,id2 INT,id3 int,id4 int, name varchar(8),age int) INSERT INTO @T select 1,2,3,4,'zzw',50 union all select 1,2,4,4,'zzw',50 union all select 1,2,5,4,'zzw',50 union all select 1,3,3,4,'zzw',50 union all select 1,6,3,4,'zzw',50 union all select 1,6,6,4,'zzw',50select distinct id1,id2,id4,name,age from @t
?
group by id1,id2,id4,name,age
(
ID1 int,
ID2 int,
ID3 int,
ID4 int,
[name] varchar(20),
age int
)
insert into #tb select 1,2,3,4,'zzw',50
insert into #tb select 1,2,4,4,'zzw',50
insert into #tb select 1,2,5,4,'zzw',50
insert into #tb select 1,3,3,4,'zzw',50
insert into #tb select 1,6,3,4,'zzw',50
insert into #tb select 1,6,6,4,'zzw',50select ID1,ID2,ID4,[name],age from #tb t where not exists (select * from #tb where ID2=t.ID2 and ID3>t.ID3)
(
ID1 int,
ID2 int,
ID3 int,
ID4 int,
[name] varchar(20),
age int
)
insert into #tb select 1,2,3,4,'zzw',50
insert into #tb select 1,2,4,4,'zzw',50
insert into #tb select 1,2,5,4,'zzw',50
insert into #tb select 1,3,3,4,'zzw',50
insert into #tb select 1,6,3,4,'zzw',50
insert into #tb select 1,6,6,4,'zzw',50select ID1,ID2,ID4,[name],age from #tb t where not exists (select * from #tb where ID2=t.ID2 and ID3>t.ID3)
ID1 ID2 ID4 name age
----------- ----------- ----------- -------------------- -----------
1 2 4 zzw 50
1 3 4 zzw 50
1 6 4 zzw 50(3 行受影响)
INSERT INTO @T
select 1,2,3,4,'zzw',50 union all
select 1,2,4,4,'zzw',50 union all
select 1,2,5,4,'zzw',50 union all
select 1,3,3,4,'zzw',50 union all
select 1,6,3,4,'zzw',50 union all
select 1,6,6,4,'zzw',50select distinct id1,id2,id4,name,age from @t
(联合主键1) (联合主键2) (联合主键3) (联合主键4)
ID1 ID2 ID3 ID4 name age
1 2 3 4 zzw 50
1 2 4 4 zzw 50
1 2 5 4 zzw 50
1 3 3 4 zzw 50
1 6 3 4 zzw 50
1 6 6 4 zzw 50 结果:
ID1 ID2 ID4 name age
1 2 4 zzw 50
1 3 4 zzw 50
1 6 4 zzw 50 怎么样通过一条SQL姐姐问题,高手指教。select ID1,ID2,ID4,name,age from tb group by ID1,ID2,ID4,name,age
该表中有四个联合主键
(联合主键1)ID1 (联合主键2)ID2 (联合主键3)ID3 (联合主键4) ID4
其他字段name age 新增时这样的规定:ID3可多选,一次插入数据库,
也就是说如果ID3选择了3条数据,那么将会插入数据库3条记录,这三条记录只有ID3不一样,其他都一样。这样的三条记录我只要取去一条。
(1)
key1 key2 key3 key4
2 001 98 2009-04-29 00:00:00.000 2009-04-30 00:00:00.000 11.0000 11.00 11.00 11.00 02 11 11 1 1 2009-04-29 00:00:00.000 9999999 2009-04-29 00:00:00.000 9999999
(2)
3 003 98 2009-04-30 00:00:00.000 2009-04-30 00:00:00.000 11.0000 13.00 23.00 23.00 01 23 232323232 1 1 2009-04-29 00:00:00.000 9999999 2009-04-29 00:00:00.000 9999999
(3)
3 003 94 2009-04-30 00:00:00.000 2009-04-30 00:00:00.000 11.0000 13.00 23.00 23.00 01 23 232323232 1 1 2009-04-29 00:00:00.000 9999999 2009-04-29 00:00:00.000 9999999(1)是单独插入的
(2)(3)是一起插入的,只有98,94这两个字段值不一样,其他都一样。像(2)(3)这样的记录我只要一条。