I will be writing a query to achieve something like below:
TableName: Application
AppId (PK,int) AppType (bit)
1 0
2 0
3 0
4 0
5 1
6 0
7 0
8 0
9 1
10 1
11 0
12 0
13 1
14 0
15 1
I have to sequentially group App_Id based on App_type and create a batch of records. The important point to note is that I have to maintain the SEQUENCE of AppId while creating the batches. The maximum number of records a batch can have depends on batch size parameter (say batch size set to 3 for now). Once the batch is created, insert the details in a different table say called ApplicationBatch. So I want an output something like:
TableName: ApplicationBatch
BatchId MinAppId MaxAppId AppType开发者_开发技巧
001 1 3 0
002 4 4 0
003 5 5 1
004 6 8 0
005 9 10 1
006 11 12 0
007 13 13 1
008 14 14 0
009 15 15 1
One more thing I have to design the query in a best efficient and optimized way because Application table can have more than million records in it.
Update:
Currently I have Application table (defined in my original question above) and I want to populate ApplicationBatch table based on data from Application table.
Try this. Pretty complicated but works. I didn't test it on so many rows, but it iterates through the table only once.
First you have to do some preporocesing.
-- create temporary table
CREATE TABLE #tmpApp(AppId INT, AppType BIT , BatchId INT)
INSERT INTO #tmpApp(AppId,AppType)
SELECT AppId, AppType FROM Application
-- declare variables
DECLARE @curId INT
DECLARE @oldCurId INT
DECLARE @appType INT
DECLARE @oldAppType INT
DECLARE @batchNo INT
DECLARE @itemsInBatch INT
SET @oldCurId = 0
SET @batchNo = 1
SET @itemsInBatch = 0
SELECT TOP 1
@curId = AppId,
@appType = AppType
FROM #tmpApp
WHERE AppId > @oldCurId
ORDER BY AppId
WHILE @curId IS NOT NULL
BEGIN
IF @oldAppType <> @appType OR @itemsInBatch >= 3
BEGIN
SET @batchNo = @batchNo + 1
SET @itemsInBatch = 0
END
SET @itemsInBatch = @itemsInBatch + 1
UPDATE #tmpApp
SET batchId = @batchNo
WHERE AppId = @curId
SET @oldCurId = @curId
SET @oldAppType = @appType
SET @curId = NULL
SELECT TOP 1
@curId = AppId,
@appType = AppType
FROM #tmpApp
WHERE AppId > @oldCurId
ORDER BY AppId
END
And execute the query:
-- the final query
SELECT
BatchId,
MIN(AppId) AS MinAppId,
MAX(AppId) AS MaxAppId,
AppType
FROM #tmpApp
GROUP BY BatchId, AppType
ORDER BY BatchId
the question is not clear, but I understand the next.
you want to have a sequence for aplication and that depends of the number of lines inserted in anohter table?
see you.
It's hard to do this without CURSORS or SQLCLR. Would you consider writting a table-valued function in c# and ambedding the assembly in SQL Server? (SQLCLR) That's what I would do, and then I would do a while loop processing the records sequantially.
精彩评论