|
我正在尝试计算单个用户发布的总评论数。这是表的表结构comments:
! Y1 \& h% r: W7 w7 N5 OCREATE TABLE `PLD_COMMENT` (5 s8 L& B- ]" P: w
`ID` int(11) NOT NULL auto_increment,
4 Z* E- E$ ?/ Y `ITEM_ID` varchar(11) NOT NULL,
. k/ H7 m& P8 }) _9 f `USER_ID` varchar(11) NOT NULL,, s2 m; h: a% n( u/ w
`USER_NAME` varchar(255) NOT NULL, + J: J7 B+ }1 f- J4 w) K
`COMMENT` longtext,
! Y) @9 j7 \: H3 C `COMMENT_TITLE` varchar(255) default NULL,+ @1 s1 Q- t) w
`COMMENT_RATING` tinyint(1) default '1',
9 a) u3 _. ~8 ?9 ~, G0 Y/ [% p" M `TYPE` int(11) NOT NULL,
* y5 `5 F2 W6 [ `DATE_ADDED` timestamp NOT NULL " Y* Y1 D2 D+ ~! v
default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,' {0 Q. S2 z* ^( }
`IPADDRESS` varchar(15) default NULL,
' J5 h8 `9 b7 {- }4 B8 ]. } `STATUS` varchar(11) NOT NULL,
" n/ q+ c, O( {5 I PRIMARY KEY (`ID`) 0 ?+ }1 j1 B" Z; ]
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
5 L' K8 g8 X3 z( N4 M这里是表结构user表
1 O5 R7 `7 V! a4 U; u$ dCREATE TABLE `pld_user`(
@0 C4 [8 g- n `ID` int(11) NOT NULL auto_increment,
* i) V" b: K# J; U$ e `LOGIN` varchar(100) NOT NULL,
1 C# v, D" J7 k, M `NAME` varchar(255) NOT NULL,
# f+ b4 S& ]' w$ p8 w- W; J `PASSWORD` varchar(46) NOT NULL,3 A$ ?/ h: I0 r! u5 b, V% d3 V
`LEVEL` tinyint(4) NOT NULL default '0',
7 g, M# x/ Y' J9 X' d `RANK` tinyint(4) NOT NULL default '0',: N, ~/ { h4 L( y% O% R+ m
`ACTIVE` tinyint(4) NOT NULL default '0',. }% ~3 y6 |* q9 H2 q
`LAST_LOGIN` timestamp NOT NULL % v0 d3 P c8 d4 b- K' q: @
default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ N2 H4 [ O! Z% l: A3 a `REGISTRATION_DATE` timestamp NOT NULL default '0000-00-00 00:00:00',
% @4 i& [; ^2 S8 ~ `AUTH_IMG` varchar(255) default NULL,
5 ], S+ N$ r1 p; i0 |, c$ Z `AUTH_IMGTN` varchar(255) default NULL,9 w- I; F* n+ m& \7 y: ]* @1 R
`SUBMIT_NOTIF` tinyint(4) NOT NULL default '1', ; X, g" _' k# t, S
`PAYMENT_NOTIF` tinyint(4) NOT NULL default '1', 3 W, ^1 j+ N& ]
`ADDRESS` varchar(255) default NULL, ( Z# ~3 }: Z3 }2 l
`EMAIL` varchar(255) NOT NULL,( Z X) s5 D. u7 ~; r, ]) A) n' V; M
`WEBSITE` varchar(255) default NULL, ' t9 P% ]5 b2 O. w$ L
`WEBSITE_NAME` varchar(255) default NULL,( |2 B: x- p. H9 y
`INFO` varchar(255) default NULL,
. ^/ v/ o6 X; w9 e0 V `ANONYMOUS` tinyint(4) NOT NULL default '0',
$ j: m# m8 @! ^! }8 t2 j. C8 @ `LANGUAGE` varchar(2) default NULL,
5 ^8 M) z3 A( ^- { `AVATAR` varchar(100) default NULL,: p4 d! E9 d3 R+ @
`ICQ` varchar(15) default NULL,
! k% ]: X3 }) [2 S9 v+ I `AIM` varchar(255) default NULL, ( F# r( W3 a% e; `2 h) Q `7 b @3 ]
`YIM` varchar(255) default NULL, 2 J3 B; z% @( g; H1 }5 F
`MSN` varchar(255) default NULL, / A6 y5 T. B. y! Y/ s5 w
`CONFIRM` varchar(10) default NULL,
5 w5 w9 ^$ M9 @ a/ V! _" w3 ^; @ `NEW_PASSWORD` varchar(46) default NULL,
2 r- {5 p0 ~! d. F `EMAIL_CONFIRMED` int(11) NOT NULL default '1',
/ M- b6 L& j4 r6 E( d" { S `LNAME` varchar(255) default NULL,
4 G7 o8 Q; I5 m9 { l! r( d0 ^) G `CITY` varchar(255) default NULL,
" C% j. Z" @ W+ V `STATE` varchar(255) default NULL,
0 P9 y( y9 R1 y; w8 { `DOB` date default NULL,
7 F3 h: @$ T2 c7 S$ _ `UTYPE` tinyint(1) NOT NULL default '0',
6 v% l* k4 \, J' V# e8 q& i PRIMARY KEY (`ID`) ) e" }2 M; O% |3 A8 \) T8 P5 d
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 8 z, j/ r1 i G _! {
COMMENT='Stores all the users with informations'" n i: }6 u# V5 n: Q
这是我的查询:
( \* i3 v9 N _# F- ]9 BSELECT count(c.USER_ID) as total_commments_user , & m0 L, k9 X6 `! e
c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG+ X, h; z* I+ | Y" \
FROM `PLD_COMMENT` c
- K2 b* \8 Y' N+ X* gleft outer join `PLD_USER` u ON (u.ID = c.USER_ID)
# x2 l% N% C5 F5 W5 r1 @3 w) nleft outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID AND l.STATUS='2')
' j: I" Y( @9 L IWHERE c.TYPE = '1'
0 S% @( N; L5 X0 d# W AND c.STATUS = '2'
* D* d) s: T6 P9 Dgroup by c.ID ORDER BY c.ID DESC LIMIT 0 , 3
2 R G0 c% d3 v. g) X6 Z当我运行此查询时,在的每一行中都有1 total_comments_user。
9 E* L }7 D# F# t: o+ }任何的想法?1 o# b0 p+ k. @2 _
4 w5 `7 Q" j' h9 W解决方案: |
|