select * from b1 inner join a on b1.product_id=a.product_id where 2>(select count(*) from b where b1.product_id=product_id and b1.time>time)
TIME是否唯一 贴建表及插入记录的SQL,及要求结果出来看看
我做了2个类似的临时表,新闻表如下 DROP TABLE IF EXISTS `news`; CREATE TABLE `news` ( `newsid` bigint(20) NOT NULL AUTO_INCREMENT, `newscontent` varchar(255) DEFAULT NULL, `time` int(11) DEFAULT NULL, `pid` bigint(20) DEFAULT NULL, PRIMARY KEY (`newsid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of news -- ---------------------------- INSERT INTO `news` VALUES ('1', 'asfaf', '36', '2'); INSERT INTO `news` VALUES ('2', 'avdvdfd', '32', '2'); INSERT INTO `news` VALUES ('3', 'asdfdsf', '63', '2'); INSERT INTO `news` VALUES ('4', 'b1', '21', '1'); INSERT INTO `news` VALUES ('5', 'b2', '20', '1'); INSERT INTO `news` VALUES ('6', 'b3', '68', '1');产品表如下 DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `pid` bigint(20) NOT NULL AUTO_INCREMENT, `pname` varchar(255) DEFAULT NULL, PRIMARY KEY (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO `product` VALUES ('1', '产品a'); INSERT INTO `product` VALUES ('2', '产品b');想要的结果1 产品a b3 6 68 1 产品a b1 4 21 2 产品b asdfdsf 3 63 2 产品b asfaf 1 36每个产品取最新的2条新闻即可,按新闻降序的
SELECT * FROM news a INNER JOIN `product` b ON a.`pid`=b.`pid` WHERE 2>(SELECT COUNT(*) FROM news WHERE a.`pid`=`pid` AND a.`time`<`time`)
select * from b1 inner join a on b1.product_id=a.product_id where 2>(select count(*) from b where b1.product_id=product_id and b1.time>time) 菜鸟求教,这个SQL语句是否能够实现lz所说的问题?
表a是 product_id,product_name
表b是 news_id,news_content,product_id
inner join a on b1.product_id=a.product_id
where 2>(select count(*) from b where b1.product_id=product_id and b1.time>time)
贴建表及插入记录的SQL,及要求结果出来看看
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
`newsid` bigint(20) NOT NULL AUTO_INCREMENT,
`newscontent` varchar(255) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
`pid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`newsid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of news
-- ----------------------------
INSERT INTO `news` VALUES ('1', 'asfaf', '36', '2');
INSERT INTO `news` VALUES ('2', 'avdvdfd', '32', '2');
INSERT INTO `news` VALUES ('3', 'asdfdsf', '63', '2');
INSERT INTO `news` VALUES ('4', 'b1', '21', '1');
INSERT INTO `news` VALUES ('5', 'b2', '20', '1');
INSERT INTO `news` VALUES ('6', 'b3', '68', '1');产品表如下
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`pname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '产品a');
INSERT INTO `product` VALUES ('2', '产品b');想要的结果1 产品a b3 6 68
1 产品a b1 4 21
2 产品b asdfdsf 3 63
2 产品b asfaf 1 36每个产品取最新的2条新闻即可,按新闻降序的
inner join a on b1.product_id=a.product_id
where 2>(select count(*) from b where b1.product_id=product_id and b1.time>time) 菜鸟求教,这个SQL语句是否能够实现lz所说的问题?