SELECT * FROM `article_cat` b LEFT JOIN `article` a ON a.cat_id=b.cat_id WHERE NOT EXISTS(SELECT 1 FROM `article` WHERE a.cat_id=cat_id AND a.add_time<add_time)
SELECT * FROM `article_cat` b LEFT JOIN `article` a ON a.cat_id=b.cat_id WHERE NOT EXISTS(SELECT 1 FROM `article` WHERE a.cat_id=cat_id AND a.add_time<add_time AND a.parent_id=16)这样报错啊。1054 - Unknown column 'a.parent_id' in 'where clause'其中parent_id在表article_cat 和 article里面都有。而赵一和其它都有一个共同的parent_id来区分,要怎么做啊?
示例:
select * from tt a where nor exists(select 1 from tt whre a.id=id and a.time<time)
article_id 文章ID
cat_id 所属人ID
title 文章标题
content 文章内容
add_time 发表时间article_cat表
cat_id 所属人ID
cat_name 所属人
-- 表的结构 `article_cat`
--CREATE TABLE IF NOT EXISTS `article_cat` (
`cat_id` int(10) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(50) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;--
-- 转存表中的数据 `article_cat`
--INSERT INTO `article_cat` (`cat_id`, `cat_name`) VALUES
(1, '赵一'),
(2, '钱二'),
(3, '孙三'),
(4, '李四');
CREATE TABLE IF NOT EXISTS `article` (
`article_id` int(10) NOT NULL AUTO_INCREMENT,
`cat_id` int(10) NOT NULL,
`title` text,
`content` text,
`add_time` datetime DEFAULT NULL,
PRIMARY KEY (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;--
-- 转存表中的数据 `article`
--INSERT INTO `article` (`article_id`, `cat_id`, `title`, `content`, `add_time`) VALUES
(1, 1, '2343424242', '2342342432', '2011-06-02 00:00:00'),
(2, 1, '2343424242', '2342342432', '2011-06-02 00:00:00'),
(3, 2, '2343424242', '2342342432', '2011-06-03 00:00:00'),
(4, 3, '2343424242', '2342342432', '2011-06-04 00:00:00'),
(5, 1, '2343424242', '2342342432', '2011-06-05 00:00:00'),
(6, 1, '2343424242', '2342342432', '2011-06-06 00:00:00');
LEFT JOIN `article` a
ON a.cat_id=b.cat_id
WHERE NOT EXISTS(SELECT 1 FROM `article` WHERE a.cat_id=cat_id AND a.add_time<add_time)
要是只有这2个相同的时间,不就算不出来了?
(1, 1, '2343424242', '2342342432', '2011-06-02 00:00:00')
(2, 1, '2343424242', '2342342432', '2011-06-02 00:00:00')
LEFT JOIN `article` a
ON a.cat_id=b.cat_id
WHERE NOT EXISTS(SELECT 1 FROM `article` WHERE a.cat_id=cat_id AND a.add_time<add_time AND a.parent_id=16)这样报错啊。1054 - Unknown column 'a.parent_id' in 'where clause'其中parent_id在表article_cat 和 article里面都有。而赵一和其它都有一个共同的parent_id来区分,要怎么做啊?
`cat_id` int(10) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(50) NOT NULL,
`parent_id` int(10) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;--
-- 转存表中的数据 `article_cat`
--INSERT INTO `article_cat` (`cat_id`, `cat_name`, `parent_id`) VALUES
(1, '赵一', 16),
(2, '钱二', 16),
(3, '孙三', 15),
(4, '李四', 14);CREATE TABLE IF NOT EXISTS `article` (
`article_id` int(10) NOT NULL AUTO_INCREMENT,
`cat_id` int(10) NOT NULL,
`title` text,
`content` text,
`add_time` datetime DEFAULT NULL,
PRIMARY KEY (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;--
-- 转存表中的数据 `article`
--INSERT INTO `article` (`article_id`, `cat_id`, `title`, `content`, `add_time`) VALUES
(1, 1, '2343424242', '2342342432', '2011-06-02 00:00:00'),
(2, 1, '2343424242', '2342342432', '2011-06-02 00:00:00'),
(3, 2, '2343424242', '2342342432', '2011-06-03 00:00:00'),
(4, 3, '2343424242', '2342342432', '2011-06-04 00:00:00'),
(5, 1, '2343424242', '2342342432', '2011-06-05 00:00:00'),
(6, 1, '2343424242', '2342342432', '2011-06-06 00:00:00');
更新了一下,
要和上面一样,只是要显示parent_id=16的就行了。
mysql> SELECT * FROM `article_cat` b
-> LEFT JOIN `article` a
-> ON a.cat_id=b.cat_id
-> WHERE NOT EXISTS(SELECT 1 FROM `article` WHERE a.cat_id=cat_id AND a.add_
time<add_time )
-> and b.parent_id=16;
+--------+----------+-----------+------------+--------+------------+------------
+---------------------+
| cat_id | cat_name | parent_id | article_id | cat_id | title | content
| add_time |
+--------+----------+-----------+------------+--------+------------+------------
+---------------------+
| 1 | 赵一 | 16 | 6 | 1 | 2343424242 | 2342342432
| 2011-06-06 00:00:00 |
| 2 | 钱二 | 16 | 3 | 2 | 2343424242 | 2342342432
| 2011-06-03 00:00:00 |
+--------+----------+-----------+------------+--------+------------+------------
+---------------------+
2 rows in set (0.00 sec)mysql>
[征集]分组取最大N条记录方法征集,及散分....
-> select * from article order by add_time desc
-> ) t
-> group by cat_id;
+------------+--------+------------+------------+---------------------+
| article_id | cat_id | title | content | add_time |
+------------+--------+------------+------------+---------------------+
| 6 | 1 | 2343424242 | 2342342432 | 2011-06-06 00:00:00 |
| 3 | 2 | 2343424242 | 2342342432 | 2011-06-03 00:00:00 |
| 4 | 3 | 2343424242 | 2342342432 | 2011-06-04 00:00:00 |
+------------+--------+------------+------------+---------------------+
3 rows in set (0.03 sec)mysql>