|
这是我想要做的示例代码,下面是结果:) X: i- H6 f) H1 g8 N# w; V
CREATE TABLE dbo.#TempDoc_DocContRoles (DocID int null, FullName varchar(500), DocContRole
3 k5 Y! B* W0 X' c, i6 h varchar (100), NumRole int null)
+ s" {: f. `+ f INSERT INTO #TempDoc_DocContRoles(DocID, FullName, DocContRole)8 r2 u( y+ v, W+ t1 W$ L+ `
SELECT
& D# s0 D( c6 {3 G8 `6 N* h d.DocID, c.FirstName + ' ' + c.LastName as FullName, ldcro.DocContRole
( y2 H; \6 |" j. S9 w8 G; s FROM - u F+ P) o1 {/ a! L* H6 `5 R, b3 a
Document as d
$ X: ^/ T; O% W3 l. W" Y JOIN
1 w9 u4 n3 \; j8 q dbo.Split( ',','30,31') AS l ON d.DocID = cast(l.[Value] AS int)
" [8 }$ C0 v7 Z. @7 r% n' l* [, j JOIN
) g$ r: T7 _. V8 E0 p. x& } Doc_Contact AS dc ON d.DocID = dc.DocID " ^+ v+ P# c9 P, }( F! e
JOIN # e m7 g0 X. o+ `7 g
Contact AS c ON dc.P_Number = c.P_Number ' o9 F3 N3 ~4 e V+ H; Q
LEFT JOIN
' H R8 e- [( u9 J8 A; |% a h lkpDocContactRole AS ldcro ON ldcro.DocContRoleID = dc.DocContRoleID
+ ?5 K8 k- _8 @% c+ b( S. O JOIN
+ Q$ z3 \& c* A$ K9 j: K( u dbo.Split( ',','1,2,7') AS r ON ldcro.DocContRoleID = cast(r.[Value] AS int)
1 r) y1 E# c. |- F; y
$ v3 n* h# k& w6 n CREATE TABLE dbo.#MaxNumRoles (DocID int null, DocContRole varchar(100), NumRole int null)9 p7 c8 h- k, k" j% q' U g
INSERT INTO dbo.#MaxNumRoles (DocID,DocContRole,NumRole)
1 Z9 X" W v0 @) H8 ]4 o8 j SELECT + u- X1 T2 D4 [
DocID, DocContRole, COUNT(*)
4 ?/ a/ Q z! D/ \; D2 n FROM
& N B' R4 U8 ?& o8 l dbo.#TempDoc_DocContRoles $ J1 M6 d( C. R( k' s9 Z0 c
GROUP BY 2 o; `1 N8 G* x V' c, |' {( }
DocID, DocContRole
2 Q2 c8 S) ]9 P9 Y7 j! y2 L9 ] HAVING
* a7 x2 @% Y3 U" ~ Count(*) > 0
* e o5 t5 ~. V) g) _ UPDATE td 2 \4 @, x1 K+ h+ a0 q3 i e
SET td.NumRole = mr.NumRole% F4 f9 p- n) r. U
FROM dbo.#TempDoc_DocContRoles as td
1 U* M7 ^, _( D% d+ C5 s+ N INNER JOIN dbo.#MaxNumRoles as mr ON td.DocContRole = mr.docContRole- H3 {7 o/ U- Z& }; a
SELECT * FROM dbo.#TempDoc_DocContRoles
9 t* t; o7 M- O# b6 p% N* x DROP TABLE dbo.#TempDoc_DocContRoles
( C& c$ k1 X3 l7 G$ a/ L0 L DROP TABLE dbo.#MaxNumRoles s' y% h& Q( _# p$ p- t1 Z* p+ ^
结果:
+ d' C$ |* h3 y7 o6 |DocID FullName DocContRole NumRole
6 v, ^5 O3 p( s8 f Q. S30 Smith Author 3+ ^1 N( `/ T0 `# S) m6 R9 W) T. z
30 Daln Staff 2
1 k0 Z$ @, Q' u, `30 Dolby Author 3
( _# p5 S, U. ]3 y0 H: z31 Tammy Author 3
% l7 N& p2 r, j30 Barny Author 3
6 [9 X8 H. }& P j30 Sanny Res Coor 1, z; G( H" K( P6 c s4 @% u- E$ h
30 Johny Staff Rev 22 i6 y A) k0 t
我想实际得到:1 a$ U7 Z* w% b5 M0 O4 e% `5 H
DocID FullName DocContRole NumRole: i" V2 e- f# g$ E) s3 Y
30 Smith Author 1
& {: |" a) j |/ L6 V. Y! T; f30 Daln Staff 1# o) _- {2 f- r8 e W
30 Dolby Author 2
* Z3 C ]3 @, P31 Tammy Author 18 C0 `0 H: f; B9 M8 Z
30 Barny Author 3
8 b0 V( Q" `' Q% d3 t7 r30 Sanny Res Coor 1( n* z: B$ v8 o! n$ E8 q6 U
30 Johny Staff Rev 2
2 \. V5 R( |. H( S8 v4 D它应该NumRole按docContRole和增加数字docID(例如作者1,作者2等)。目前,它给出了每位作者的总数DocID。
% V" o) \$ l6 W1 g0 Z! @我的最终目标是获得类似
. n* X2 a- g; V/ N2 D6 n( f4 M) Q- F DocID Author_1 Author_2 Author_3 Staff_1 Staff_2 ResCoor_15 l: j, Q- C- E* N f8 d5 K+ {( |
30 Smith Dolby Barny Daln Johny Sanny
. d/ `$ F* T3 X1 U31 Tammy
6 `' o2 P7 Y t
6 o2 V& Y# }6 V$ u! z解决方案: |
|