select a.OrderCode,a.AllianceName,a.userID,o.OrderID,o.AddTime,c.ProID,c.ProType,c.SigoPrice,c.BuyCount,o.PayType from Sigo_AlliancesOrders a join Sigo_ProOrder o on a.OrderCode=o.OrderCode right join Sigo_ProCart c on o.OrderID=c.OrderID where a.AllianceName=@AllianceName and (c.AddTime >= @fromTime and c.AddTime<=@toTime) order by a.OrderCodeselect a.OrderCode,a.AllianceName,a.userID,o.OrderID,o.AddTime,c.ProID,c.ProType,c.SigoPrice,c.BuyCount,o.PayType from Sigo_AlliancesOrders a join Sigo_ProOrder o on a.OrderCode=o.OrderCode right join Sigo_ProCart c on o.OrderID=c.OrderID where a.AllianceName=@AllianceName and (o.AddTime >= @fromTime and o.AddTime<=@toTime) order by a.OrderCode
select a.OrderCode,a.AllianceName,a.userID,o.OrderID,o.AddTime,c.ProID,c.ProType,c.SigoPrice,c.BuyCount,o.PayType from Sigo_AlliancesOrders a join Sigo_ProOrder o on a.OrderCode=o.OrderCode right join Sigo_ProCart c on o.OrderID=c.OrderID where a.AllianceName=@AllianceName and (o.AddTime >= @fromTime and o.AddTime<=@toTime) order by a.OrderCode
Sigo_ProCart
Sigo_ProOrder
两表完全匹配则结果一样。
create table #a(id int,date varchar(10))insert into #a
select 1,'2010-08-01' union all
select 2,'2010-08-02'create table #b(id int,date varchar(10))
insert into #b
select 1,'2010-08-01' union all
select 3,'2010-08-04'
select *from #a a right join #b b on a.id=b.id
where a.date between '2010-08-01' and '2010-08-30'
/*
id date id date
----------- ---------- ----------- ----------
1 2010-08-01 1 2010-08-01(1 行受影响)*/
select *from #a a right join #b b on a.id=b.id
where b.date between '2010-08-01' and '2010-08-30'
/*
id date id date
----------- ---------- ----------- ----------
1 2010-08-01 1 2010-08-01
NULL NULL 3 2010-08-04(2 行受影响)*/
或者用with顯示寫清楚你要使用的索引
MSSQL的會使用它覺得可能最有的方式來優化-- ,天知道它在搞什麽
我有次就踫到,,A查詢 5分鐘 B查詢 1分鐘 ,但重點是B查詢居然比A查詢還多平行連接一個30多W筆數據的表
或者踫到更改一個where條件就導致速度減慢10倍的情況