SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] where title like '%测%'
and SellID IN (SELECT SellID FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2)
一直说IN 效率不高, 可我现在有这样的需求,应该怎样优化呢?
...全文
1509打赏收藏
求教一SQL优化
SELECT [SellID] ,[SortID] ,[Title] FROM [TestSort].[dbo].[T_Sell] where title like '%测%' and SellID IN (SELECT SellID FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2) 一直说IN 效率不高, 可我现在有这样的需求,应该怎样优化呢?
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] a where title like '%测%'
and exists(SELECT 1 FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2
and a.SellID=SellID)
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] a,(SELECT SellID FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2) b
where title like '%测%'
and a.SellID=b.SellID
SELECT T.[SellID]
,T.[SortID]
,T.[Title]
FROM [TestSort].[dbo].[T_Sell] AS T
INNER JOIN (SELECT SellID FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2) AS P
ON T.SellID=P.SellID WHERE T.title like '%测%'
刚才发的有点问题。。exists比in效率高,好多人都这么说,我没测试过,你可以试试。。
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] a where title like '%测%'
and exists (SELECT * FROM [T_Sell_Property] where a.sellid=sellid and ValueString='黑色' AND LabelID=2)
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] a where title like '%测%'
and SellID exists (SELECT * FROM [T_Sell_Property] where a.sellid=sellid and ValueString='黑色' AND LabelID=2)
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] a join [T_Sell_Property] b
on a.title like '%测%' and b.ValueString='黑色' and b.LabelID=2 and a.SellID = b.SellID
select a.* from
(
SELECT [SellID]
,[SortID]
,[Title]
FROM [TestSort].[dbo].[T_Sell] where title like '%测%'
) a,
(
SELECT SellID FROM [T_Sell_Property] where ValueString='黑色' AND LabelID=2
) b
where a.SellID = b.SellID