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 allselect 'bbb',2 union allselect '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 allselect 'aaa',33 union allselect 'aaa',55 union allselect 'aaa',77 union allselect 'aaa',88 union allselect 'bbb',33 union allselect 'bbb',99 union allselect 'bbb',11 union allselect 'ccc',22 union allselect 'ccc',34--------------开始查询--------------------------select a.a,b.bfrom ajoin(select a,(select COUNT(distinct b) from b where a=t.a and b>=t.b) as numb,bfrom b t)bon a.a=b.awhere b.numb<=a.b----------------结果----------------------------/* a b---- -----------aaa 88aaa 77aaa 55bbb 99bbb 33ccc 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 allselect 'bbb',2 union allselect '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 allselect 'aaa',33 union allselect 'aaa',55 union allselect 'aaa',77 union allselect 'aaa',88 union allselect 'bbb',33 union allselect 'bbb',99 union allselect 'bbb',11 union allselect 'ccc',22 union allselect 'ccc',34--------------开始查询--------------------------select a.a,b.bfrom ajoin(select a,(select COUNT(distinct b) from b where a=t.a and b>=t.b) as numb,bfrom b t)bon a.a=b.awhere b.numb<=a.b----------------结果----------------------------/* a b---- -----------aaa 88aaa 77aaa 55bbb 99bbb 33ccc 34(6 行受影响)*/