SELECT
CONCAT(us.user_id,' ', us.name),
UPPER(sc.so_number) Order_no ,
sh.upc UPC,re.label Error,
(SELECT count(*) FROM order_checker_scan scan WHERE scan.so_number =sh.so_number and scan.upc=sh.upc and scan.user_id!=0
and DATE_FORMAT(scan_time,'%Y-%m-%d') >= '2015-11-01' ) AS
prev_data,
(select CONCAT(u.user_id,' ', u.name) from users u,picklist_history p where u.user_id=p.user_id and
p.so_number=sh.so_number limit 1) as picker,
sh.item_key Times,
DATE_FORMAT(sc.date_started,'%b%d %Y %h:%i%p') datetime,sh.qty_required QTY
FROM
order_checker_short sh,
order_checker_header
sc,order_checker_short_reason re,
users us
WHERE sh.so_number=sc.so_number AND
sh.reason_id=re.reason_id AND
sc.created_by=us.user_id And
sc.created_by!=0 AND
DATE_FORMAT(date_started,'%Y-%m-%d') between '2015-11-16' and '2015-11-17' AND
sh.reason_id !=0 AND
sh.upc !=1
GROUP BY sc.so_number,sh.upc
ORDER BY sc.date_started DESC, sc.so_number DESC , sh.upc ASC
请测试以下内容：
// 1st subselect
$prevDataSelect = $db->select()
->from(array('scan' => 'order_checker_scan'), array('count(*)'))
->where('scan.so_number = sh.so_number')
->where('scan.upc = sh.upc')
->where('scan.user_id != 0')
->where("DATE_FORMAT(scan_time,'%Y-%m-%d') >= '2015-11-01'");
// 2nd subselect
$pickerSelect = $db->select()
->from(array('u' => 'users', 'p' => 'picklist_history'), array("CONCAT(u.user_id,' ', u.name)"))
->where('u.user_id = p.user_id')
->where('p.so_number = sh.so_number')
->limit(1);
// Main selection
$mainSelect = $db->select()
->from(
// tables
array(
'sh' => 'order_checker_short',
'sc' => 'order_checker_header',
're' => 'order_checker_short_reason',
'us' => 'users',
),
// columns
array(
'SomeName' => "CONCAT(us.user_id, ' ', us.name)",
'Order_no' => 'UPPER(sc.so_number)',
'UPC' => 'sh.upc',
'Error' => 're.label',
'prev_data' => new Zend_Db_Expr('(' . $prevDataSelect . ')'),
'picker' => new Zend_Db_Expr('(' . $pickerSelect . ')'),
'Times' => 'sh.item_key',
'datetime' => "DATE_FORMAT(sc.date_started,'%b%d %Y %h:%i%p')",
'QTY' => 'sh.qty_required',
)
)
// AND WHERE clauses
->where('sh.so_number = sc.so_number')
->where('sh.reason_id = re.reason_id')
->where('sc.created_by = us.user_id')
->where('sc.created_by != 0')
->where("DATE_FORMAT(date_started, '%Y-%m-%d') between '2015-11-16' and '2015-11-17'")
->where('sh.reason_id != 0')
->where('sh.upc != 1')
// GROUP BY clause
->group(array('sc.so_number', 'sh.upc'))
->order(array('sc.date_started DESC', 'sc.so_number DESC', 'sh.upc ASC'));
如果不起作用，请告诉我$mainSelect->assemble() 的输出是什么