|
|
我正在尝试计算单个用户发布的总评论数。这是表的表结构comments：
" k: U& v+ C9 B# oCREATE TABLE `PLD_COMMENT` (
# p- _- J9 M" N8 Y: @/ N: A `ID` int(11) NOT NULL auto_increment, 0 c: c: T2 o3 g$ Y
`ITEM_ID` varchar(11) NOT NULL,
. p2 m& q7 [4 j9 e2 d) z `USER_ID` varchar(11) NOT NULL,
1 s @1 N: U+ y8 t# o3 d `USER_NAME` varchar(255) NOT NULL,
& B2 c$ a1 |, K1 T0 m, w `COMMENT` longtext,
+ t, W5 i6 Z% C% _4 T `COMMENT_TITLE` varchar(255) default NULL,3 _) R6 t9 d9 v9 q, ]
`COMMENT_RATING` tinyint(1) default '1',
; G4 X6 t( z8 a2 p4 m/ C `TYPE` int(11) NOT NULL,
# n$ s2 F! W/ r( ^4 F `DATE_ADDED` timestamp NOT NULL * l6 S7 e: f9 N& |: g9 C3 b7 N
default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,: o, n5 a% Q2 L! l' u3 ^
`IPADDRESS` varchar(15) default NULL,/ @/ ]- g/ ?* H. B# g+ K
`STATUS` varchar(11) NOT NULL, 5 C+ O5 H7 H8 ?- N9 _9 c- ^9 M
PRIMARY KEY (`ID`) 5 D) N! g2 ?- A; R/ [5 l# v2 Y. J
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
/ K, V6 N9 \" K- z& W这里是表结构user表
* G1 a, D& {; }3 [8 B) {0 wCREATE TABLE `pld_user`(
0 I( {, o* q$ r* f3 c `ID` int(11) NOT NULL auto_increment,
- m. W$ \# I. v8 L( V `LOGIN` varchar(100) NOT NULL,
3 x6 a. \% J: ]' M8 i" o `NAME` varchar(255) NOT NULL,
3 {" `9 C7 x5 w" R+ Q `PASSWORD` varchar(46) NOT NULL,
: ?- I+ ^2 o7 j0 q7 c8 O `LEVEL` tinyint(4) NOT NULL default '0',9 \/ U j' {+ F
`RANK` tinyint(4) NOT NULL default '0',
+ X& q1 q; c* I0 I; j' V1 i `ACTIVE` tinyint(4) NOT NULL default '0',* g) G! J+ X/ j) u4 l
`LAST_LOGIN` timestamp NOT NULL
, I1 M j& n3 d0 e5 L default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP," T4 c, L+ [2 q& I( K
`REGISTRATION_DATE` timestamp NOT NULL default '0000-00-00 00:00:00',2 Z5 x2 E9 t" @1 z) R) P
`AUTH_IMG` varchar(255) default NULL,
0 ~& O) r' S6 E( b/ S; }4 d `AUTH_IMGTN` varchar(255) default NULL,' f7 I# ?6 t$ E. f. k% N/ v1 F
`SUBMIT_NOTIF` tinyint(4) NOT NULL default '1',
7 i [, D5 w; z3 q! u `PAYMENT_NOTIF` tinyint(4) NOT NULL default '1',
. h: i9 E; w5 \# C& |" V `ADDRESS` varchar(255) default NULL, 4 V4 m/ K9 ]+ m \4 p% Y
`EMAIL` varchar(255) NOT NULL,1 G0 `1 {, p" j |5 ?; }
`WEBSITE` varchar(255) default NULL,
7 e: p5 L" T8 o8 s6 _ i `WEBSITE_NAME` varchar(255) default NULL,
) p+ A( B( L/ @* z# ]" M `INFO` varchar(255) default NULL,
) D. e8 [: ?" {0 ]+ M `ANONYMOUS` tinyint(4) NOT NULL default '0', ! e9 i; R) F# {* [
`LANGUAGE` varchar(2) default NULL,
' ~5 I% V: a4 k: j4 a `AVATAR` varchar(100) default NULL,
+ n. Q$ x' q- q* W `ICQ` varchar(15) default NULL, - m, G8 D6 ~/ V' D1 A
`AIM` varchar(255) default NULL,
% R4 U7 \7 [5 x' c4 B `YIM` varchar(255) default NULL, 4 t6 v4 [! V. E2 l* K6 C) T- b
`MSN` varchar(255) default NULL, & _+ z6 ^# p2 n. l# y
`CONFIRM` varchar(10) default NULL," N* S! N8 J, @# |" J: }) n
`NEW_PASSWORD` varchar(46) default NULL,. v2 Y# u7 L n
`EMAIL_CONFIRMED` int(11) NOT NULL default '1', 9 X5 S c6 u4 k- F& u% U0 M
`LNAME` varchar(255) default NULL,1 V& ~5 v; H" ?- }% r9 Q: L
`CITY` varchar(255) default NULL,
8 ?3 @! ]3 D8 l5 `' ? `STATE` varchar(255) default NULL,
- m( s& e& `2 G+ j& q' R/ ` `DOB` date default NULL,
! s3 P" I. W$ L J `UTYPE` tinyint(1) NOT NULL default '0',1 S) p1 i* M0 Y% c- Q
PRIMARY KEY (`ID`)
& Y+ H7 n& s/ L- O% z) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
4 ?8 s) F3 }8 ?3 i: _+ N" m COMMENT='Stores all the users with informations'6 e+ k$ y6 I+ V; R* A! \
这是我的查询：
1 Z3 G$ [; [: n* t) rSELECT count(c.USER_ID) as total_commments_user ,
; _, ?0 H7 \" O" ~ `, i c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG
@9 Q% r: y" e& C$ P' \FROM `PLD_COMMENT` c* m3 c1 E# x- D! H9 _
left outer join `PLD_USER` u ON (u.ID = c.USER_ID) 6 w. b9 Q- o$ G( u2 L/ h
left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID AND l.STATUS='2') $ s( t! Q& ]- r0 n7 ]
WHERE c.TYPE = '1'
9 l) S; c8 a c6 A3 f5 _ AND c.STATUS = '2'
2 V4 n: I! B" v6 ^ [ |' ?6 S% Mgroup by c.ID ORDER BY c.ID DESC LIMIT 0 , 39 a/ _9 s8 i! @% _4 u; w5 E
当我运行此查询时，在的每一行中都有1 total_comments_user。( J4 }# Y1 n( } r0 U) p" R
任何的想法？
5 M7 ~+ `8 D g- d/ L
4 c, c7 ]7 |" Z3 |) F5 `0 p7 ^- v解决方案: |
|
技术问答
297 人阅读
|
0 人回复
|
2023-09-11
