mysql語句集（一）

標籤：fun   erb   class   limit   bat   inner   batch   blog   code   

SELECT            UcenterTdouLog.user_id, UcenterTdouLog.subject, UcenterTdouLog.tdou,UcenterTdouLog.created,            UcenterTdouLog.out_id, ifnull(SaleOrder.order_sn, 0) AS order_sn,            (                SELECT                    SUM(tdou)                FROM                    ucenter_tdou_log AS a                WHERE                    a.created <= UcenterTdouLog.created                    AND a.user_id = UcenterTdouLog.user_id                GROUP BY                    a.user_id            ) AS tdou_total        FROM            ucenter_tdou_log AS UcenterTdouLog            LEFT JOIN sale_order AS SaleOrder ON SaleOrder.order_id = UcenterTdouLog.out_id        WHERE             1=1 %s        ORDER BY             UcenterTdouLog.user_id asc,UcenterTdouLog.created asc

　　收發存匯總

             

SELECT             GoodsSku.sku_id,             GoodsSku.barcode,             GoodsSku.sku_name,             GoodsTypeSpec1.spec_name AS sku_spec1,             GoodsTypeSpec2.spec_name AS sku_spec2,             GoodsCategory1.cat_name AS catName1,             GoodsCategory2.cat_name AS catName2,             GoodsCategory3.cat_name AS catName3,             GoodsUnit.sale_unit,             SellerBase.seller_name,             IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id %s),0) AS qichuNum,             IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price ) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id %s),0) AS qichuPrice,             IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id not in(02,05) %s),0) AS batchNum,             IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price ) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id not in(02,05) %s),0) AS batchPrice,             IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 02 %s),0) AS allocBatchNum,             IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 02 %s),0) AS allocBatchPrice,             IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 05 %s),0) AS takeBatchNum,             IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id AND WmsBatchBase.type_id = 05 %s),0) AS takeBatchPrice,             IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id not in (01,05) %s),0) AS OrderNum,             IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id not in (01,05) %s),0) AS OrderPrice,             IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 01 %s),0) AS allocOrderNum,             IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 01 %s), 0) AS allocOrderPrice,             IFNULL((SELECT SUM(WmsOrderItem.sku_num) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 05 %s),0) AS takeOrderNum,             IFNULL((SELECT SUM(WmsOrderItem.sku_num * WmsOrderItem.stock_price) FROM wms_order_base AS WmsOrderBase INNER JOIN wms_order_item AS WmsOrderItem ON WmsOrderItem.order_id = WmsOrderBase.order_id WHERE WmsOrderItem.sku_id = GoodsSku.sku_id AND WmsOrderBase.type_id = 05 %s), 0) AS takeOrderPrice        FROM             goods_sku AS GoodsSku             INNER JOIN goods_base AS GoodsBase ON GoodsSku.goods_id = GoodsBase.goods_id             LEFT JOIN goods_category AS GoodsCategory1 ON GoodsCategory1.cat_id = GoodsBase.cat_id1             LEFT JOIN goods_category AS GoodsCategory2 ON GoodsCategory2.cat_id = GoodsBase.cat_id2             LEFT JOIN goods_category AS GoodsCategory3 ON GoodsCategory3.cat_id = GoodsBase.cat_id3             LEFT JOIN goods_unit AS GoodsUnit ON GoodsUnit.unit_id = GoodsBase.unit_id             LEFT JOIN seller_base AS SellerBase ON SellerBase.seller_id = GoodsBase.seller_id             LEFT JOIN goods_type_spec AS GoodsTypeSpec1 ON GoodsTypeSpec1.spec_id = GoodsSku.sku_spec1             LEFT JOIN goods_type_spec AS GoodsTypeSpec2 ON GoodsTypeSpec2.spec_id = GoodsSku.sku_spec2        LIMIT %d,%d

　　

/**     * 收發存匯總報表     * @param $houseID     * @param $startDate     * @param $endDate     * @param $page     * @param int $size     * @return array     */    public function fetchOrderBatchAllReport($houseID, $startDate, $endDate, $page, $size = 20)    {        $database = $this->database();        $where1 = ‘‘;        $where2 = ‘‘;        $where3 = ‘‘;        $where4 = ‘‘;        $where5 = ‘‘;        $where6 = ‘‘;        $where7 = ‘‘;        $where8 = ‘‘;        $where9 = ‘‘;        $where10 = ‘‘;        $where11 = ‘‘;        $where12 = ‘‘;        $where13 = ‘‘;        $where14 = ‘‘;        $params1 = array();        $params2 = array();        $params3 = array();        $params4 = array();        $params5 = array();        $params6 = array();        $params7 = array();        $params8 = array();        $params9 = array();        $params10 = array();        $params11 = array();        $params12 = array();        $params13 = array();        $params14 = array();        if ($houseID) {            $where1 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where2 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where3 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where4 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where5 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where6 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where7 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where8 .= ‘ AND WmsBatchBase.house_id = ?‘;            $where9 .= ‘ AND WmsOrderBase.house_id = ?‘;            $where10 .= ‘ AND WmsOrderBase.house_id = ?‘;            $where11 .= ‘ AND WmsOrderBase.house_id = ?‘;            $where12 .= ‘ AND WmsOrderBase.house_id = ?‘;            $where13 .= ‘ AND WmsOrderBase.house_id = ?‘;            $where14.= ‘ AND WmsOrderBase.house_id = ?‘;            array_push($params1, $houseID);            array_push($params2, $houseID);            array_push($params3, $houseID);            array_push($params4, $houseID);            array_push($params5, $houseID);            array_push($params6, $houseID);            array_push($params7, $houseID);            array_push($params8, $houseID);            array_push($params9, $houseID);            array_push($params10, $houseID);            array_push($params11, $houseID);            array_push($params12, $houseID);            array_push($params13, $houseID);            array_push($params14, $houseID);        }        if ($startDate && $endDate) {            $started = strtotime($startDate);            $ended = strtotime($endDate);            $where1 .= ‘ AND WmsBatchBase.created  <= ?‘;            $where2 .= ‘ AND WmsBatchBase.created  <= ?‘;            $where3 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where4 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where5 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where6 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where7 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where8 .= ‘ AND WmsBatchBase.created >= ? AND WmsBatchBase.created - (24*60*60) <= ? ‘;            $where9 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            $where10 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            $where11 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            $where12 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            $where13 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            $where14 .= ‘ AND WmsOrderBase.created >= ? AND WmsOrderBase.created - (24*60*60) <= ? ‘;            array_push($params1, $started);            array_push($params2, $started);            array_push($params3, $started, $ended);            array_push($params4, $started, $ended);            array_push($params5, $started, $ended);            array_push($params6, $started, $ended);            array_push($params7, $started, $ended);            array_push($params8, $started, $ended);            array_push($params9, $started, $ended);            array_push($params10,$started, $ended);            array_push($params11,$started, $ended);            array_push($params12,$started, $ended);            array_push($params13,$started, $ended);            array_push($params14,$started, $ended);        }        $params =array_merge($params1,$params2,$params3,$params4,$params5,$params6,$params7,$params8,$params9,$params10,$params11,$params12,$params13,$params14);        return array(            $database->query(sprintf(self::ORDER_BATCH_ALL_REPORT_SQL, $where1, $where2, $where3, $where4, $where5, $where6, $where7, $where8, $where9, $where10, $where11, $where12,$where13,$where14, ($page - 1) * $size, $size), $params, DatabaseProvider::FETCH_STYLE_OBJ),            $database->query(self::ORDER_BATCH_ALL_REPORT_COUNT_SQL),        );    }

　　

mysql語句集（一）