|
这是我想要做的示例代码,下面是结果:
" S4 g7 r5 z% d* f CREATE TABLE dbo.#TempDoc_DocContRoles (DocID int null, FullName varchar(500), DocContRole ; k9 M4 Q* ?3 Z" A; E
varchar (100), NumRole int null)5 I$ O1 f; z& L1 X
INSERT INTO #TempDoc_DocContRoles(DocID, FullName, DocContRole)' r* a7 w: o4 e/ W$ w: t
SELECT % k; i. j: g& \, c9 o" w
d.DocID, c.FirstName + ' ' + c.LastName as FullName, ldcro.DocContRole 1 |0 B; L8 y) {% v F) {2 x
FROM
" d [( l# s- }5 ~, V+ H M Document as d
4 ^) t$ [# ?( E d3 X JOIN " L: I$ Z% r. j t" V
dbo.Split( ',','30,31') AS l ON d.DocID = cast(l.[Value] AS int)
8 f5 w! J6 F. ^ JOIN , v% _& b! B ~) r
Doc_Contact AS dc ON d.DocID = dc.DocID
8 X; F$ s" n5 s0 U" I" } JOIN $ Q& T0 u$ u, r n K
Contact AS c ON dc.P_Number = c.P_Number : x- `) `( B! J* F% G' v" {
LEFT JOIN
: T0 o6 ?9 K! H. ?8 E1 T, n lkpDocContactRole AS ldcro ON ldcro.DocContRoleID = dc.DocContRoleID
7 h5 L* m# P1 L' v0 ?5 n JOIN
5 m( r1 f4 d, Q: D: l dbo.Split( ',','1,2,7') AS r ON ldcro.DocContRoleID = cast(r.[Value] AS int)
+ N% o2 L* d9 c8 x8 ~/ S' P6 x, Y7 a% T0 |* M6 }
CREATE TABLE dbo.#MaxNumRoles (DocID int null, DocContRole varchar(100), NumRole int null)
2 ]) v( E& \5 o& P: U: O INSERT INTO dbo.#MaxNumRoles (DocID,DocContRole,NumRole)* t: ?9 |$ E( r9 d1 d
SELECT + a- h; D. C# y
DocID, DocContRole, COUNT(*)
* n* s( `+ g" F& P7 x FROM , C3 l. _: P8 q& |, Z
dbo.#TempDoc_DocContRoles
; X# ?" q3 x( c4 |6 \4 w GROUP BY
9 j4 l7 u) R0 c( Y* A DocID, DocContRole
( S0 u* c1 _! p HAVING 7 {% N0 N+ M" L. d8 J( Q
Count(*) > 03 O# J+ p5 }) j+ Q
UPDATE td ( X& J3 R' m% W
SET td.NumRole = mr.NumRole) u& v2 v9 `3 o
FROM dbo.#TempDoc_DocContRoles as td6 M: P$ a3 p8 Z" p! k
INNER JOIN dbo.#MaxNumRoles as mr ON td.DocContRole = mr.docContRole
, D3 w3 w8 U0 ?$ X& C- r# _: i SELECT * FROM dbo.#TempDoc_DocContRoles) m( g6 Z/ w( d' `7 v
DROP TABLE dbo.#TempDoc_DocContRoles 9 ^; B: F6 g/ Z% I% p
DROP TABLE dbo.#MaxNumRoles- D0 ?( U; E0 R+ c/ e
结果:. ~. w5 o& n9 x0 S" I1 S+ K
DocID FullName DocContRole NumRole' l' h4 G0 A) A) E: b
30 Smith Author 3
& e( p3 n9 s( {' V) v1 Q& z2 |, U2 u30 Daln Staff 2
* r. A8 u: ?2 P- K! h2 e$ J30 Dolby Author 3' f, n0 v! c0 O! f! `+ R" ^
31 Tammy Author 3
3 P+ r4 s5 U5 P( G4 E: V30 Barny Author 3. p5 h: p4 U6 Q! Z7 M3 h. C3 h
30 Sanny Res Coor 1
- T1 a6 D: Y' M1 ^4 j, n* |* b7 q30 Johny Staff Rev 2
3 f7 p/ D: W% e& E/ n. a我想实际得到:
6 u4 _: [2 {& ?8 M. F2 N4 I0 GDocID FullName DocContRole NumRole+ G3 D. ]0 c2 J1 |9 Z' s6 p
30 Smith Author 1# R" L- B3 @+ c* r5 m
30 Daln Staff 1; a' T% X4 f* @
30 Dolby Author 2, F; R! [* Y0 @0 g
31 Tammy Author 1) N: D! r% N- {) }6 ?; i% m% m
30 Barny Author 37 a$ h7 `6 i5 |! d
30 Sanny Res Coor 1
( z1 T9 v9 B. j3 N8 \$ j% V: `30 Johny Staff Rev 2
6 C2 f# x4 m7 |' e8 [( L4 C! R# D9 H它应该NumRole按docContRole和增加数字docID(例如作者1,作者2等)。目前,它给出了每位作者的总数DocID。2 V0 N5 i6 t A3 k" f6 |1 j
我的最终目标是获得类似
, h% Y9 S- _0 p* @) j DocID Author_1 Author_2 Author_3 Staff_1 Staff_2 ResCoor_1
3 x% v: _+ N, y30 Smith Dolby Barny Daln Johny Sanny ! E# B3 U9 B# y$ @7 f9 ?' ^
31 Tammy
" n8 K# X$ {) s w1 @$ V, z 4 g1 Q+ u( U" @# m, _2 W& H
解决方案: |
|