select * from a where not exists(select 1 from b where a.主键=b.主键)select * from b where not exists(select 1 from a where a.主键=b.主键)
a中有但是b中没有 select * from a left join b on a.关键字=b.关键字 where b.资料 is null 或者 b中有而a中没有 的资料 select * from a right join b on a.关键字=b.关键字 where a.资料 is null/*满足你的要求的方法很多,你贴出你的数据及你要的结果 再来看用哪种方法最快最好*/
select * from 表1 where 主键 not in (select 主键 from 表2) 表1数据多于表2数据
a中有,b中没有的! select * from a where a.主键 not in (select b.主键 from b )b中有,a中没有的! select * from b where b.主键 not in (select a.主键 from a )
有没有简单点的啊~SELECT TOP 300 p.ID, p.ID AS ProductID, p.BrandID, p.BaseConfig, p.Intro, p2.MinProdPrice, p2.MaxProdPrice, p.BrandName, p.Model, p.PriceImage, DATEDIFF(Day, p.PostDate, GETDATE()) AS DayNumber, dp.MaxDealerPrice, dp.MinDealerPrice, p.IsNewProduct, dp2.ProductID CanSellProductID, p.PostDate, dp.DealerCount FROM dbo.vwProduct p INNER JOIN (SELECT TOP 10 ID FROM Brand WHERE Status>=100 AND SmallClassID=10 ORDER BY Brand_Order DESC) b ON p.BrandID=b.ID LEFT OUTER JOIN (SELECT ProductID, MIN(CAST(Price AS FLOAT)) MinProdPrice, MAX(Cast(Price AS FLOAT)) MaxProdPrice FROM Price WHERE Status >= 100 AND CAST(Price AS FLOAT) >= 1 GROUP BY ProductID) p2 ON p.ID = p2.ProductID LEFT OUTER JOIN (SELECT MIN(dp1.Price) MinDealerPrice, MAX(dp1.Price) MaxDealerPrice, dp1.ProductID, COUNT(dp1.ProductID) AS DealerCount FROM Dealer_Product dp1 INNER JOIN Dealer_Ads da ON dp1.DealerID = da.DealerID WHERE dp1.Status >= 100 AND dp1.Price > 0 AND dp1.SmallClassID = 10 AND da.Commend >= 100 AND (da.BeginDate <= GETDATE()) AND (da.EndDate >= GETDATE()) AND da.SmallClassID = 10 GROUP BY dp1.ProductID) dp ON p.ID = dp.ProductID LEFT OUTER JOIN (SELECT ProductID FROM Dealer_Product WHERE Price > 0 AND StockNum > 0 AND CanSell >= 100 AND Status >= 100 AND SellDate>=GetDate()) dp2 ON p.ID = dp2.ProductID WHERE (p.Status >= 100) AND (p.SmallClassID = 10) ORDER BY p.PostDate DESC 和 SELECT DISTINCT TOP 300 p.ID, p.ID AS ProductID, p.BrandID, p.BaseConfig, p.Intro, p2.MinProdPrice, p2.MaxProdPrice, p.BrandName, p.Model, p.PriceImage, DATEDIFF(Day, p.PostDate, GETDATE()) AS DayNumber, dp.MaxDealerPrice, dp.MinDealerPrice, p.IsNewProduct, dp2.ProductID CanSellProductID, p.PostDate, dp.DealerCount FROM dbo.vwProduct p INNER JOIN (SELECT TOP 10 ID FROM Brand WHERE Status>=100 AND SmallClassID=10 ORDER BY Brand_Order DESC) b ON p.BrandID=b.ID LEFT OUTER JOIN (SELECT ProductID, MIN(CAST(Price AS FLOAT)) MinProdPrice, MAX(Cast(Price AS FLOAT)) MaxProdPrice FROM Price WHERE Status >= 100 AND CAST(Price AS FLOAT) >= 1 GROUP BY ProductID) p2 ON p.ID = p2.ProductID LEFT OUTER JOIN (SELECT MIN(dp1.Price) MinDealerPrice, MAX(dp1.Price) MaxDealerPrice, dp1.ProductID, COUNT(dp1.ProductID) AS DealerCount FROM Dealer_Product dp1 INNER JOIN Dealer_Ads da ON dp1.DealerID = da.DealerID WHERE dp1.Status >= 100 AND dp1.Price > 0 AND dp1.SmallClassID = 10 AND da.Commend >= 100 AND (da.BeginDate <= GETDATE()) AND (da.EndDate >= GETDATE()) AND da.SmallClassID = 10 GROUP BY dp1.ProductID) dp ON p.ID = dp.ProductID LEFT OUTER JOIN (SELECT ProductID FROM Dealer_Product WHERE Price > 0 AND StockNum > 0 AND CanSell >= 100 AND Status >= 100 AND SellDate>=GetDate()) dp2 ON p.ID = dp2.ProductID WHERE (p.Status >= 100) AND (p.SmallClassID = 10) ORDER BY p.PostDate DESC以上说的我也会,只是语句太长了~我要优化SQL语句,看DISTINCT有没有必要加
where not exists(select 1 from b where a.主键=b.主键)select * from b
where not exists(select 1 from a where a.主键=b.主键)
select * from a left join b on a.关键字=b.关键字 where b.资料 is null
或者 b中有而a中没有 的资料
select * from a right join b on a.关键字=b.关键字 where a.资料 is null/*满足你的要求的方法很多,你贴出你的数据及你要的结果
再来看用哪种方法最快最好*/
表1数据多于表2数据
select * from a
where a.主键 not in (select b.主键 from b )b中有,a中没有的!
select * from b
where b.主键 not in (select a.主键 from a )
DATEDIFF(Day, p.PostDate, GETDATE()) AS DayNumber, dp.MaxDealerPrice, dp.MinDealerPrice, p.IsNewProduct, dp2.ProductID CanSellProductID, p.PostDate,
dp.DealerCount FROM dbo.vwProduct p INNER JOIN (SELECT TOP 10 ID FROM Brand WHERE Status>=100 AND SmallClassID=10
ORDER BY Brand_Order DESC) b ON p.BrandID=b.ID LEFT OUTER JOIN (SELECT ProductID, MIN(CAST(Price AS FLOAT)) MinProdPrice, MAX(Cast(Price AS FLOAT))
MaxProdPrice FROM Price WHERE Status >= 100 AND CAST(Price AS FLOAT) >= 1 GROUP BY ProductID) p2 ON p.ID = p2.ProductID LEFT OUTER JOIN
(SELECT MIN(dp1.Price) MinDealerPrice, MAX(dp1.Price) MaxDealerPrice, dp1.ProductID, COUNT(dp1.ProductID) AS DealerCount FROM Dealer_Product dp1
INNER JOIN Dealer_Ads da ON dp1.DealerID = da.DealerID WHERE dp1.Status >= 100 AND dp1.Price > 0 AND dp1.SmallClassID = 10 AND da.Commend >= 100
AND (da.BeginDate <= GETDATE()) AND (da.EndDate >= GETDATE()) AND da.SmallClassID = 10 GROUP BY dp1.ProductID) dp ON p.ID = dp.ProductID
LEFT OUTER JOIN (SELECT ProductID FROM Dealer_Product WHERE Price > 0 AND StockNum > 0 AND CanSell >= 100 AND Status >= 100
AND SellDate>=GetDate()) dp2 ON p.ID = dp2.ProductID WHERE (p.Status >= 100) AND (p.SmallClassID = 10) ORDER BY p.PostDate DESC
和
SELECT DISTINCT TOP 300 p.ID, p.ID AS ProductID, p.BrandID, p.BaseConfig, p.Intro, p2.MinProdPrice, p2.MaxProdPrice, p.BrandName, p.Model, p.PriceImage,
DATEDIFF(Day, p.PostDate, GETDATE()) AS DayNumber, dp.MaxDealerPrice, dp.MinDealerPrice, p.IsNewProduct, dp2.ProductID CanSellProductID, p.PostDate,
dp.DealerCount FROM dbo.vwProduct p INNER JOIN (SELECT TOP 10 ID FROM Brand WHERE Status>=100 AND SmallClassID=10
ORDER BY Brand_Order DESC) b ON p.BrandID=b.ID LEFT OUTER JOIN (SELECT ProductID, MIN(CAST(Price AS FLOAT)) MinProdPrice, MAX(Cast(Price AS FLOAT))
MaxProdPrice FROM Price WHERE Status >= 100 AND CAST(Price AS FLOAT) >= 1 GROUP BY ProductID) p2 ON p.ID = p2.ProductID LEFT OUTER JOIN
(SELECT MIN(dp1.Price) MinDealerPrice, MAX(dp1.Price) MaxDealerPrice, dp1.ProductID, COUNT(dp1.ProductID) AS DealerCount FROM Dealer_Product dp1
INNER JOIN Dealer_Ads da ON dp1.DealerID = da.DealerID WHERE dp1.Status >= 100 AND dp1.Price > 0 AND dp1.SmallClassID = 10 AND da.Commend >= 100
AND (da.BeginDate <= GETDATE()) AND (da.EndDate >= GETDATE()) AND da.SmallClassID = 10 GROUP BY dp1.ProductID) dp ON p.ID = dp.ProductID
LEFT OUTER JOIN (SELECT ProductID FROM Dealer_Product WHERE Price > 0 AND StockNum > 0 AND CanSell >= 100 AND Status >= 100
AND SellDate>=GetDate()) dp2 ON p.ID = dp2.ProductID WHERE (p.Status >= 100) AND (p.SmallClassID = 10) ORDER BY p.PostDate DESC以上说的我也会,只是语句太长了~我要优化SQL语句,看DISTINCT有没有必要加
minus
select * from b