虽然SQL中的循环不起作用 [英] While loop in SQL not working

问题描述

表1：

Table1:

customer_id	dept_id	level_id	event_group
222		300300	UltraSound1	NULL
222		300300	UltraSound1	NULL
222		300300	Lab		NULL
222		300300	Lab		NULL
222		300300	UltraSound2	NULL
222		300300	UltraSound2	NULL
222		300300	UltraSound2	NULL
444		400400	Intensive Care	NULL
444		400400	UltraSound4	NULL
444		400400	UltraSound5	NULL
444		400400	UltraSound6	NULL
444		400400	Cardiology	NULL
444		400400	Cardiology	NULL
444		400400	UltraSound4	NULL
444		400400	UltraSound4	NULL
444		400400	Xray		NULL

想输出如下：

Would like output as follow:

customer_id	dept_id	level_id	event_group
222		300300	UltraSound1	Group1
222		300300	UltraSound1	Group1
222		300300	Lab		NULL
222		300300	Lab		NULL
222		300300	UltraSound2	Group3
222		300300	UltraSound2	Group3
222		300300	UltraSound2	Group3
444		400400	Intensive Care	NULL
444		400400	UltraSound4	Group5
444		400400	UltraSound5	Group5
444		400400	UltraSound6	Group5
444		400400	Cardiology	NULL
444		400400	Cardiology	NULL
444		400400	UltraSound4	Group7
444		400400	UltraSound4	Group7
444		400400	Xray		NULL

我尝试过：



这是我试图尝试的但它运行不正常.....

DECLARE @TOTAL AS INT


IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1;

SELECT A.*, ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS ROWID
INTO #TEMP1
  FROM Table1 A

SELECT @TOTAL = COUNT(*) FROM #TEMP1

DECLARE @COUNTER AS INT, @VAL AS INT
SET @COUNTER = 1
SET @VAL = 1

DECLARE @CURRENTVAL AS VARCHAR(255)
DECLARE @CURRENTCUSTOMER AS VARCHAR(255)
DECLARE @CURRENTCUSTOMERENCOUNTER AS VARCHAR(255)
DECLARE @COMPAREVAL AS VARCHAR(255)

WHILE (@COUNTER <= @TOTAL)
  BEGIN
    -- GET ROW FROM #TEMP1
    SELECT @CURRENTCUSTOMER = CUSTOMER_ID, @CURRENTVAL = LEVEL_ID, @CURRENTCUSTOMERDEPT = DEPT_ID FROM #TEMP1 WHERE ROWID = @COUNTER
      
      IF (@CURRENTVAL LIKE '%UltraSound%')
 	     UPDATE
             [Table1] SET EVENT_GROUP = 'Group'+CAST(@VAL AS VARCHAR(10)) WHERE  CUSTOMER_ID = @CURRENTCUSTOMER AND DEPT_ID = @CURRENTCUSTOMERDEPT AND LEVEL_ID = @CURRENTVAL
    ELSE  SET @VAL = @VAL + 1

  SET @COUNTER = @COUNTER + 1
END

这是我运行此代码后得到的（这是错误的） ）

this is what I got after running this code (it is wrong)

customer_id	dept_id	level_id	event_group
222		300300	UltraSound1	Group1
222		300300	UltraSound1	Group1
222		300300	Lab		NULL
222		300300	Lab		NULL
222		300300	UltraSound2	Group3
222		300300	UltraSound2	Group3
222		300300	UltraSound2	Group3
444		400400	Intensive Care	NULL
444		400400	UltraSound4	Group6
444		400400	UltraSound5	Group4
444		400400	UltraSound6	Group4
444		400400	Cardiology	NULL
444		400400	Cardiology	NULL
444		400400	UltraSound4	Group6
444		400400	UltraSound4	Group6
444		400400	Xray		NULL

推荐答案

1）从SOURCE GROUP BY中选择level_id level_id

2）加入SOURCE和Group+ ROWNUMBER（）来自（1）



然后是NULL的东西。

1) Select level_id from SOURCE GROUP BY level_id
2) Join SOURCE and "Group" + ROWNUMBER() from (1)

Then the NULL thing.

DECLARE  @TotalRows INT, @CurrentRow INT 
DECLARE  @TransID INT

IF OBJECT_ID('tempdb..#QcAccounts') IS NOT NULL
    DROP TABLE #QcAccounts

create table #QcAccounts
( 
	RowID INT IDENTITY ( 1 , 1 ), 
	TransID INT
) 
INSERT #QcAccounts 
SELECT TransID FROM AgentTransaction 
     
SET @TotalRows = @@ROWCOUNT 
SET @CurrentRow = 1 
     
WHILE (@CurrentRow <= @TotalRows) 
BEGIN 
		SELECT @TransID = TransID 
		FROM   #QcAccounts 
		WHERE  RowID = @CurrentRow 

		print @TransID
		----------------------------------QcTransaction--------------------------------------------------------
		IF NOT EXISTS ( SELECT TransID FROM QcTransaction WHERE TransID = @TransID )
		BEGIN
			INSERT INTO QcTransaction(TransID,AllotedDate) VALUES(@TransID,getdate())
			--UPDATE AgentTransaction SET AuditStatusID = dbo.udfGetAuditStatusID('ALT') 
			--WHERE TransID = @TransID
		END
		-------------------------------------------------------------------------------------------------------

		SET @CurrentRow = @CurrentRow + 1 
END    

这篇关于虽然SQL中的循环不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！