有两个SQL表,分别是如下的:
CREATE TABLE status_db
(
sid int unsigned primary key AUTO_INCREMENT,
owner int unsigned not null,
photo_URL varchar(100) default '0',
audio_URL varchar(100) default '0',
video_URL varchar(100) default '0',
create_time varchar(20) not null ,
extra_message varchar(255),
display_time tinyint unsigned default 168,
latitude double not null,
longitude double not null,
geohash varchar(12) not null,
foreign key(owner) references user_static_db(uid) on delete cascade on update cascade
);
create table status_comment_message_db(
mid int unsigned not null AUTO_INCREMENT,
sid int unsigned not null,
uid int unsigned not null,
create_time varchar(50),
comment_content varchar(255),
refrence_mid int unsigned,
primary key (mid),
foreign key(sid) references status_db(sid) on delete cascade on update cascade
);
create table user_volatile_db(
uid int unsigned primary key,
school varchar(30),
user_name varchar(100),
signature varchar(255),
head_URL varchar(100),
video_URL varchar(100),
photo_URL varchar(100),
job varchar(255),
hobby varchar(255),
present varchar(1000),
foreign key(uid) references user_static_db(uid) on delete cascade on update cascade
);
在查询status_db的内容的时候,需要将用户的头像,名字以及评论数(评论表里面没有sid,那么就代表这个评论为0)显示出来,也就是这个时候需要查询3个表.我一开始是这样做的:
下面是我最先写的sql语句:
SELECT COUNT( mid ) AS comment_num, user_volatile_db.user_name, user_volatile_db.head_URL, status_db . *
FROM user_volatile_db, status_db, status_comment_message_db
WHERE user_volatile_db.uid
IN (
SELECT owner
FROM status_db
)
GROUP BY sid
ORDER BY sid DESC
LIMIT 0 , 10;
理想的情况应该是当comment_num等于0的时候也应该显示,但是这样做的结果却是所有状态评论数都是一样的.我以为是什么错了,于是改了一下sql语句,简化了一下看看:
SELECT COUNT( mid ) AS comment_num, status_db . *
FROM status_db, status_comment_message_db
GROUP BY status_db.sid
ORDER BY sid DESC
LIMIT 0 , 10;
结果发现结果还是所有状态的评论数都是一样的!突然发现自己犯了一个错误,没有加条件:
SELECT COUNT( mid ) AS comment_num, status_db . *
FROM status_db, status_comment_message_db where status_comment_message_db.sid=status_db.sid
GROUP BY status_db.sid
ORDER BY sid DESC
LIMIT 0 , 10;
这下好了,但是评论数为0的还是显示不了.这是为什么呢?看了一下数据库系统概论,笛卡尔积如果where不满足,整个所生成的元组就不会显示.如果要显示的话,就必须使用join连接:
select status_db.*,count(mid) as comment_num from status_comment_message_db right outer join status_db on status_comment_message_db.sid=status_db.sid group by status_comment_message_db.sid;
或者使用左连接:
select status_db.*,count(mid) as comment_num from status_db left outer join status_comment_message_db on status_comment_message_db.sid=status_db.sid group by status_comment_message_db.sid;
至于连接是否比where子句高效,众说纷纭,我也不知道.但是有一个优点可以肯定,那就是使用join比where更好些逻辑.下面是完整的代码:
SELECT status_db.*,user_volatile_db.user_name, user_volatile_db.head_URL,
count(mid) AS comment_num
FROM status_db
LEFT JOIN status_comment_message_db ON status_db.sid=status_comment_message_db.sid
LEFT JOIN user_volatile_db ON status_db.owner= user_volatile_db.uid
GROUP BY status_db.sid limit 0,10;
而且我测试了一下,这个逻辑都是错误的,count对于所有的sid每次的结果都是一样的.这个与sql语句执行顺序有关:
SQL SELECT语句完整的执行顺序:
1、FROM子句组装来自不同数据源的数据;
2、WHERE子句基于指定的条件对记录进行筛选;
3、GROUP BY子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用HAVING子句筛选分组;
6、计算所有表达式;
7、使用ORDER BY对结果进行排序
问题终于解决.