开源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 Orders
MAX 函数返回一列中的最大值。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生成的临时表会包含左表里的所有记录。