select * from tb a where orderid in(select top 3 orderid from tb where productid=a.productid order by orderid)
select * from 销售表A a where orderID in(select top 3 orderID from 销售表A where productID=a.productID)
select * from table a where orderid in(select top 3 orderid from table where productid=a.productid order by orderid)
select * from t a where orderID in(select top 3 orderID from t where productID=a.productID)
用一条SQL语句的时间复杂度太高了 如果数据量很大 不适用数据量大推荐用存储过程、临时表解决CREATE PROCEDURE dbo.GetTOPNOrder @GetTopNum int AS DECLARE @vProductID int, @vInsertSql varchar(100) BEGIN BEGIN TRANSACTION IF @GetTOPNum<1 GOTO deal_err --Create Temp Table SELECT * INTO ##TempTb FROM A WHERE 1=2 IF @@ERROR<>0 GOTO deal_err --Get ProductIDs DECLARE C_ProcID CURSOR LOCAL FOR SELECT DISTINCT ProductID FROM A OPEN C_ProcID IF @@ERROR<>0 GOTO deal_err FETCH NEXT FROM C_ProcID INTO @vProductID --INSERT TOP N RECORDER FOR EVERY PRODUCT WHILE @@DETCH_STATUS=0 BEGIN SET @vInsertSql='INSERT INTO ##TempTb SELECT TOP ' + CAST(@GetTOPNum AS varchar) + ' FROM A WHERE ProductID=' + CAST(@vProductID AS varchar) + ' ORDER BY OrderID) EXEC(@vInsertSql) IF @@ERROR<>0 GOTO deal_err FETCH NEXT FROM C_ProcID INTO @vProductID END CLOSE C_ProcID DEALLOCATE C_ProcID SELECT * FROM ##TempTb DROP TABLE ##TempTB COMMIT TRANSACTION RETURN deal_err: ROLLBACK TRANSACTION --RETURN -1 SHOW THAT ERR RAISE SELECT -1 RETURN END
where orderid in(select top 3 orderid from tb where productid=a.productid order by orderid)
where orderID in(select top 3 orderID from 销售表A where productID=a.productID)
where orderid in(select top 3 orderid from table where productid=a.productid order by orderid)
where orderID in(select top 3 orderID from t where productID=a.productID)
如果数据量很大
不适用数据量大推荐用存储过程、临时表解决CREATE PROCEDURE dbo.GetTOPNOrder
@GetTopNum int
AS
DECLARE
@vProductID int,
@vInsertSql varchar(100)
BEGIN
BEGIN TRANSACTION
IF @GetTOPNum<1 GOTO deal_err
--Create Temp Table
SELECT * INTO ##TempTb FROM A WHERE 1=2
IF @@ERROR<>0 GOTO deal_err
--Get ProductIDs
DECLARE C_ProcID CURSOR LOCAL
FOR SELECT DISTINCT ProductID FROM A OPEN C_ProcID
IF @@ERROR<>0 GOTO deal_err FETCH NEXT FROM C_ProcID INTO @vProductID --INSERT TOP N RECORDER FOR EVERY PRODUCT
WHILE @@DETCH_STATUS=0
BEGIN
SET @vInsertSql='INSERT INTO ##TempTb SELECT TOP ' + CAST(@GetTOPNum AS varchar) + ' FROM A WHERE ProductID=' + CAST(@vProductID AS varchar) + ' ORDER BY OrderID)
EXEC(@vInsertSql)
IF @@ERROR<>0 GOTO deal_err
FETCH NEXT FROM C_ProcID INTO @vProductID
END CLOSE C_ProcID
DEALLOCATE C_ProcID SELECT * FROM ##TempTb
DROP TABLE ##TempTB
COMMIT TRANSACTION
RETURN
deal_err:
ROLLBACK TRANSACTION
--RETURN -1 SHOW THAT ERR RAISE
SELECT -1
RETURN
END
在BEGIN TRANSACTION前插入一行
SET NOCOUNT ON