从一个表中的列值取另一个表中的数据,该如何处理

从一个表中的列值取另一个表中的数据
a表

a b
-----
aaa 3
bbb 2
ccc 1

b 表
a b
------
aaa 20
aaa 33
aaa 55
aaa 77
aaa 88
bbb 33
bbb 99
bbb 11
ccc 22
ccc 34

请问如何通过 a表中的 b 字段的数值取得对应b 表中的条数,并按大小排列

最后结果要是这样的
b表

a b
--------
aaa 88
aaa 77
aaa 55
bbb 99
bbb 33
ccc 34

------解决方案--------------------

SQL code

----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-08-09 16:14:38
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--    Jul  9 2008 14:43:34 
--    Copyright (c) 1988-2008 Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([a] varchar(3),[b] int)
insert [a]
select 'aaa',3 union all
select 'bbb',2 union all
select 'ccc',1
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([a] varchar(3),[b] int)
insert [b]
select 'aaa',20 union all
select 'aaa',33 union all
select 'aaa',55 union all
select 'aaa',77 union all
select 'aaa',88 union all
select 'bbb',33 union all
select 'bbb',99 union all
select 'bbb',11 union all
select 'ccc',22 union all
select 'ccc',34
--------------开始查询--------------------------
select 
  a.a,b.b
from
  a
join
(
select
  a,(select COUNT(distinct b) from b where a=t.a and b>=t.b) as numb,b
from
  b t
)b
on
  a.a=b.a
where
  b.numb<=a.b

----------------结果----------------------------
/* a    b
---- -----------
aaa  88
aaa  77
aaa  55
bbb  99
bbb  33
ccc  34

(6 行受影响)
*/

------解决方案--------------------

SQL code

----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-08-09 16:14:38
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--    Jul  9 2008 14:43:34 
--    Copyright (c) 1988-2008 Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([a] varchar(3),[b] int)
insert [a]
select 'aaa',3 union all
select 'bbb',2 union all
select 'ccc',1
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([a] varchar(3),[b] int)
insert [b]
select 'aaa',20 union all
select 'aaa',33 union all
select 'aaa',55 union all
select 'aaa',77 union all
select 'aaa',88 union all
select 'bbb',33 union all
select 'bbb',99 union all
select 'bbb',11 union all
select 'ccc',22 union all
select 'ccc',34
--------------开始查询--------------------------
select 
  a.a,b.b
from
  a
join
(
select
  a,(select COUNT(distinct b) from b where a=t.a and b>=t.b) as numb,b
from
  b t
)b
on
  a.a=b.a
where
  b.numb<=a.b

----------------结果----------------------------
/* a    b
---- -----------
aaa  88
aaa  77
aaa  55
bbb  99
bbb  33
ccc  34

(6 行受影响)
*/

从一个表中的列值取另一个表中的数据,该如何处理

相关推荐