select * from (
select *,ROW_NUMBER() over (order by InsertTime desc) as row,COUNT(1) over() as dataCout from (
(select ChaseOrderID,55 as 'btName',
COUNT(1)CountQS,
SUM(case when OrderState&1=1 then 1 else 0 end) CountSY,
SUM(case when OrderState&4<>4 then BetMoney else 0 end) SumBetMoney,
SUM(AwardMoney) SumAwardMoney,
MIN(InsertTime) as InsertTime,
users.UserName,users.USER_ID
from BetInfo_55 with(nolock) left join Users with(nolock) on dbo.BetInfo_55.USER_ID=Users.USER_ID
where 1=1 and (BetMode&2=2 or BetMode&4=4 )
and Users.MerchantCode='009' AND Users.Category&0x0004<>0x0004
group by ChaseOrderID,users.UserName,users.USER_ID )
union all
(select ChaseOrderID,5 as 'btName',
COUNT(1)CountQS,
SUM(case when OrderState&1=1 then 1 else 0 end) CountSY,
SUM(case when OrderState&4<>4 then BetMoney else 0 end)SumBetMoney,
SUM(AwardMoney) SumAwardMoney,
MIN(InsertTime) as InsertTime,
users.UserName,users.USER_ID
from BetInfo_5 with(nolock) left join Users with(nolock) on dbo.BetInfo_5.USER_ID=Users.USER_ID
where 1=1 and (BetMode&2=2 or BetMode&4=4 )
and Users.MerchantCode='009' AND Users.Category&0x0004<>0x0004
group by ChaseOrderID,users.UserName,users.USER_ID )
union all
(select ChaseOrderID,12 as 'btName',
COUNT(1)CountQS,
SUM(case when OrderState&1=1 then 1 else 0 end) CountSY,
SUM(case when OrderState&4<>4 then BetMoney else 0 end) SumBetMoney,
SUM(AwardMoney) SumAwardMoney,
MIN(InsertTime) as InsertTime,
users.UserName,users.USER_ID
from BetInfo_12 with(nolock) left join Users with(nolock) on dbo.BetInfo_12.USER_ID=Users.USER_ID
where 1=1 and (BetMode&2=2 or BetMode&4=4 )
and Users.MerchantCode='009' AND Users.Category&0x0004<>0x0004
group by ChaseOrderID,users.UserName,users.USER_ID )
) as allbet where 1=1 and InsertTime between cast('2015/8/1 0:00:00' as datetime) and cast('2015/8/12 23:59:59' as datetime)
and allbet.SumBetMoney >0) as data
不到3万条数据查询很慢的 需要6秒啊
betmode、category 属于可分割的数据项目。
你这种就是属于第一范式都不满足。
无法利用索引，就不能优化了。