table1的id全部会在table2种出现么?select id,max(num),result
from table1
where id in (select id from table2)
group by id,result
from table1
where id in (select id from table2)
group by id,result
解决方案 »
- 求:通俗解释关系数据库中的候选关键字的意思
- SQLSERVER 问题?
- 存储过程的多重循环欠套,很难
- WIN 7旗舰版SQL SERVER 2008安装问题
- HelpMe!有没有可能可以实现这样的触发器?欢迎大家进来一起讨论!
- 比较菜的问题,请帮忙!
- 请问,server2000数据库不支持短日期格式吗,为什么我从ASSECE导入数据总是提示溢出呢?
- 找一条语句
- 变量定义的时候超过8000,如何处理!!
- 关于SqlServer7.0 数据倒入 数据库,从别的 电脑拷贝过来的 code_data.mdf 和 code_log.ldf
- 能否在两个操作系统之间共享一个MS-SQL Server?
- 数据库别名的问题
Insert into table1
select '1','11','aa'
union all select '1','22','gg'
union all select '1','14','ii'
union all select '2','13','bb'
union all select '3','14','cc'
union all select '3','05','kk'
union all select '4','11','dd'select a.* from table1 a where num in(select max(num) from table1 where id=a.id) order by id--結果
id num result
---------------------
1 22 gg
2 13 bb
3 14 cc
4 11 dd
(
select * from table1 a join
(select id,max(num)as num from table1 group by id)b
on a.id=b.id and a.num=b.num
)b on a.id=b.id where b.id is not null order by a.id
Insert into table1
select '1','11','aa'
union all select '1','22','gg'
union all select '1','14','ii'
union all select '2','13','bb'
union all select '3','14','cc'
union all select '3','05','kk'
union all select '4','11','dd'select a.* from table1 a,(select id,num=max(num) from table1 group by id)b
where a.id=b.id and a.num=b.num
order by a.id
--結果
id num result
---------------------
1 22 gg
2 13 bb
3 14 cc
4 11 dd
Insert into table1
select '1','11','aa'
union all select '1','22','gg'
union all select '1','14','ii'
union all select '2','13','bb'
union all select '3','14','cc'
union all select '3','05','kk'
union all select '4','11','dd'
create table table2(id int)
insert into table2 select 1
insert into table2 select 3
insert into table2 select 4select b.* from table2 a left join
(
select a.* from table1 a join
(select id,max(num)as num from table1 group by id)b
on a.id=b.id and a.num=b.num
)b on a.id=b.id where b.id is not null order by a.iddrop table table1
drop table table2
Insert into table1
select '1','11','aa'
union all select '1','22','gg'
union all select '1','14','ii'
union all select '2','13','bb'
union all select '3','14','cc'
union all select '3','05','kk'
union all select '4','11','dd'create table table2(id int)
Insert into table2
select '1'
union all select '3'
union all select '4'select b.* from table2 a,(select a.* from table1 a where num in(select max(num) from table1 where id=a.id))b
where a.id=b.id--結果
id num result
---------------------
1 22 gg
3 14 cc
4 11 dd
Insert into table1
select '1','11','aa'
union all select '1','22','gg'
union all select '1','14','ii'
union all select '2','13','bb'
union all select '3','14','cc'
union all select '3','05','kk'
union all select '4','11','dd'create table table2(id int)insert into table2
select 1 union all
select 3 union all
select 4 select * from table1 a join table2 b on a.id=b.id
where not exists(select * from table1 where num>a.num and id=a.id)
order by a.id
-----------------------------------------------------------------
1 22 gg 1
3 14 cc 3
4 11 dd 4(所影响的行数为 3 行)
select a.* from table1 a join table2 b on a.id=b.id
where not exists(select * from table1 where num>a.num and id=a.id)
order by a.id
-----------------------------------------------------------------
1 22 gg
3 14 cc
4 11 dd
from table1 a,
(
select id,max(num) as num
from table1
group by id) b
where a.id=b.id and a.num=b.num
order by a.id