感谢ACMAIN_CHM的提醒,现将信息补充完整。 CREATE TABLE `customer` ( `cname` text, `regTime` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into customer values('张三','2010-01-01'); insert into customer values('李四','2010-02-01'); insert into customer values('王五','2010-03-01');CREATE TABLE `product` ( `pid` int(11) DEFAULT NULL, `pname` text, ) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into product values('1','内存'); insert into product values('2','主板'); insert into product values('3','显示器');CREATE TABLE `review` ( `pid` int(11) DEFAULT NULL, `cname` text, `summary` text, ) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into review values('1','李四','很好很强大'); insert into review values('1','李四','这次不怎么样'); insert into review values('1','王五','很不错'); insert into review values('2','张三','很好'); insert into review values('2','李四','还行吧');MySql version 5.1
由于比较急,我在发帖之前也问了同学,同学已经给出了解决。 再次感谢所有人。SELECT c.cname, p.pid, (SELECT COUNT(*) AS frequency FROM review WHERE (pid = p.pid) AND (cname = c.cname)) AS frequency FROM customer AS c , product AS p ORDER BY c.cname 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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
CREATE TABLE `customer` (
`cname` text,
`regTime` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into customer values('张三','2010-01-01');
insert into customer values('李四','2010-02-01');
insert into customer values('王五','2010-03-01');CREATE TABLE `product` (
`pid` int(11) DEFAULT NULL,
`pname` text,
) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into product values('1','内存');
insert into product values('2','主板');
insert into product values('3','显示器');CREATE TABLE `review` (
`pid` int(11) DEFAULT NULL,
`cname` text,
`summary` text,
) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into review values('1','李四','很好很强大');
insert into review values('1','李四','这次不怎么样');
insert into review values('1','王五','很不错');
insert into review values('2','张三','很好');
insert into review values('2','李四','还行吧');MySql version 5.1
再次感谢所有人。SELECT c.cname, p.pid,
(SELECT COUNT(*) AS frequency
FROM review
WHERE (pid = p.pid) AND (cname = c.cname)) AS frequency
FROM customer AS c , product AS p
ORDER BY c.cname DESC