|
|
我正在尝试计算单个用户发布的总评论数。这是表的表结构comments：
0 P3 b* O9 p! {6 q [1 PCREATE TABLE `PLD_COMMENT` (! ~/ ~9 a. c2 H. {: b
`ID` int(11) NOT NULL auto_increment,
* |7 U" A n0 H `ITEM_ID` varchar(11) NOT NULL,
& x$ G$ _# u I4 i `USER_ID` varchar(11) NOT NULL,
3 {/ o; d: n& R `USER_NAME` varchar(255) NOT NULL,
! U1 h2 N: t! R: i `COMMENT` longtext,
2 m* G# f- ?8 F/ M5 ]! x8 o0 E `COMMENT_TITLE` varchar(255) default NULL,% n5 k5 }1 V: o+ ~- F, W
`COMMENT_RATING` tinyint(1) default '1', , C- m O7 J' i! v) I3 I K
`TYPE` int(11) NOT NULL, " O4 \& k# x+ @' ]- m/ y g' n
`DATE_ADDED` timestamp NOT NULL
/ Z" R2 k9 `# p8 Z) _9 a- J/ {9 j default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,2 q4 ] I; v; l1 X1 j( i8 f" K
`IPADDRESS` varchar(15) default NULL,1 A* Y+ f, n6 Y H% M4 ^6 }
`STATUS` varchar(11) NOT NULL,
0 y, G5 G3 T; m: ^- b PRIMARY KEY (`ID`)
; _2 A4 p3 P4 g9 @( l# ~) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
! x' c) C/ }( Q$ h; q% J+ K这里是表结构user表
) s0 @7 x3 l% Y5 @CREATE TABLE `pld_user`(
2 V5 ~; p( u* c/ \. a3 H3 _ `ID` int(11) NOT NULL auto_increment,! A; H# M' W+ W- X' Q0 n
`LOGIN` varchar(100) NOT NULL,, k) M, ?# d/ v1 O$ ?
`NAME` varchar(255) NOT NULL,
8 N& N8 E( T$ C6 r1 U/ W6 ] `PASSWORD` varchar(46) NOT NULL,
4 q( R% |3 h! m& |8 c- }9 @8 ] `LEVEL` tinyint(4) NOT NULL default '0',2 _: ]' ?9 S6 V
`RANK` tinyint(4) NOT NULL default '0',
. ?$ h" C) |/ _, K& z0 ~ r `ACTIVE` tinyint(4) NOT NULL default '0',
1 X! U6 \% D: G/ G0 I `LAST_LOGIN` timestamp NOT NULL
0 O R, y5 `: v2 ?) H8 i default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,) t6 Z6 ?0 M: y) _9 q
`REGISTRATION_DATE` timestamp NOT NULL default '0000-00-00 00:00:00',- K2 D3 s( Y) n; V' I% l
`AUTH_IMG` varchar(255) default NULL,
" J) b5 f3 o- o4 B* _" k r `AUTH_IMGTN` varchar(255) default NULL,) U; b, Z0 r1 }% o$ l. N
`SUBMIT_NOTIF` tinyint(4) NOT NULL default '1', - y, p. E. s6 p: D
`PAYMENT_NOTIF` tinyint(4) NOT NULL default '1',
& c8 ^# P; {% i$ ? `ADDRESS` varchar(255) default NULL,
" B4 ^, {. |* a& R1 G2 { `EMAIL` varchar(255) NOT NULL,9 X" ?! X) K7 Z1 D
`WEBSITE` varchar(255) default NULL, ( J1 u3 E2 }6 O) M. b1 r& [
`WEBSITE_NAME` varchar(255) default NULL,' e" Z- B! z$ ~6 A! }
`INFO` varchar(255) default NULL,
, @: v% U4 F/ _" c( ^' T! i `ANONYMOUS` tinyint(4) NOT NULL default '0',
0 k5 r6 c$ Q, V! j. o2 M8 v- U! S `LANGUAGE` varchar(2) default NULL,
: `* C* N5 ?* V4 ] `AVATAR` varchar(100) default NULL,
/ X( N3 [3 C4 @" q! E `ICQ` varchar(15) default NULL, : ]% T$ Y: L/ j Z8 W* m' v
`AIM` varchar(255) default NULL,
, @' I& }3 ?0 K# y `YIM` varchar(255) default NULL, & J: C$ H X. C1 V& V9 }9 |6 k
`MSN` varchar(255) default NULL, & o" X; L7 }: @, M! T, I
`CONFIRM` varchar(10) default NULL,
$ P# P! L6 @# r# o; A2 l1 ` `NEW_PASSWORD` varchar(46) default NULL,
! f- G9 ^7 f# S `EMAIL_CONFIRMED` int(11) NOT NULL default '1', 5 \% }" i& g* @5 X
`LNAME` varchar(255) default NULL,! ~! |1 R: j% n! g* y5 h
`CITY` varchar(255) default NULL, $ v( C- p/ H3 |) Z
`STATE` varchar(255) default NULL,$ H- ` S9 W; k* D' K4 P: m
`DOB` date default NULL,
8 E8 `6 C$ g2 |0 g \ `UTYPE` tinyint(1) NOT NULL default '0',7 q' A) T9 r5 D9 P
PRIMARY KEY (`ID`) ! v1 \- @" z7 R) E+ p/ ^
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
: F- O6 [/ _! u/ C6 w COMMENT='Stores all the users with informations'# J) U5 R/ }; D8 ?8 f+ j: k
这是我的查询：1 g. _9 W d1 Y# Q6 _4 w
SELECT count(c.USER_ID) as total_commments_user , ! Y6 A; G4 \. y% l: _' r, p& D; q; b
c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG1 @' d. [0 S% b9 _; T7 r8 @9 w
FROM `PLD_COMMENT` c' T/ r! W7 P* b
left outer join `PLD_USER` u ON (u.ID = c.USER_ID) 2 z1 ^8 J8 H9 W4 K
left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID AND l.STATUS='2')
b9 X2 ~: l, d: U. ~' a& wWHERE c.TYPE = '1'
7 X. v% f# O: D AND c.STATUS = '2' 0 j/ M3 J9 o7 g" s- s" m! l
group by c.ID ORDER BY c.ID DESC LIMIT 0 , 3
* K7 U" b% t3 x当我运行此查询时，在的每一行中都有1 total_comments_user。" S' Y; b6 I O7 I
任何的想法？
8 n6 j- Z7 J4 J6 ~6 y ~ % R1 V) T, p7 o
解决方案: |
|
技术问答
408 人阅读
|
0 人回复
|
2023-09-11
