查出兑换商品的排名

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



  2. 查出兑换商品的排名
  3. SELECT ig.goods_id,ig.exchange_integral,ig.goods_thumb,og.integral_count FROM (
  4. SELECT g.goods_id, g.goods_thumb,eg.exchange_integral
  5. FROM `s513304db0`.`ecs_exchange_goods` AS eg, `s513304db0`.`ecs_goods` AS g
  6. WHERE eg.is_exchange =1
  7. AND eg.goods_id = g.goods_id
  8. AND g.is_on_sale =1
  9. AND g.is_delete =0
  10. ) AS ig,
  11. (
  12. SELECT count( * ) AS integral_count, og.goods_id
  13. FROM `s513304db0`.`ecs_order_goods` AS og, `s513304db0`.`ecs_order_info` AS oi
  14. WHERE og.order_id = oi.order_id
  15. AND oi.integral <>0
  16. AND oi.integral_money =0
  17. GROUP BY og.goods_id
  18. ) AS og WHERE ig.goods_id = og.goods_id ORDER BY og.integral_count DESC
  19. 子查询，查出在线可以兑换的商品
  20. SELECT g.goods_id, g.goods_thumb, eg.exchange_integral
  21. FROM `s513304db0`.`ecs_exchange_goods` AS eg, `s513304db0`.`ecs_goods` AS g
  22. WHERE eg.is_exchange =1
  23. AND eg.goods_id = g.goods_id
  24. AND g.is_on_sale =1
  25. AND g.is_delete =0
  26. LIMIT 0 , 30
  27. 子查询，查出订单中属于积分兑换的商品
  28. SELECT count( * ) AS integral_count, og.goods_id
  29. FROM `s513304db0`.`ecs_order_goods` AS og, `s513304db0`.`ecs_order_info` AS oi
  30. WHERE og.order_id = oi.order_id
  31. AND oi.integral <>0
  32. AND oi.integral_money =0
  33. GROUP BY og.goods_id
  34. LIMIT 0 , 30

  36. /**
  37. * 获得兑换商品排名 liangfang add
  38. *
  39. * @accesspublic
  40. * @paramstring$children
  41. * @returnarray
  42. */
  43. function get_exchange_goods_top( $num)
  44. {
  45. $sql='SELECT ig.goods_id,ig.goods_name,ig.exchange_integral,ig.goods_thumb,og.integral_count FROM (
  46. SELECT g.goods_id, g.goods_thumb,g.goods_name,eg.exchange_integral
  47. FROM '.$GLOBALS['ecs']->table('exchange_goods').' AS eg, '.$GLOBALS['ecs']->table('goods').' AS g
  48. WHERE eg.is_exchange =1
  49. AND eg.goods_id = g.goods_id
  50. AND g.is_on_sale =1
  51. AND g.is_delete =0

  53. ) AS ig,
  54. (
  55. SELECT count( * ) AS integral_count, og.goods_id
  56. FROM '.$GLOBALS['ecs']->table('order_goods').' AS og, '.$GLOBALS['ecs']->table('order_info').' AS oi
  57. WHERE og.order_id = oi.order_id
  58. AND oi.integral <>0
  59. AND oi.integral_money =0
  60. GROUP BY og.goods_id

  62. ) AS og WHERE ig.goods_id = og.goods_id ORDER BY og.integral_count DESC';

  64. if($num > 0)
  65. {
  66. $sql.=" LIMIT $num";
  67. }
  68. $integral_goods=$GLOBALS['db']->getAll($sql);



  72. return $integral_goods;
  73. }
复制代码