sql 高级查询

Question

  表（1）
main_no           yl_code            num     weight    yl_code    RCP200908040001 20700109080400001 3 2.0 10100309080400001   (1)RCP200908040001 20700109080400001 23 43.0 10100709080400001   (2) RCP200908040001 20700109080400001 1 3.0 10100809080400001   (3)RCP200908060001 20400109080500001 1 23.0 10100409080500001    (4)RCP200908060003 21000109080600001 2 32.0 10100709080600001    (5)
表（2）main_no           yl_code            num     weight    yl_code      RCP200908040001 20700109080400001 23 43.0 10100709080400001   (2)    RCP200908040001 20700109080400001 23 43.0 10100709080400001   (2) RCP200908060003 21000109080600001 2 32.0 10100709080600001    (5)
问题:如何将表1里的数据查询出表2

Answer 1

多了一条吧
select * from tb t where not exists(select 1 from tb where main_no=t.main_no and num>t.num)

Answer 2

select *
from tb where not exists (select 1 from tb where main_no=t.main_no and num>t.num)

Answer 3

--猜的
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-06 13:51:02
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([main_no] varchar(15),[yl_code1] bigint,[num] int,[weight] numeric(3,1),[yl_code] bigint,[C6] varchar(3))
insert [tb]
select 'RCP200908040001',20700109080400001,3,2.0,10100309080400001,'(1)' union all
select 'RCP200908040001',20700109080400001,23,43.0,10100709080400001,'(2)' union all
select 'RCP200908040001',20700109080400001,1,3.0,10100809080400001,'(3)' union all
select 'RCP200908060001',20400109080500001,1,23.0,10100409080500001,'(4)' union all
select 'RCP200908060003',21000109080600001,2,32.0,10100709080600001,'(5)'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where main_no=t.main_no and num>t.num)
----------------结果----------------------------
/*main_no         yl_code1             num         weight yl_code              C6   
--------------- -------------------- ----------- ------ -------------------- ---- 
RCP200908040001 20700109080400001    23          43.0   10100709080400001    (2)
RCP200908060001 20400109080500001    1           23.0   10100409080500001    (4)
RCP200908060003 21000109080600001    2           32.0   10100709080600001    (5)（所影响的行数为 3 行）
*/