一个根据用户选择的配送至城市筛选是否有货的SQL

2016-07-07 14:55 来源：www.chinab4c.com 作者：ecshop专家




  1. SELECT g.*
  2. FROM `ecs_agency_goods` AS ag,`ecs_goods` as g
  3. WHERE ag.`agency_id` = (

  5. SELECT r.`agency_id`
  6. FROM `ecs_region` AS r, `ecs_region` AS rp
  7. WHERE rp.`parent_id` = r.`region_id`
  8. AND rp.`region_id` =168
  9. GROUP BY rp.`agency_id`
  10. LIMIT 1
  11. )
  12. and ag.goods_id=g.goods_id
  13. LIMIT 0 , 30
复制代码


要想能查出结果必须办事处绑定管理区域，并且有一个办事处商品的表
  1. CREATE TABLE IF NOT EXISTS `ecs_agency_goods` (
  2. `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  3. `agency_id` smallint(5) NOT NULL,
  4. `goods_id` int(10) NOT NULL,
  5. `goods_number` int(10) NOT NULL,
  6. `warn_number` tinyint(3) NOT NULL,
  7. `last_update` int(10) NOT NULL,
  8. PRIMARY KEY (`id`)
  9. ) ENGINE=MyISAMDEFAULT CHARSET=utf8 AUTO_INCREMENT=39 ;
复制代码

其中 rp.`region_id` =168 是查询一个热门城市的id,这个id GET或者POST过来判断，