--测试--测试数据 create table 表A(字段1 int,字段2 varchar(10),字段3 varchar(10),字段4 int) insert 表A select 1,'*','*',0 union all select 1,'*','*',1 union all select 2,'*','*',3 union all select 3,'*','*',2 union all select 3,'*','*',4 union all select 4,'*','*',0 union all select 5,'*','*',0 union all select 6,'*','*',2 union all select 6,'*','*',3 go--查询 select a.* from 表A a,(select 字段1,字段4=min(字段4) from 表A group by 字段1)b where a.字段1=b.字段1 and a.字段4=b.字段4 go--删除测试 drop table 表A/*--测试结果字段1 字段2 字段3 字段4 ----------- ---------- ---------- ----------- 1 * * 0 2 * * 3 3 * * 2 4 * * 0 5 * * 0 6 * * 2(所影响的行数为 6 行) --*/
create table test(a int, b int, c int, d int) insert into test select 1,0,0,0 union select 2,0,0,3 union select 3,0,0,2 union select 3,0,0,4 union select 4,0,0,0 union select 5,0,0,0 union select 6,0,0,2 union select 6,0,0,3 go select a,b,c ,max(d) from test group by a,b,c
--测试数据 create table 表A(字段1 int,字段2 varchar(10),字段3 varchar(10),字段4 int) insert 表A select 1,'*','*',0 union all select 1,'*','*',1 union all select 2,'*','*',3 union all select 3,'*','*',2 union all select 3,'*','*',2 --这里的数据相同会怎么样 union all select 4,'*','*',0 union all select 5,'*','*',0 union all select 6,'*','*',2 union all select 6,'*','*',3 go
create table 表A(字段1 int,字段2 varchar(10),字段3 varchar(10),字段4 int)
insert 表A select 1,'*','*',0
union all select 1,'*','*',1
union all select 2,'*','*',3
union all select 3,'*','*',2
union all select 3,'*','*',4
union all select 4,'*','*',0
union all select 5,'*','*',0
union all select 6,'*','*',2
union all select 6,'*','*',3
go--查询
select a.*
from 表A a,(select 字段1,字段4=min(字段4) from 表A group by 字段1)b
where a.字段1=b.字段1 and a.字段4=b.字段4
go--删除测试
drop table 表A/*--测试结果字段1 字段2 字段3 字段4
----------- ---------- ---------- -----------
1 * * 0
2 * * 3
3 * * 2
4 * * 0
5 * * 0
6 * * 2(所影响的行数为 6 行)
--*/
insert into test select 1,0,0,0
union select 2,0,0,3
union select 3,0,0,2
union select 3,0,0,4
union select 4,0,0,0
union select 5,0,0,0
union select 6,0,0,2
union select 6,0,0,3
go
select a,b,c ,max(d) from test group by a,b,c
create table 表A(字段1 int,字段2 varchar(10),字段3 varchar(10),字段4 int)
insert 表A select 1,'*','*',0
union all select 1,'*','*',1
union all select 2,'*','*',3
union all select 3,'*','*',2
union all select 3,'*','*',2 --这里的数据相同会怎么样
union all select 4,'*','*',0
union all select 5,'*','*',0
union all select 6,'*','*',2
union all select 6,'*','*',3
go