开源ECSHOP中SQL SELECT操作集锦，练习SQL

列出近期看代码过程中的一些SQL 查询操作，以此来学习！

$sql = 'SELECT g.goods_id, g.goods_name, g.shop_price AS org_price, ' . "IFNULL(mp.user_price, g.shop_price * '$_SESSION[discount]') AS shop_price, " . "RAND() AS rnd " . 'FROM ' . $this->pre . 'goods AS g ' . "LEFT JOIN " . $this->pre . "member_price AS mp " . "ON mp.goods_id = g.goods_id AND mp.user_rank = '$_SESSION[user_rank]' ";$sql .= ' WHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND ' . $type;$sql .= ' ORDER BY g.sort_order, g.last_update DESC limit ' . $start . ', ' . $limit;$sql = 'SELECT g.goods_id, g.goods_name, g.goods_name_style, g.market_price, g.shop_price AS org_price, g.promote_price, ' ."g.is_best, g.is_new, g.is_hot, g.is_promote, RAND() AS rnd " .'FROM ' . $this->pre . 'goods AS g ' .'LEFT JOIN ' . $this->pre . 'brand AS b ON b.brand_id = g.brand_id ' ."LEFT JOIN " . $this->pre . "member_price AS mp " ."ON mp.goods_id = g.goods_id AND mp.user_rank = '$_SESSION[user_rank]' " .'WHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 ' ." AND g.is_promote = 1 AND promote_start_date <= '$time' AND promote_end_date >= '$time' ";$sql = "SELECT c.cat_id, c.cat_name, cr.recommend_type FROM " . $this->pre . "cat_recommend AS cr INNER JOIN " . $this->pre . "category AS c ON cr.cat_id=c.cat_id";$sql = "SELECT b.type_id, b.type_money, SUM(o.goods_number) AS number " ."FROM " . $this->pre . "order_goods AS o, " .$this->pre . "goods AS g, " .$this->pre . "bonus_type AS b " ." WHERE o.order_id = '$order_id' " ." AND o.is_gift = 0 " ." AND o.goods_id = g.goods_id " ." AND g.bonus_type_id = b.type_id " ." AND b.send_type = '" . SEND_BY_GOODS . "' " ." AND b.send_start_date <= '$today' " ." AND b.send_end_date >= '$today' " ." GROUP BY b.type_id ";SQL函数：函数的返回值一般用AS来获得访问名AVG() 函数返回数值列的平均值。NULL 值不包括在计算中。COUNT() 函数返回匹配指定条件的行数。SUM 函数返回数值列的总数（总额）。 eg:SELECT SUM(OrderPrice) AS OrderTotal FROM OrdersMAX 函数返回一列中的最大值。NULL 值不包括在计算中。 IFNULL(column_name, value) 如果column_name为NULL，则用value取代，非NULL，则用真实值

掌握了left join、right join、inner join的联合查表方式后，基本就能满足需求了。

inner方式与from table1, table2的效果是等同的，均只取交集

使用left join 或者right join 时on条件只用于生成临时表，where条件是最终过滤临时表时采用的。left join生成的临时表会包含左表里的所有记录。