I want to update table qcard with data every record from q_id table
我想用q_id表中的每条记录更新表qcard和数据
q_id table field and have 5 record
q_id表字段并有5条记录
---------------
q_id groupcode
---- ---------
1 A01
2 A02
3 A03
4 A05
5 A06
qcard table now have data in field qcard 10001-2000 field loop q_id groupcode not data
qcard表现在有字段qcard中的数据10001-2000字段循环q_id groupcode不是数据
qcard loop q_id groupcode
----- ---- ---- ---------
10001
10002
10003
10004
10005
10006
10007
10008
10009
10010
I need to update qcard table with all record from q_id table for each loop (loop I set from 01,02,03,....to 99) if finish each loop have all record from q_id table
我需要使用q_id表中的所有记录为每个循环更新qcard表(循环我设置从01,02,03,....到99)如果完成每个循环都有来自q_id表的所有记录
if works qcard table this data
如果工作qcard表这个数据
-------------------------------
qcard loop q_id groupcode
----- ---- ---- ---------
10001 01 1 A01
10002 01 2 A02
10003 01 3 A03
10004 01 4 A04
10005 01 5 A05
10006 02 1 A01
10007 02 2 A02
10008 02 3 A03
10009 02 4 A04
10010 02 5 A05
...... untill
1xxxx 99 1 A01
1xxxx 99 2 A02
1xxxx 99 3 A03
1xxxx 99 4 A04
1xxxx 99 5 A05
1 个解决方案
#1
0
Try this Answer,
试试这个答案,
CREATE TABLE #Numbers(N INT)
INSERT INTO #NUMBERS(N)
SELECT TOP 495 ROW_NUMBER() OVER(ORDER BY T1.NUMBER) AS N
FROM MASTER..spt_values T1
CROSS JOIN MASTER..spt_values t2
SELECT N,CASE WHEN LEN(CAST((N+4)/5 as VARCHAR))<2 THEN '0'+CAST((N+4)/5 as VARCHAR) ELSE CAST((N+4)/5 as VARCHAR) END[Loop]
,CASE WHEN N%10 in(1,6) THEN 1 WHEN N%10 in(2,7) THEN 2 WHEN N%10 in(3,8) THEN 3 WHEN N%10 in(4,9) THEN 4 WHEN N%10 in(5,0) THEN 5 END q_id
from #Numbers
UPDATE A SET A.Loop=B.Loop, AND A.q_id=B.q_id
FROM qcard A,#Numbers B
WHERE (A.qcard%10000)=B.N
UPDATE B
SET B.groupcode=A.groupcode
FROM q_id A,qcard B
WHERE A.q_id=B.q_id
DROP TABLE #Numbers
#1
0
Try this Answer,
试试这个答案,
CREATE TABLE #Numbers(N INT)
INSERT INTO #NUMBERS(N)
SELECT TOP 495 ROW_NUMBER() OVER(ORDER BY T1.NUMBER) AS N
FROM MASTER..spt_values T1
CROSS JOIN MASTER..spt_values t2
SELECT N,CASE WHEN LEN(CAST((N+4)/5 as VARCHAR))<2 THEN '0'+CAST((N+4)/5 as VARCHAR) ELSE CAST((N+4)/5 as VARCHAR) END[Loop]
,CASE WHEN N%10 in(1,6) THEN 1 WHEN N%10 in(2,7) THEN 2 WHEN N%10 in(3,8) THEN 3 WHEN N%10 in(4,9) THEN 4 WHEN N%10 in(5,0) THEN 5 END q_id
from #Numbers
UPDATE A SET A.Loop=B.Loop, AND A.q_id=B.q_id
FROM qcard A,#Numbers B
WHERE (A.qcard%10000)=B.N
UPDATE B
SET B.groupcode=A.groupcode
FROM q_id A,qcard B
WHERE A.q_id=B.q_id
DROP TABLE #Numbers