-- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2011 年 04 月 13 日 06:39
-- 服务器版本: 5.0.51
-- PHP 版本: 5.2.5SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `zhongliang`
---- ----------------------------------------------------------
-- 表的结构 `zl_agency`
--CREATE TABLE IF NOT EXISTS `zl_agency` (
`agency_id` smallint(5) unsigned NOT NULL auto_increment,
`agency_name` varchar(255) NOT NULL,
`agency_desc` text NOT NULL,
`telephone` varchar(11) NOT NULL,
PRIMARY KEY (`agency_id`),
KEY `agency_name` (`agency_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;--
-- 导出表中的数据 `zl_agency`
--INSERT INTO `zl_agency` (`agency_id`, `agency_name`, `agency_desc`, `telephone`) VALUES
(1, '北京办事处', '北京市朝阳区望京南湖东园122楼', '12345678911'),
(2, '上海办事处', '上海市朝阳区望京南湖东园123楼', '23456789011'),
(3, '重庆办事处', '上海市朝阳区望京南湖东园124楼', '15007365581'),
(4, '海南办事处', '上海市朝阳区望京南湖东园125楼', '13466705321'),
(5, '江西办事处', '上海市朝阳区望京南湖东园126楼', '15007365827');
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2011 年 04 月 13 日 06:39
-- 服务器版本: 5.0.51
-- PHP 版本: 5.2.5SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `zhongliang`
---- ----------------------------------------------------------
-- 表的结构 `zl_agency`
--CREATE TABLE IF NOT EXISTS `zl_agency` (
`agency_id` smallint(5) unsigned NOT NULL auto_increment,
`agency_name` varchar(255) NOT NULL,
`agency_desc` text NOT NULL,
`telephone` varchar(11) NOT NULL,
PRIMARY KEY (`agency_id`),
KEY `agency_name` (`agency_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;--
-- 导出表中的数据 `zl_agency`
--INSERT INTO `zl_agency` (`agency_id`, `agency_name`, `agency_desc`, `telephone`) VALUES
(1, '北京办事处', '北京市朝阳区望京南湖东园122楼', '12345678911'),
(2, '上海办事处', '上海市朝阳区望京南湖东园123楼', '23456789011'),
(3, '重庆办事处', '上海市朝阳区望京南湖东园124楼', '15007365581'),
(4, '海南办事处', '上海市朝阳区望京南湖东园125楼', '13466705321'),
(5, '江西办事处', '上海市朝阳区望京南湖东园126楼', '15007365827');
`order_id` mediumint(8) unsigned NOT NULL auto_increment,
`order_sn` varchar(20) NOT NULL default '',
`user_id` mediumint(8) unsigned NOT NULL default '0',
`order_status` tinyint(1) unsigned NOT NULL default '0',
`shipping_status` tinyint(1) unsigned NOT NULL default '0',
`pay_status` tinyint(1) unsigned NOT NULL default '0',
`consignee` varchar(60) NOT NULL default '',
`country` smallint(5) unsigned NOT NULL default '0',
`province` smallint(5) unsigned NOT NULL default '0',
`city` smallint(5) unsigned NOT NULL default '0',
`district` smallint(5) unsigned NOT NULL default '0',
`address` varchar(255) NOT NULL default '',
`zipcode` varchar(60) NOT NULL default '',
`tel` varchar(60) NOT NULL default '',
`mobile` varchar(60) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
`best_time` varchar(120) NOT NULL default '',
`sign_building` varchar(120) NOT NULL default '',
`postscript` varchar(255) NOT NULL default '',
`shipping_id` tinyint(3) NOT NULL default '0',
`shipping_name` varchar(120) NOT NULL default '',
`pay_id` tinyint(3) NOT NULL default '0',
`pay_name` varchar(120) NOT NULL default '',
`how_oos` varchar(120) NOT NULL default '',
`how_surplus` varchar(120) NOT NULL default '',
`pack_name` varchar(120) NOT NULL default '',
`card_name` varchar(120) NOT NULL default '',
`card_message` varchar(255) NOT NULL default '',
`inv_payee` varchar(120) NOT NULL default '',
`inv_content` varchar(120) NOT NULL default '',
`goods_amount` decimal(10,2) NOT NULL default '0.00',
`shipping_fee` decimal(10,2) NOT NULL default '0.00',
`insure_fee` decimal(10,2) NOT NULL default '0.00',
`pay_fee` decimal(10,2) NOT NULL default '0.00',
`pack_fee` decimal(10,2) NOT NULL default '0.00',
`card_fee` decimal(10,2) NOT NULL default '0.00',
`money_paid` decimal(10,2) NOT NULL default '0.00',
`surplus` decimal(10,2) NOT NULL default '0.00',
`integral` int(10) unsigned NOT NULL default '0',
`integral_money` decimal(10,2) NOT NULL default '0.00',
`bonus` decimal(10,2) NOT NULL default '0.00',
`order_amount` decimal(10,2) NOT NULL default '0.00',
`from_ad` smallint(5) NOT NULL default '0',
`referer` varchar(255) NOT NULL default '',
`add_time` int(10) unsigned NOT NULL default '0',
`confirm_time` int(10) unsigned NOT NULL default '0',
`pay_time` int(10) unsigned NOT NULL default '0',
`shipping_time` int(10) unsigned NOT NULL default '0',
`pack_id` tinyint(3) unsigned NOT NULL default '0',
`card_id` tinyint(3) unsigned NOT NULL default '0',
`bonus_id` mediumint(8) unsigned NOT NULL default '0',
`invoice_no` varchar(255) NOT NULL default '',
`extension_code` varchar(30) NOT NULL default '',
`extension_id` mediumint(8) unsigned NOT NULL default '0',
`to_buyer` varchar(255) NOT NULL default '',
`pay_note` varchar(255) NOT NULL default '',
`agency_id` smallint(5) unsigned NOT NULL,
`inv_type` varchar(60) NOT NULL,
`tax` decimal(10,2) NOT NULL,
`is_separate` tinyint(1) NOT NULL default '0',
`parent_id` mediumint(8) unsigned NOT NULL default '0',
`discount` decimal(10,2) NOT NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `order_sn` (`order_sn`),
KEY `user_id` (`user_id`),
KEY `order_status` (`order_status`),
KEY `shipping_status` (`shipping_status`),
KEY `pay_status` (`pay_status`),
KEY `shipping_id` (`shipping_id`),
KEY `pay_id` (`pay_id`),
KEY `extension_code` (`extension_code`,`extension_id`),
KEY `agency_id` (`agency_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;INSERT INTO `zl_order_info` (`order_id`, `order_sn`, `user_id`, `order_status`, `shipping_status`, `pay_status`, `consignee`, `country`, `province`, `city`, `district`, `address`, `zipcode`, `tel`, `mobile`, `email`, `best_time`, `sign_building`, `postscript`, `shipping_id`, `shipping_name`, `pay_id`, `pay_name`, `how_oos`, `how_surplus`, `pack_name`, `card_name`, `card_message`, `inv_payee`, `inv_content`, `goods_amount`, `shipping_fee`, `insure_fee`, `pay_fee`, `pack_fee`, `card_fee`, `money_paid`, `surplus`, `integral`, `integral_money`, `bonus`, `order_amount`, `from_ad`, `referer`, `add_time`, `confirm_time`, `pay_time`, `shipping_time`, `pack_id`, `card_id`, `bonus_id`, `invoice_no`, `extension_code`, `extension_id`, `to_buyer`, `pay_note`, `agency_id`, `inv_type`, `tax`, `is_separate`, `parent_id`, `discount`) VALUES
(1, '2009051298180', 1, 1, 2, 2, '刘先生', 1, 2, 52, 500, '[中国 北京 北京 海淀区] 中关村海兴大厦', '100085', '010-25851234', '13986765412', '[email protected]', '中午', '法院', '', 5, '申通快递', 1, '余额支付', '等待所有商品备齐后再发', '', '', '', '', '', '', 385.00, 15.00, 0.00, 0.00, 0.00, 0.00, 0.00, 400.00, 0, 0.00, 0.00, 0.00, 0, '本站', 1242142274, 1242142274, 1242142274, 1242142432, 0, 0, 0, '122', '', 0, '', '', 1, '', 0.00, 0, 0, 0.00),
(2, '2009051255518', 1, 4, 0, 0, '刘先生', 1, 2, 52, 500, '[中国 北京 北京 海淀区] 中关村海兴大厦', '100085', '010-25851234', '13986765412', '[email protected]', '中午', '法院', '', 3, '城际快递', 1, '余额支付', '等待所有商品备齐后再发', '', '精品包装', '祝福贺卡', '晚来的祝福', '', '', 960.00, 10.00, 0.00, 0.00, 0.00, 5.00, 0.00, 0.00, 0, 0.00, 0.00, 0.00, 0, '本站', 1242142324, 1242142324, 1242142324, 1242142389, 1, 1, 0, '111', '', 0, '', '', 2, '', 0.00, 0, 0, 0.00),
(3, '2009051267570', 1, 4, 0, 0, '刘先生', 1, 2, 52, 500, '[中国 北京 北京 海淀区] 中关村海兴大厦', '100085', '010-25851234', '13986765412', '[email protected]', '中午', '法院', '', 3, '城际快递', 1, '余额支付', '等待所有商品备齐后再发', '', '', '', '', '', '', 2300.00, 10.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0.00, 0.00, 0, '本站', 1242142549, 1242142549, 1242142549, 1242142589, 0, 0, 0, '', '', 0, '', '', 0, '', 0.00, 0, 0, 0.00),
(4, '2009051230249', 1, 1, 0, 2, '刘先生', 1, 2, 52, 500, '[中国 北京 北京 海淀区] 中关村海兴大厦', '100085', '010-25851234', '13986765412', '[email protected]', '中午', '法院', '', 3, '城际快递', 1, '余额支付', '等待所有商品备齐后再发', '', '', '', '', '', '', 5999.00, 10.00, 0.00, 0.00, 0.00, 0.00, 0.00, 5989.00, 0, 0.00, 20.00, 0.00, 0, '本站', 1242142681, 1242142681, 1242142681, 0, 0, 0, 1, '', '', 0, '', '', 0, '', 0.00, 0, 0, 0.00),
(5, '2009051276258', 1, 1, 3, 2, '刘先生', 1, 2, 52, 500, '[中国 北京 北京 海淀区] 中关村海兴大厦', '100085', '010-25851234', '13986765412', '[email protected]', '中午', '法院', '', 3, '城际快递', 1, '余额支付', '等待所有商品备齐后再发', '', '', '', '', '', '', 8600.00, 10.00, 0.00, 0.00, 0.00, 0.00, 0.00, 8610.00, 0, 0.00, 0.00, 0.00, 0, '本站', 1242142808, 1242142808, 1242142808, 0, 0, 0, 0, '', '', 0, '', '', 0, '', 0.00, 0, 0, 0.00),
(6, '2009051217221', 3, 3, 0, 0, '叶先生', 1, 2, 52, 510, '通州区旗舰凯旋小区', '', '13588104710', '', '[email protected]', '', '', '', 5, '申通快递', 2, '银行汇款/转帐', '等待所有商品备齐后再发', '', '', '', '', '', '', 20.00, 15.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0.00, 35.00, 0, '', 1242143292, 0, 0, 0, 0, 0, 0, '', '', 0, '', '', 1, '', 0.00, 0, 0, 0.00),
(7, '2009051227085', 3, 1, 0, 0, '叶先生', 1, 2, 52, 510, '通州区旗舰凯旋小区', '', '13588104710', '', '[email protected]', '', '', '', 5, '申通快递', 2, '银行汇款/转帐', '等待所有商品备齐后再发', '', '', '', '', '', '', 2298.00, 15.00, 0.00, 0.00, 0.00, 0.00, 0.00, 1000.00, 0, 0.00, 0.00, 1198.10, 0, '', 1242143383, 1242143454, 0, 0, 0, 0, 0, '', '', 0, '', '', 0, '', 0.00, 0, 0, 114.90),
(8, '2009051299732', 3, 0, 0, 0, '叶先生', 1, 2, 52, 510, '通州区旗舰凯旋小区', '', '13588104710', '', '[email protected]', '', '', '', 5, '申通快递', 2, '银行汇款/转帐', '等待所有商品备齐后再发', '', '', '', '', '', '', 623.00, 15.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0.00, 638.00, 0, '', 1242143444, 0, 0, 0, 0, 0, 0, '', '', 0, '', '', 0, '', 0.00, 0, 0, 0.00),
(9, '2009051210718', 3, 2, 0, 0, '叶先生', 1, 2, 52, 510, '通州区旗舰凯旋小区', '', '13588104710', '', '[email protected]', '', '', '', 5, '申通快递', 2, '银行汇款/转帐', '等待所有商品备齐后再发', '', '', '', '', '', '', 2000.00, 15.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 0.00, 0.00, 0.00, 0, '', 1242143732, 0, 0, 0, 0, 0, 0, '', '', 0, '1', '', 1, '', 0.00, 0, 0, 0.00),
(10, '2009051268194', 1, 1, 0, 2, '刘先生', 1, 2, 52, 500, '海兴大厦', '', '010-25851234', '13986765412', '[email protected]', '', '', '', 3, '城际快递', 1, '余额支付', '等待所有商品备齐后再发', '', '', '', '', '', '', 0.00, 10.00, 0.00, 0.00, 0.00, 0.00, 0.00, 10.00, 17000, 0.00, 0.00, 0.00, 0, '', 1242143920, 1242143920, 1242143920, 0, 0, 0, 0, '', 'exchange_goods', 24, '', '', 0, '', 0.00, 0, 0, 0.00),
;
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE zl_order_info.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time#1054 - Unknown column 'zl_order_info.agency_id' in 'where clause' 这要怎么写?
?
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE A.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE b.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time
表已经用了别名,在WHERE中可以使用别名
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE a.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE A.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time最好字段名前都加上别名,你的SQL语句不是标准的SQL语句
关于汇总的 count 在这里查询后的汇总不准确。比如,我只按条件查出一条,汇总金额是100元如果按条件查出两二条了,汇总金额是200元
(SELECT SUM( surplus )FROM `zl_order_info`) AS count?
SUM(DISTINCT surplus) 试试
FROM zl_order_info a LEFT JOIN zl_agency b ON ( a.agency_id = b.agency_id )
WHERE a.agency_id =1
AND pay_status =2
AND 0905 = FROM_UNIXTIME( `pay_time` , '%y%m' )
GROUP BY order_sn
ORDER BY pay_time
FROM " . $ecs->table(order_info)." a LEFT JOIN " . $ecs->table(agency)." b ON ( a.agency_id = b.agency_id )
WHERE b.agency_id =$zitidian_id
AND pay_status = 2
AND $time = FROM_UNIXTIME( `pay_time` , '%y%m')
GROUP BY order_sn
ORDER BY pay_time