用户表U
字段:Uid(自动编号)
文章表A
字段:CUid(用户id),AView(浏览数),Aid(针对这个id写的文章)
评论表C
字段:CUid(用户id),CView(支持数),Cid(针对这个id写的评论,和Aid是同一数字的)上面的字段都是数字类型,CUid和CUid关联用户表的Uid假如上面A表和C表针对的id是:123文章记录sql可以: select * from A where Aid=123
评论记录sql可以: select * from C where Cid=123好,我想要的结果是:
显示:有对Aid=123写文章的用户 和 有对Cid=123评论的用户,
排序:( ((文章数*2)+sun(AView)) + ((评论数*2)+sun(CView)) ) desc
就是文章数*2加上文章总浏览数,再加上评论数*2+评论总支持数,数字最大的用户排前面。不知道说明白了没有,急求一条高效sql,在线等,并在线实时测试高手给出的sql
字段:Uid(自动编号)
文章表A
字段:CUid(用户id),AView(浏览数),Aid(针对这个id写的文章)
评论表C
字段:CUid(用户id),CView(支持数),Cid(针对这个id写的评论,和Aid是同一数字的)上面的字段都是数字类型,CUid和CUid关联用户表的Uid假如上面A表和C表针对的id是:123文章记录sql可以: select * from A where Aid=123
评论记录sql可以: select * from C where Cid=123好,我想要的结果是:
显示:有对Aid=123写文章的用户 和 有对Cid=123评论的用户,
排序:( ((文章数*2)+sun(AView)) + ((评论数*2)+sun(CView)) ) desc
就是文章数*2加上文章总浏览数,再加上评论数*2+评论总支持数,数字最大的用户排前面。不知道说明白了没有,急求一条高效sql,在线等,并在线实时测试高手给出的sql
(select count(Akey) from A where A.Aid=123)*2)+(select sum(A.Aview) from A where A.Aid=123)
+(select count(Ckey) from C where C.Cid=123)*2)+(select sum(C.Cview) from C where C.Cid=123)
) as cou
from U,A,C
where U.uid=A.Cuid AND A.Aid=C.Cid and A.Aid=123 order by cou desc根据你的说明我的理解.楼主最好把表和测试数据 已经所要的结果给贴出来.
where U.uid=A.Cuid AND A.Aid=C.Cid and A.Aid=123
而且这个好象也有问题,U.uid=A.Cuid那文章的用户呢?文章表A
字段:CUid(用户id),AView(浏览数),Aid(针对这个id写的文章)
这个写错了,让我搞得跟评论用户的id一样了,应该是AUid(用户id)
不过搜出来的只有评论用户,没有写文章用户,where (U.uid=A.Cuid or U.uid=C.Cuid) AND A.Aid=C.Cid and A.Aid=123 order by cou desc
我改成这样就有文章用户了,还过排序好象有点问题,没有按预期的排,等我查查是什么问题先
(select ((count(Akey)*2)+sum(A.Aview)) as Acount from A where A.Aid=123)
+(select ((count(Ckey)*2)+sum(C.Cview)) as Ccount from C where C.Cid=123)
) as cou还有这个要select distinct U.*,要不好多重复用户。
下面三個表,用戶表,文章表,評論表,123是一個這視頻的id,是針對這個視頻寫的文章和評論。CREATE TABLE `user` (
`iUKey` int(8) NOT NULL auto_increment,
`cUserName` varchar(30) NOT NULL default '',
PRIMARY KEY (`iUKey`)
) ENGINE=MyISAM;
INSERT INTO `user` VALUES (1, 'user1');
INSERT INTO `user` VALUES (2, 'user2');
INSERT INTO `user` VALUES (3, 'user3');CREATE TABLE `article` (
`iAKey` int(8) NOT NULL auto_increment,
`iAUId` int(10) NOT NULL default '0',
`iCView` int(10) NOT NULL default '0',
`iAVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iAKey`)
) ENGINE=MyISAM;
INSERT INTO `article` VALUES (1,2, 25,123);
INSERT INTO `article` VALUES (2,2, 12,123);
INSERT INTO `article` VALUES (3,1, 20,123);CREATE TABLE `comment` (
`iCKey` int(8) NOT NULL auto_increment,
`iCUId` int(10) NOT NULL default '0',
`iCNum` int(10) NOT NULL default '0',
`iCVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iCKey`)
) ENGINE=MyISAM;
INSERT INTO `comment` VALUES (1,3, 5,123);
INSERT INTO `comment` VALUES (2,1, 8,123);
INSERT INTO `comment` VALUES (3,3, 1,123);用於排序的計算
user1:(1篇文章*2+文章點擊數20)+(發了1條評論*2+評論被支持8)=22+10=32
user2:(2篇文章*2+2篇文章點擊數37)+(發了0條評論*2+該評論被支持0)=41+0=41
user3:(0篇文章*2+該文章點擊數0)+(發了2條評論*2+2條評論被支持6)=0+6=6查詢article和comment表結果由大到小排序顯示用戶名:
user2
user1
user3
user3:(0篇文章*2+該文章點擊數0)+(發了2條評論*2+2條評論被支持6)=0+10=10
+-------+-----------+
| iUKey | cUserName |
+-------+-----------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+-------+-----------+
3 rows in set (0.11 sec)mysql> select * from article;
+-------+-------+--------+-----------+
| iAKey | iAUId | iCView | iAVedioId |
+-------+-------+--------+-----------+
| 1 | 2 | 25 | 123 |
| 2 | 2 | 12 | 123 |
| 3 | 1 | 20 | 123 |
+-------+-------+--------+-----------+
3 rows in set (0.00 sec)mysql> select * from comment;
+-------+-------+-------+-----------+
| iCKey | iCUId | iCNum | iCVedioId |
+-------+-------+-------+-----------+
| 1 | 3 | 5 | 123 |
| 2 | 1 | 8 | 123 |
| 3 | 3 | 1 | 123 |
+-------+-------+-------+-----------+
3 rows in set (0.00 sec)mysql> select cUserName
-> from user u
-> order by
-> IFNULL((select count(*)*2+sum(iCView) from article where iAUId=u.iUKey),0) +
-> IFNULL((select count(*)*2+sum(iCNum) from comment where iCUId=u.iUKey),0) desc;
+-----------+
| cUserName |
+-----------+
| user2 |
| user1 |
| user3 |
+-----------+
3 rows in set (0.00 sec)mysql>
INSERT INTO `comment` VALUES (1,3, 5,123);
INSERT INTO `comment` VALUES (2,1, 8,123);
INSERT INTO `comment` VALUES (3,3, 1,123);
INSERT INTO `comment` VALUES (3,3, 1,393);
INSERT INTO `comment` VALUES (3,3, 1,68);
from user u
order by
IFNULL((select count(*)*2+sum(iCView) from article where iAUId=u.iUKey and iAVedioId=123 ),0) +
IFNULL((select count(*)*2+sum(iCNum) from comment where iCUId=u.iUKey and iCVedioId=123),0) desc;所以说准备测试用例也是花时间和仔细认真的。
INSERT INTO `comment` VALUES (3,3, 1,68);
这两条能插入吗?iCKey能重复?
+-------+-----------+
| iUKey | cUserName |
+-------+-----------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3
4| user4
5| user5
+-------+-----------+
如上面,还有更多用户,而我只要显示:
+-----------+
| cUserName |
+-----------+
| user2 |
| user1 |
| user3 |
+-----------+
但那样写把user4,user5都显示了
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
IFNULL((select count(*)*2+sum(iCNum) from comment where iCUId=AA.iUKey and iCVedioId=123),0)) as getcount
from user as AA left join article as BB on BB.iAUId=AA.iUKey and BB.iAVedioId=123 left join comment as CC
on CC.iCUId=AA.iUKey and CC.iCVedioId=123 where CC.iCVedioId=123 or BB.iAVedioId=123 order by getcount desc;";
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`iUKey` int(8) NOT NULL auto_increment,
`cUserName` varchar(30) NOT NULL default '',
PRIMARY KEY (`iUKey`)
) ENGINE=MyISAM;
INSERT INTO `user` VALUES (1, 'user1');
INSERT INTO `user` VALUES (2, 'user2');
INSERT INTO `user` VALUES (3, 'user3');
INSERT INTO `user` VALUES (4, 'user4');
INSERT INTO `user` VALUES (5, 'user5');
INSERT INTO `user` VALUES (6, 'user6');CREATE TABLE `article` (
`iAKey` int(8) NOT NULL auto_increment,
`iAUId` int(10) NOT NULL default '0',
`iCView` int(10) NOT NULL default '0',
`iAVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iAKey`)
) ENGINE=MyISAM;
INSERT INTO `article` VALUES (1,2, 25,123);
INSERT INTO `article` VALUES (2,2, 12,123);
INSERT INTO `article` VALUES (3,1, 20,123);CREATE TABLE `comment` (
`iCKey` int(8) NOT NULL auto_increment,
`iCUId` int(10) NOT NULL default '0',
`iCNum` int(10) NOT NULL default '0',
`iCVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iCKey`)
) ENGINE=MyISAM;
INSERT INTO `comment` VALUES (1,3, 5,123);
INSERT INTO `comment` VALUES (2,1, 8,123);
INSERT INTO `comment` VALUES (3,3, 1,123);用於排序的計算
user1:(1篇文章*2+文章點擊數20)+(發了1條評論*2+評論被支持8)=22+10=32
user2:(2篇文章*2+2篇文章點擊數37)+(發了0條評論*2+該評論被支持0)=41+0=41
user3:(0篇文章*2+該文章點擊數0)+(發了2條評論*2+2條評論被支持6)=0+6=6查詢article和comment表結果由大到小排序顯示用戶名:
user2
user1
user3
-> from user u
-> where exists (select 1 from article where iAUId=u.iUKey)
-> or exists (select 1 from comment where iCUId=u.iUKey)
-> order by
-> IFNULL((select count(*)*2+sum(iCView) from article where iAUId=u.iUKey),0) +
-> IFNULL((select count(*)*2+sum(iCNum) from comment where iCUId=u.iUKey),0) desc;
+-----------+
| cUserName |
+-----------+
| user2 |
| user1 |
| user3 |
+-----------+
3 rows in set (0.00 sec)mysql>
(SELECT `iCUId`,SUM(`iCNum`) AS na
FROM `comment` GROUP BY `iCUId`
) b1 ON a1.iukey=b1.`iCUId`
LEFT JOIN (SELECT `iAUId`,COUNT(*) AS nc,SUM(`iCView`) AS nb FROM `article` GROUP BY `iAUId`) b2
ON a1.iukey=b2.`iaUId`
WHERE NOT (na IS NULL AND nb IS NULL)
ORDER BY COALESCE(nc,0)*2+COALESCE(nb,0)+COALESCE(na,0) DESC
`iUKey` int(8) NOT NULL auto_increment,
`cUserName` varchar(30) NOT NULL default '',
PRIMARY KEY (`iUKey`)
) ENGINE=MyISAM;
INSERT INTO `user` VALUES (1, 'user1');
INSERT INTO `user` VALUES (2, 'user2');
INSERT INTO `user` VALUES (3, 'user3');
INSERT INTO `user` VALUES (4, 'user4');
INSERT INTO `user` VALUES (5, 'user5');
INSERT INTO `user` VALUES (6, 'user6');CREATE TABLE `article` (
`iAKey` int(8) NOT NULL auto_increment,
`iAUId` int(10) NOT NULL default '0',
`iCView` int(10) NOT NULL default '0',
`iAVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iAKey`)
) ENGINE=MyISAM;
INSERT INTO `article` VALUES (1,2, 25,123);
INSERT INTO `article` VALUES (2,2, 12,123);
INSERT INTO `article` VALUES (3,1, 20,123);
INSERT INTO `article` VALUES (4,1, 20,231);
INSERT INTO `article` VALUES (5,1, 20,295);CREATE TABLE `comment` (
`iCKey` int(8) NOT NULL auto_increment,
`iCUId` int(10) NOT NULL default '0',
`iCNum` int(10) NOT NULL default '0',
`iCType` tinyint(1) NOT NULL default 0,
`iCVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iCKey`)
) ENGINE=MyISAM;
INSERT INTO `comment` VALUES (1,3, 5,1,123);
INSERT INTO `comment` VALUES (2,1, 8,1,123);
INSERT INTO `comment` VALUES (3,3, 1,1,123);
INSERT INTO `comment` VALUES (4,5, 1,2,2);
INSERT INTO `comment` VALUES (5,1, 1,2,3);
INSERT INTO `comment` VALUES (6,3, 1,2,2);
INSERT INTO `comment` VALUES (7,1, 1,2,3);
INSERT INTO `comment` VALUES (8,1, 1,2,4);
INSERT INTO `comment` VALUES (9,2, 1,2,5);========================
加入对文章评论的计算,很复杂了,这样有没有可能用一条sql实现?谢谢。评论表多了个iCType为评论类型,值为1是对视频的评论,2为对文章的评论。用於排序的計算, 对文章的评论是指评论视频123的文章,其它视频如231,295不在计算之内。
user1:(1篇文章*2+文章點擊數20)+(發了1條视频評論*2+評論被支持8)+(对文章的评论数2条)=22+10+2=34
user2:(2篇文章*2+2篇文章點擊數37)+(發了0條视频評論*2+該評論被支持0) +(对文章的评论数0条)=41+0+1=41
user3:(0篇文章*2+該文章點擊數0)+(發了2條视频評論*2+2條評論被支持6) +(对文章的评论数1条)=0+6+1=7
user5:(0篇文章*2+該文章點擊數0)+(發了0條视频評論*2+2條評論被支持0) +(对文章的评论数1条)=0+0+1=1user4和user6没参与视频123的评论,也没有参与为视频123写的文章的评论,所以不计算。查詢结果:
user2
user1
user3
user5
我最讨厌的十类问题
user1:(1篇文章*2+文章點擊數20)+(發了1條视频評論*2+評論被支持8)+(对文章的评论数2条)=22+10+2=34
user2:(2篇文章*2+2篇文章點擊數37)+(發了0條视频評論*2+該評論被支持0) +(对文章的评论数0条)=41+0+1=41
user3:(0篇文章*2+該文章點擊數0)+(發了2條视频評論*2+2條評論被支持6) +(对文章的评论数1条)=0+6+1=7
user5:(0篇文章*2+該文章點擊數0)+(發了0條视频評論*2+2條評論被支持0) +(对文章的评论数1条)=0+0+1=1
mysql> select cUserName
-> from user u
-> where exists (select 1 from article where iAUId=u.iUKey)
-> or exists (select 1 from comment where iCUId=u.iUKey)
-> order by IFNULL((select count(*)*2+sum(iCView) from article where iAUId=u.iUKey and iAVedioId=123),0)+
-> IFNULL((select count(*)*2+sum(iCNum) from comment where iCUId=u.iUKey and iCType=1 and iCVedioId=123),0) +
-> IFNULL((select count(*) from comment inner join article on comment.iCVedioId=article.iAKey where iCUId=u.iUKey and iCType=2 and iAVedioId=123),0) desc
;
+-----------+
| cUserName |
+-----------+
| user2 |
| user1 |
| user3 |
| user5 |
+-----------+
4 rows in set (0.00 sec)mysql>
因为别的用户评论了不相关的文章,如评论了下面两条不相关的文章:
INSERT INTO `article` VALUES (4,1, 20,231);
INSERT INTO `article` VALUES (5,1, 20,295);
===============
加几条user4和user5不相关的评论:CREATE TABLE `comment` (
`iCKey` int(8) NOT NULL auto_increment,
`iCUId` int(10) NOT NULL default '0',
`iCNum` int(10) NOT NULL default '0',
`iCType` tinyint(1) NOT NULL default 0,
`iCVedioId` int(10) NOT NULL default '0',
PRIMARY KEY (`iCKey`)
) ENGINE=MyISAM;
INSERT INTO `comment` VALUES (1,3, 5,1,123);
INSERT INTO `comment` VALUES (2,1, 8,1,123);
INSERT INTO `comment` VALUES (3,3, 1,1,123);
INSERT INTO `comment` VALUES (4,5, 1,2,2);
INSERT INTO `comment` VALUES (5,1, 1,2,3);
INSERT INTO `comment` VALUES (6,3, 1,2,2);
INSERT INTO `comment` VALUES (7,1, 1,2,3);
INSERT INTO `comment` VALUES (8,1, 1,2,4);
INSERT INTO `comment` VALUES (9,2, 1,2,5);
INSERT INTO `comment` VALUES (10,4, 1,2,5);
INSERT INTO `comment` VALUES (11,6, 1,2,4);
INSERT INTO `comment` VALUES (12,6, 1,2,4);
INSERT INTO `comment` VALUES (13,4, 1,2,5);
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖