CREATE TABLE IF NOT EXISTS `brands` (
`brand_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`brand_name` varchar(225) NOT NULL,
PRIMARY KEY (`brand_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;INSERT INTO `brands` VALUES
(1, 'brand1'),
(2, 'brand2');CREATE TABLE IF NOT EXISTS `goods` (
`goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`goods_brand_id` int(10) unsigned NOT NULL,
`goods_name` varchar(225) NOT NULL,
`goods_number` int(11) NOT NULL,
PRIMARY KEY (`goods_id`),
KEY `goods_brand_id` (`goods_brand_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;INSERT INTO `goods` VALUES
(1, 1, 'goodsaa', 3),
(2, 1, 'goodsab', -1),
(3, 2, 'goodsba', 2),
(4, 2, 'goodsbb', 3);
在上面的两个表中求品牌记录,条件:品牌中的商品不能有负数,怎么求,谢谢。
`brand_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`brand_name` varchar(225) NOT NULL,
PRIMARY KEY (`brand_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;INSERT INTO `brands` VALUES
(1, 'brand1'),
(2, 'brand2');CREATE TABLE IF NOT EXISTS `goods` (
`goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`goods_brand_id` int(10) unsigned NOT NULL,
`goods_name` varchar(225) NOT NULL,
`goods_number` int(11) NOT NULL,
PRIMARY KEY (`goods_id`),
KEY `goods_brand_id` (`goods_brand_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;INSERT INTO `goods` VALUES
(1, 1, 'goodsaa', 3),
(2, 1, 'goodsab', -1),
(3, 2, 'goodsba', 2),
(4, 2, 'goodsbb', 3);
在上面的两个表中求品牌记录,条件:品牌中的商品不能有负数,怎么求,谢谢。
`goods` a1 ON b.`brand_id`=a1.`goods_brand_id`
WHERE NOT EXISTS(SELECT 1 FROM `goods` WHERE a1.`goods_brand_id`=`goods_brand_id` AND
`goods_number`<0
)
`goods` a1 ON b.`brand_id`=a1.`goods_brand_id`
WHERE a1.goods_number>=0;