怎么优化查询

[__lhy]

select 
[PageViewsCount]=(select sum([PageViewsCount]) from [TmallGoods] where [TmallGoodsId] in (select [TmallGoodsId] from [StyleNoCode] where [StyleNo]=s.[StyleNo]) and ChannelType=0), 
[PageVisitorsCount]=(select sum([PageVisitorsCount]) from [TmallGoods] where [TmallGoodsId] in (select [TmallGoodsId] from [StyleNoCode] where [StyleNo]=s.[StyleNo]) and ChannelType=0), 
'2015-01-09'as [StatisticalDate] 
from StyleNos s

CREATE TABLE [dbo].[TmallGoods](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TmallGoodsId] [nvarchar](50) NOT NULL,
	[ChannelType] [int] NULL,
	[PageViewsCount] [int] NULL,
 CONSTRAINT [PK_TmallGoods] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[StyleNoCode](
	[StyleNo] [nvarchar](50) NOT NULL,
	[TmallGoodsId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_StyleNoCode] PRIMARY KEY CLUSTERED 
(
	[TmallGoodsId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[StyleNos](
	[StyleNo] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_StyleNos] PRIMARY KEY CLUSTERED 
(
	[StyleNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

目前的查询很耗时，请问怎么优化？

[hepe00]

试试下面优化：

select  [PageViewsCount],
        [PageVisitorsCount] ,
        '2015-01-09' as [StatisticalDate]
from    StyleNos s
CROSS APPLY (
	select sum([PageViewsCount]) AS [PageViewsCount]
         from   [TmallGoods] AS A
         where  EXISTS (
                select  1
                from    [StyleNoCode] AS B
                where   B.[StyleNo] = s.[StyleNo] AND A.[TmallGoodsId]=B.[TmallGoodsId] )
                and ChannelType = 0
) AS R
CROSS APPLY(
	select  sum([PageVisitorsCount])
        from    [TmallGoods] AS A
        where   EXISTS (
                select  1
                from    [StyleNoCode] AS B
                where   B.[StyleNo] = s.[StyleNo] AND A.[TmallGoodsId]=B.[TmallGoodsId] )
                and ChannelType = 0
) AS T

[hepe00]

select  [PageViewsCount],
        [PageVisitorsCount] ,
        '2015-01-09' as [StatisticalDate]
from    StyleNos s
CROSS APPLY (
	select sum([PageViewsCount]) AS [PageViewsCount]
         from   [TmallGoods] AS A
         where  EXISTS (
                select  1
                from    [StyleNoCode] AS B
                where   B.[StyleNo] = s.[StyleNo] AND A.[TmallGoodsId]=B.[TmallGoodsId] )
                and ChannelType = 0
) AS R
CROSS APPLY(
	select  sum([PageVisitorsCount]) AS [PageVisitorsCount]
        from    [TmallGoods] AS A
        where   EXISTS (
                select  1
                from    [StyleNoCode] AS B
                where   B.[StyleNo] = s.[StyleNo] AND A.[TmallGoodsId]=B.[TmallGoodsId] )
                and ChannelType = 0
) AS T

[唐诗三百首]

try this,

select y.[PageViewsCount],
       z.[PageVisitorsCount],
       '2015-01-09' [StatisticalDate]  
 from StyleNos s
 left join
 (select y2.[StyleNo],
         sum(y1.[PageViewsCount]) [PageViewsCount]
  from [TmallGoods] y1
  inner join [StyleNoCode] y2 on y1.[TmallGoodsId]=y2.[TmallGoodsId]
  where y1.ChannelType=0
  group by y2.[StyleNo]) y on s.[StyleNo]=y.[StyleNo]
 left join
 (select z2.[StyleNo],
         sum(z1.[PageVisitorsCount]) [PageVisitorsCount] 
  from [TmallGoods] z1
  inner join [StyleNoCode] z2 on z1.[TmallGoodsId]=z2.[TmallGoodsId]
  where z1.ChannelType=0
  group by z2.[StyleNo]) z on s.[StyleNo]=z.[StyleNo]

[__lhy]

引用 3 楼 ap0405140 的回复:

try this,

select y.[PageViewsCount],
       z.[PageVisitorsCount],
       '2015-01-09' [StatisticalDate]  
 from StyleNos s
 left join
 (select y2.[StyleNo],
         sum(y1.[PageViewsCount]) [PageViewsCount]
  from [TmallGoods] y1
  inner join [StyleNoCode] y2 on y1.[TmallGoodsId]=y2.[TmallGoodsId]
  where y1.ChannelType=0
  group by y2.[StyleNo]) y on s.[StyleNo]=y.[StyleNo]
 left join
 (select z2.[StyleNo],
         sum(z1.[PageVisitorsCount]) [PageVisitorsCount] 
  from [TmallGoods] z1
  inner join [StyleNoCode] z2 on z1.[TmallGoodsId]=z2.[TmallGoodsId]
  where z1.ChannelType=0
  group by z2.[StyleNo]) z on s.[StyleNo]=z.[StyleNo]

谢谢！代码没添全

select 
[PageViewsCount]=(select sum([PageViewsCount]) from [TmallGoods] where [TmallGoodsId] in (select [TmallGoodsId] from [StyleNoCode] where [StyleNo]=s.[StyleNo]) and ChannelType=0), 
[PageVisitorsCount]=(select sum([PageVisitorsCount]) from [TmallGoods] where [TmallGoodsId] in (select [TmallGoodsId] from [StyleNoCode] where [StyleNo]=s.[StyleNo]) and ChannelType=0), 
'2015-01-09'as [StatisticalDate] 
from StyleNos s
inner join RealGoods r on s.StyleNo=r.StyleNo and r.StatisticalDate='2015-01-09' group by s.StyleNo

CREATE TABLE [dbo].[RealGoods](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StyleNo] [nvarchar](50) NOT NULL,
	[NewShoppingCartCount] [int] NULL,
	[NewCollectionCount] [int] NULL,
	[GoodsSn] [nvarchar](50) NULL,
	[Brand] [nvarchar](50) NULL,
	[CostPrice] [money] NULL,
	[Cat] [nvarchar](50) NULL,
	[QiChu] [int] NULL,
	[QiMo] [int] NULL,
	[CaiGouRu] [int] NULL,
	[KeYong] [int] NULL,
	[ZaiTu] [int] NULL,
	[TopClass] [int] NULL,
	[StatisticalDate] [nvarchar](50) NULL,
 CONSTRAINT [PK_RealGoods] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这样应该怎么改？

[唐诗三百首]

try this,

select y.[PageViewsCount],
       z.[PageVisitorsCount],
       '2015-01-09' [StatisticalDate]  
 from (select distinct s0.StyleNo
       from StyleNos s0
       inner join RealGoods r on s0.StyleNo=r.StyleNo and r.StatisticalDate='2015-01-09') s
 left join
 (select y2.[StyleNo],
         sum(y1.[PageViewsCount]) [PageViewsCount]
  from [TmallGoods] y1
  inner join [StyleNoCode] y2 on y1.[TmallGoodsId]=y2.[TmallGoodsId]
  where y1.ChannelType=0
  group by y2.[StyleNo]) y on s.[StyleNo]=y.[StyleNo]
 left join
 (select z2.[StyleNo],
         sum(z1.[PageVisitorsCount]) [PageVisitorsCount] 
  from [TmallGoods] z1
  inner join [StyleNoCode] z2 on z1.[TmallGoodsId]=z2.[TmallGoodsId]
  where z1.ChannelType=0
  group by z2.[StyleNo]) z on s.[StyleNo]=z.[StyleNo]