求高效SQL

Question

--> 测试时间:2009-07-07 16:39:26
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[A1]') is not null drop table [A1]
create table [A1]([ID] int,[typeID] varchar(5))
insert [A1]
select 1,'2,3,5' union all
select 2,'1,2,4' union all
select 3,'5' union all
select 4,'2,3' union all
select 5,'2,5'
if object_id('[B1]') is not null drop table [B1]
create table [B1]([ID] int,[typename] varchar(4))
insert [B1]
select 1,'苹果' union all
select 2,'梨' union all
select 3,'青菜' union all
select 4,'鱼' union all
select 5,'饮料'
select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go
select * from B1 join (
select keyword=substring(a.typeID,b.id,charindex(',',a.typeID+',',b.id)-b.id)
,[count]=count(distinct a.id)
from A1 a,序数表 b
where b.id<=len(a.typeID)
and substring(','+a.typeID,b.id,1)=','
group by substring(a.typeID,b.id,charindex(',',a.typeID+',',b.id)-b.id)
) t on B1.ID=t.keyword
order by [count] desc
/*
ID          typename keyword count       
----------- -------- ------- ----------- 
2           梨        2       4
5           饮料       5       3
3           青菜       3       2
4           鱼        4       1
1           苹果       1       1（所影响的行数为 5 行）*/drop table A1,B1,序数表

Answer 1

if object_id('[A1]') is not null drop table [A1]
go
create table [A1]([ID] int,[typeID] varchar(5))
insert [A1]
select 1,'2,3,5' union all
select 2,'1,2,4' union all
select 3,'5' union all
select 4,'2,3' union all
select 5,'2,5'
if object_id('[B1]') is not null drop table [B1]
go
create table [B1]([ID] int,[typename] varchar(4))
insert [B1]
select 1,'苹果' union all
select 2,'梨' union all
select 3,'青菜' union all
select 4,'鱼' union all
select 5,'饮料'select *from B1 t order by (select count(1) from A1 where charindex(','+ltrim(t.ID)+',',','+[typeID]+',')>0) desc/*
ID          typename
----------- --------
2           梨
5           饮料
3           青菜
4           鱼
1           苹果(5 行受影响)
*/

Answer 2

if object_id('[A1]') is not null drop table [A1]
go
create table [A1]([ID] int,[typeID] varchar(5))
insert [A1]
select 1,'2,3,5' union all
select 2,'1,2,4' union all
select 3,'5' union all
select 4,'2,3' union all
select 5,'2,5'
if object_id('[B1]') is not null drop table [B1]
go
create table [B1]([ID] int,[typename] varchar(4))
insert [B1]
select 1,'苹果' union all
select 2,'梨' union all
select 3,'青菜' union all
select 4,'鱼' union all
select 5,'饮料'select typename
from B1 
order by (
    select count(1) 
    from A1 
    where charindex(','+ltrim(B1.ID)+',',','+[typeID]+',')>0) desc,
    ID/*
typename
--------
梨
饮料
青菜
苹果
鱼(5 行受影响)
*/

Answer 3

这个不错 很喜欢charindex(','+ltrim(B1.ID)+',',','+[typeID]+',')>0) desc这种排序方式