现在二个tabl,如下:
tabl_a
id qty date
001 10 0101
001 3 0102
001 9 0108
002 3 0102
002 8 0101
002 5 0105tabl_b
id qty date
001 10 0101
001 9 0108
002 8 0101
002 5 0105
想要得到结果如下:
id po
001 0.66
002 0.66
注:0.66是tabl_b中的001,002行数除tabl_a中的001,002行数所得
tabl_a
id qty date
001 10 0101
001 3 0102
001 9 0108
002 3 0102
002 8 0101
002 5 0105tabl_b
id qty date
001 10 0101
001 9 0108
002 8 0101
002 5 0105
想要得到结果如下:
id po
001 0.66
002 0.66
注:0.66是tabl_b中的001,002行数除tabl_a中的001,002行数所得
/count(*)
from tab_a
group by id
insert into tabl_a
select '001', 10, '0101' union all
select '001', 3 , '0102' union all
select '001', 9 , '0108' union all
select '002', 3 , '0102' union all
select '002', 8 , '0101' union all
select '002', 5 , '0105'create table tabl_b
(id varchar(10), qty int, date varchar(10))
insert into tabl_b
select '001', 10, '0101' union all
select '001', 9 , '0108' union all
select '002', 8 , '0101' union all
select '002', 5 , '0105'select id,po=(select count(*) from tabl_b where id=a.id )/cast(count(*) as numeric(10,2))
from tabl_a a
group by idid po
---------- -------------------------
001 .66666666666
002 .66666666666(2 row(s) affected)
insert tabl_a
select '001',10,'0101'
union select '001',3,'0102'
union select '001',9,'0108'
union select '002',3,'0102'
union select '002',8,'0101'
union select '002',5,'0105'create table tabl_b(id varchar(10),qty int,date varchar(10))
insert tabl_b
select '001',10,'0101'
union select '001',9,'0108'
union select '002',8,'0101'
union select '002',5,'0105'
select a.id,cast(c2*1.00/c1 as numeric(9,2)) from (select id,c1=count(qty) from tabl_a group by id) a inner join (select id,c2=count(qty) from tabl_b group by id) b on a.id=b.id
select a.id,c2*1.00/c1 from (select id,c1=count(qty) from tabl_a group by id) a inner join (select id,c2=count(qty) from tabl_b group by id) b on a.id=b.iddrop table tabl_a,tabl_b