mysql计数不能正常工作? [英] mysql count not working properly?
问题描述
我想计算单个用户发布的评论总数。下面是注释
表的表结构:
CREATE TABLE` PLD_COMMENT`(
pre>
`ID` int(11)NOT NULL auto_increment,
`ITEM_ID` varchar(11)NOT NULL,
`USER_ID`varchar(11)NOT NULL,
`USER_NAME` varchar(255)NOT NULL,
`COMMENT` longtext,
`COMMENT_TITLE`varchar(255)default NULL,
`COMMENT_RATING` tinyint b $ b`TYPE` int(11)NOT NULL,
`DATE_ADDED` timestamp NOT NULL
默认CURRENT_TIMESTAMP更新CURRENT_TIMESTAMP,
`IPADDRESS`varchar(15)default NULL,
`STATUS` varchar(11)NOT NULL,
PRIMARY KEY(`ID`)
)ENGINE = MyISAM AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1
以下是
user
表的表结构CREATE TABLE`pld_user`(
`ID` int(11)NOT NULL auto_increment,
`LOGIN` varchar(100)NOT NULL,
`NAME` varchar(255)NOT NULL,
`PASSWORD`varchar(46)NOT NULL,
`LEVEL` tinyint(4)NOT NULL默认值'0',
` (4)NOT NULL默认值'0',
`ACTIVE` tinyint(4)NOT NULL默认值'0',
`LAST_LOGIN` timestamp NOT NULL
默认CURRENT_TIMESTAMP更新CURRENT_TIMESTAMP,
`REGISTRATION_DATE` timestamp NOT NULL default'0000-00-00 00:00:00',
`AUTH_IMG` varchar(255)default NULL,
`AUTH_IMGTN` varchar(255)default NULL,
`SUBMIT_NOTIF` tinyint(4)NOT NULL默认'1',
`PAYMENT_NOTIF` tinyint(4)NOT NULL默认为'1',
`ADDRESS` varchar(255)
`EMAIL` varchar(255)NOT NULL,
`WEBSITE` varchar(255)default NULL,
`WEBSITE_NAME` varchar(255)default NULL,
`INFO` varchar (255)default NULL,
`ANONYMOUS` tinyint(4)NOT NULL default'0',
`LANGUAGE`varchar(2)default NULL,
`AVATAR`varchar NULL,
`ICQ` varchar(15)default NULL,
`AIM` varchar(255)default NULL,
`YIM` varchar(255)default NULL,
`MSN `varchar(255)default NULL,
`CONFIRM` varchar(10)default NULL,
`NEW_PASSWORD`varchar(46)default NULL,
`EMAIL_CONFIRMED`int '1',
`LNAME` varchar(255)default NULL,
`CITY` varchar(255)default NULL,
`STATE` varchar(255)default NULL,
`DOB` date default NULL,
`UTYPE` tinyint(1)NOT NULL default'0',
PRIMARY KEY(`ID`)
)ENGINE = MyISAM AUTO_INCREMENT = 8 DEFAULT CHARSET = latin1
COMMENT ='存储所有具有信息的用户
这里是我的查询: / p>
SELECT count(c.USER_ID)as total_commments_user,
c。*,u.NAME,l.TITLE as LINK_TITLE ,u.AUTH_IMG
FROM`PLD_COMMENT`c
左外连接`PLD_USER` u ON(u.ID = c.USER_ID)
左外连接`PLD_LINK` l ON = c.ITEM_ID AND l.STATUS ='2')
WHERE c.TYPE ='1'
AND c.STATUS ='2'
group by c.ID ORDER BY c。 ID DESC LIMIT 0,3
当我运行这个查询时,我在<$ c 解决方案 div您需要添加
SELECT
子句中除了c.USER_ID
到GROUP BY
子句,如下所示:ID,c.otherfields,l.title,..
EDIT:我认为以下内容会正常工作:
SELECT count(c.USER_ID)as total_commments_user,
例如:
c。*, u.NAME,l.TITLE作为LINK_TITLE,u.AUTH_IMG
从`PLD_COMMENT` c
左外连接`PLD_USER` u ON(u.ID = c.USER_ID)
左外连接`PLD_LINK` l ON(l.ID = c.ITEM_ID)
group by c.ITEM_ID,c.USER_ID
ORDER BY c.USER_ID,l.ID
如果您有以下示例数据:
$情况1:用户
PLD_LINK:
ID状态TITLE
1 1 title1
2 2 title2
PLD_USER:
ID NAME
8 Mahmoud
9 Ahmed
p>
PLD_COMMENT:
ID ITEM_ID USER_ID状态
4 1 8 1
5 1 8 1
6 1 8 1
7 2 8 2
8 2 8 2
9 1 9 1
10 1 9 1
Mahmoud
显示两次: $ b
$ b然后,上一个查询将为每个用户和每个项目提供注释的计数,例如:
total_commments_user ID ITEM_ID USER_ID名称
3 4 1 8 Mahmoud
2 7 2 8 Mahmoud
2 9 1 9 Ahmed
请注意,用户
Mahmoud
会显示两次不同的计数,因为他有不同的Item_Id
。
用户
Mahmoud
只显示一次:
如果您想获取每个用户的评论数所有项目,那么您将需要仅按
USER_ID
分组,您将获得:total_commments_user ID ITEM_ID USER_ID名称
5 4 1 8 Mahmoud
3 9 1 9 Ahmed
正如你现在可以看到的,用户
Mahmoud
只显示一次,因为我们ingonredItem_Id
。
然后,您可以按状态或过去过滤。
I am trying to count the total comments posted by single user. Here is the table structure of the
comments
table:CREATE TABLE `PLD_COMMENT` ( `ID` int(11) NOT NULL auto_increment, `ITEM_ID` varchar(11) NOT NULL, `USER_ID` varchar(11) NOT NULL, `USER_NAME` varchar(255) NOT NULL, `COMMENT` longtext, `COMMENT_TITLE` varchar(255) default NULL, `COMMENT_RATING` tinyint(1) default '1', `TYPE` int(11) NOT NULL, `DATE_ADDED` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `IPADDRESS` varchar(15) default NULL, `STATUS` varchar(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
Here is the table structure for
user
tableCREATE TABLE `pld_user`( `ID` int(11) NOT NULL auto_increment, `LOGIN` varchar(100) NOT NULL, `NAME` varchar(255) NOT NULL, `PASSWORD` varchar(46) NOT NULL, `LEVEL` tinyint(4) NOT NULL default '0', `RANK` tinyint(4) NOT NULL default '0', `ACTIVE` tinyint(4) NOT NULL default '0', `LAST_LOGIN` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `REGISTRATION_DATE` timestamp NOT NULL default '0000-00-00 00:00:00', `AUTH_IMG` varchar(255) default NULL, `AUTH_IMGTN` varchar(255) default NULL, `SUBMIT_NOTIF` tinyint(4) NOT NULL default '1', `PAYMENT_NOTIF` tinyint(4) NOT NULL default '1', `ADDRESS` varchar(255) default NULL, `EMAIL` varchar(255) NOT NULL, `WEBSITE` varchar(255) default NULL, `WEBSITE_NAME` varchar(255) default NULL, `INFO` varchar(255) default NULL, `ANONYMOUS` tinyint(4) NOT NULL default '0', `LANGUAGE` varchar(2) default NULL, `AVATAR` varchar(100) default NULL, `ICQ` varchar(15) default NULL, `AIM` varchar(255) default NULL, `YIM` varchar(255) default NULL, `MSN` varchar(255) default NULL, `CONFIRM` varchar(10) default NULL, `NEW_PASSWORD` varchar(46) default NULL, `EMAIL_CONFIRMED` int(11) NOT NULL default '1', `LNAME` varchar(255) default NULL, `CITY` varchar(255) default NULL, `STATE` varchar(255) default NULL, `DOB` date default NULL, `UTYPE` tinyint(1) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COMMENT='Stores all the users with informations'
Here is my query:
SELECT count(c.USER_ID) as total_commments_user , c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG FROM `PLD_COMMENT` c left outer join `PLD_USER` u ON (u.ID = c.USER_ID) left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID AND l.STATUS='2') WHERE c.TYPE = '1' AND c.STATUS = '2' group by c.ID ORDER BY c.ID DESC LIMIT 0 , 3
When I run this query I got 1 in each row under
total_comments_user
.Any idea?
解决方案You need to add all the columns you are selecting in the
SELECT
clause except thec.USER_ID
to theGROUP BY
clause, like this:group by c.ID, c.otherfields, l.title,..
EDIT: I think the following will work properly:
SELECT count(c.USER_ID) as total_commments_user , c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG FROM `PLD_COMMENT` c left outer join `PLD_USER` u ON (u.ID = c.USER_ID) left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID) group by c.ITEM_ID, c.USER_ID ORDER BY c.USER_ID, l.ID
Example: If you have the following sample data:
PLD_LINK:
ID STATUS TITLE 1 1 title1 2 2 title2
PLD_USER:
ID NAME 8 Mahmoud 9 Ahmed
PLD_COMMENT:
ID ITEM_ID USER_ID STATUS 4 1 8 1 5 1 8 1 6 1 8 1 7 2 8 2 8 2 8 2 9 1 9 1 10 1 9 1
Case 1: the user
Mahmoud
is displayed twice:Then, the previous query will give you the count of the comments for each User and for each item too, like this:
total_commments_user ID ITEM_ID USER_ID Name 3 4 1 8 Mahmoud 2 7 2 8 Mahmoud 2 9 1 9 Ahmed
Notice that the user
Mahmoud
is displayed twice with a different count, becouse he has differentItem_Id
.
Case 2: the user
Mahmoud
is diplayed only one time:If you want to get the count of comments for each user for all items then you will need to group by only the
USER_ID
and you will got:total_commments_user ID ITEM_ID USER_ID Name 5 4 1 8 Mahmoud 3 9 1 9 Ahmed
As you can see now the user
Mahmoud
is displayed only one time, becouse we ingonredItem_Id
.You can then filter by status or what ever.
这篇关于mysql计数不能正常工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文