今天,因为之前写了一段代码,显示文章列表(表1),但是也要显示他所属的类别的名称(表2),和浏览次数(表3求和),之前是采用,先查询出所有文章,再循环文章数组,根据文章数组的每一条查询一次查出文章所属上一级的名称,今天说是太慢了,再来改一下
先是用软件建立视图
视图代码
SELECT
sn_nl.nl_id,
sn_nl.nl_name,
Sum(sn_count.count_count)
FROM
sn_nl ,
sn_count
WHERE
sn_count.count_ss_lb = 1 AND
sn_count.count_ss_id = sn_nl.nl_id
GROUP BY
sn_nl.nl_id,
sn_nl.nl_name
这样的查询出来的结果回有缺失(因为点击次数是按天计算,有的文章并没有人浏览,所以浏览次数那张表没有对应的浏览次数字段,对应的文章的内容也不会查询出来)
所以
SELECT
a.`at_id`,
a.`at_title`,
SUM(b.`count_count`) AS `count`,
c.`nl_key`
FROM
`sn_at` AS `a`
LEFT JOIN
`sn_count` as `b`
ON
(a.`at_id`=b.`count_ss_id` AND b.`count_ss_lb`=2)
LEFT JOIN
`sn_nl` AS `c`
ON
(a.`at_nl_id`=c.`nl_id`)
GROUP BY a.`at_id`
这样就ok了