好复杂的过滤产品SQL

Question

会员角色表
id roleid   roleName   roleOrder
1   1        普通会员      1
2   2        收费会员      2
会员表
id   userTag    userType(来源于上面那个表的roleid)
1    user001      1
2    user002      2
5    user07       1
6    user32       1
8    user57       1
11    user67       1
21    user77       1
23    user87       1企业表
id    userTag   companyName   sortOrder
1      user001   A公司           20
2      user002   B公司           20
3      user07    C公司           20
4      user32    M公司            1
5      user57    p公司            2
8      user67    U公司            3
9      user77    K公司            5
11     user87    L公司             4
产品表
id    prodName    smallCityID（地区二级分类ID）   smallSortID(产品二级分类ID)  companyid(来源于上面企业表id)
1     A产品          104                               22                       1
2     YY产品         210                               23                       1
3     A产品          104                               22                       1
4     YP产品         104                               23                       1
5     QQ产品        210                                47                      9
6      TT产品       210                                22                       4
地区表
id   addressName  ownID
1     福建           0
2     江西           0104     厦门           1
210     福州            1
产品分类表
id   sortName   ownid
1     食品        0
2     玩具        0
22     泡面         1
23     食盐         1
47     大米         1================================
各个表情况如上面，现在我要在一个a.aspx列表页中显示出这样的查询
产品名称   地区（省份和城市）  
排序规则：先排序收费会员产品再排序普通会员产品。然后再排公司表的sortOrder字段。
要求各个企业只能显示出来的产品数只能一个不能多个
另外查询可配过滤条件。如果用户有根据页面的产品大分类ID  产品二级分类ID   地区一级分类ID 地区二级分类ID进行过滤的话。也要符合只能和该查询条件符合的一家企业一个代表产品。不能多条相关产品或无关产品。===============我感觉自己的难度在于过滤掉重复的产品好难。我尝试了下。自己按产品表的重复公司ID 进行过滤。发觉两万条的数据单查询这个就要耗费34秒。这么慢的执行效率。还要和其他表进行 关联，真的难以想象是什么速度。
希望大家能帮想想方案策略，最好是怎么写SQL才比较好。
（类似帖子http://topic.csdn.net/u/20080626/17/43f4cff8-36ba-44d4-9781-c2c57b7d75a0.html）

Answer 1

create proc test
(
@BigAddress int,
@SmallAddress int,
@BigProdSort int,
@SmallProdSort int,
@key nvarchar(30)
)
declare @strFilter nvarchar(2000)--1985参数，表示查询该字段的全部
if(@BigAddress<>1985)
set @strFilter=@strFilter +'BigAddress.id='+ltrim(@BigAddress)+''
if(@SmallProdSort <>1985)
set @strFilter=@strFilter+'SmallAddress.id='+ltrim(@SmallAddress)+''
if(@BigProdSort<>1985 )
set @strFilter=@strFilter+'BigProdSort.id='+ltrim(@BigProdSort)+''
if(@SmallProdSort<>1985)
set @strFiltre=@strFilter+'SmallProdSort.id='+ltrim(@SmallProdSort)+''if(@Key<>'')
set @strFilter=@strFilter+'产品表.prodName like ''%'+ltrim(@Key)+'%'''
select 产品表.*
from 产品表
inner join 企业表
on 产品表.companyid=企业表.id
inner join 用户表
on 用户表.usertag=企业表.usertag
inner join 会员角色表
on 会员角色表.RoleID=用户表.userType
inner join 地区表  as SmallAddress
on  SmallAddress.id=产品表.SmallCityID
inner join 地区表 as BigAddress
on BigAddress.id=SmallCity.OwnID
inner join 产品分类表 as SmallProdSort
on SmallProdSort.id=产品表.SmallsortID
inner join 产品分类表  as BigProdSort
on BigProdSort.id=SmallSortID.OwnID
where 1=1 +@strFilter
order by 会员角色表.RoleOrder desc,企业表.sortOrder  asc
===================
这个查询出来的结果是有很多重复产品的。执行的效率非常低。我都不敢用

Answer 2

建议先把一些表拆分出来,使用表变量或者临时表来限制结果集,
如:
如果参数@BigAddress 不为空时,
先将
select * from [地区表] where BigAddress.id = @BigAddress
这部分记录取出来,
然后和@SmallAddress的SmallAddress 关联产生一个新的表,
类似的产生一个关联的产品分类表,
最后在关联生成的小数据的表,
主要是是你关联的太多了...
一次性全部关联,速度肯定会比较慢的...

Answer 3

还有就是,不一定要全部关联,有些地方如果只是取某个表的一个值的话,可以使用查询方式,
那就不用关联这么多表了.类似于这样的～
select *, (select addressName from 地区表 where id = t.ownID) as 'BigAddress' from 地区表 t

Answer 4

用 TOP 1 …… ORDER BY 
或 ID= MAX() ......

Answer 5

如果再把这么长的SQL查出来的集合再当一个字查询对象进行过滤。那实在是超级恐怖的SQL

Answer 6

你这个显示一个产品逻辑是,
取产品ID最大的那个产品还是按照什么依据来取？
这个是否可以直接先从产品表中抓出来?
Select * from @产品表 t 
         where [id] = (select max(id) from @产品表 
                             where [companyid(来源于上面企业表id)] = t.[companyid(来源于上面企业表id)] 
                               and [smallCityID（地区二级分类ID）] = t.[smallCityID（地区二级分类ID）])
/*
id          prodName smallCityID（地区二级分类ID） smallSortID(产品二级分类ID) companyid(来源于上面企业表id)
----------- -------- --------------------- --------------------- ---------------------
5           QQ产品     210                   47                    9
6           TT产品     210                   22                    4
2           YY产品     210                   23                    1
4           YP产品     104                   23                    1*/
然后在关联地区表去过滤.

Answer 7

--try
Select t.*,
       addr.[addressName],addr.[Bigaddress],
       ent.[roleOrder],ent.[sortOrder]
from @产品表 t 
left join ( select [id], [addressName], 
                  (select [addressName] from @地区表 where [id]= ad.[ownID]) as Bigaddress from @地区表 ad ) addr on addr.[id] = t.[smallCityID（地区二级分类ID）]
left join (select e.[id],c.[roleOrder],e.[sortOrder] from @企业表 e
            left join ( select [userTag],[roleOrder] from @会员表 a left join @会员角色表 b on b.[roleid] = a.[userType(来源于上面那个表的roleid)]) c on c.[userTag] = e.[userTag]  
          ) ent on ent.[id] = t.[companyid(来源于上面企业表id)]
         where t.[id] = (select max(id) from @产品表 prod
                             where prod.[companyid(来源于上面企业表id)] = t.[companyid(来源于上面企业表id)] 
                               and prod.[smallCityID（地区二级分类ID）] = t.[smallCityID（地区二级分类ID）])

Answer 8

太复杂了把复杂的子查询拆成单独的sql，把结果insert 至temp table，然后在temp table上建索引，这样会快很多的。

Answer 9

=================
没法先根据二级产品ID或二级城市ID进行过滤不重复的产品。因为这些条件都是动态变化的。只有客户有查询的时候才组合查询条件的。没有的话就相当于where 1=1