我在MySQL中的视图不起作用
我玩MySQL 5中的CREATE VIEW，现在我有一个问题，我怎么能确定我何时使用这个代码。
SELECT
*
FROM
view_shop_invoicer_list
WHERE
accept_date >= '2009-10-16 00:00:00' AND
accept_date <= '2009-10-31 23:59:59' AND
shopid = [SHOPID];
DROP VIEW IF EXISTS view_shop_invoicer_list;
CREATE VIEW view_shop_invoicer_list AS
SELECT
SUM( it.transamount ) AS beloeb,
SUM( it.cargo_fee ) AS cargo,
SUM(
(
it.transamount -
(
( it.transamount - it.cargo_fee ) / 100 *
( ms.pay_adm_fee + ms.pay_marks_fee + ms.pay_kick_fee ) -
( it.adm_fee + it.marketing_fee + it.kickback_fee )
)
)
) AS shop_payout,
SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_adm_fee , it.adm_fee ) ) AS shop_adm_fee,
SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_marks_fee , it.marketing_fee ) ) AS shop_marks_fee,
SUM( fc_return_fee( it.transamount , it.cargo_fee , ms.pay_kick_fee , it.kickback_fee ) ) AS shop_kick_fee,
it.shopid AS shopid,
it.accept_date AS accept_date
FROM
invoice_trans it INNER JOIN invoice i ON it.orderid = i.ordreid
INNER JOIN shops ms ON ms.id = it.shopid
WHERE
i.status != 0
CREATE TABLE `invoice_trans` (
`id` int(11) NOT NULL auto_increment,
`orderid` varchar(32) NOT NULL default '0',
`transamount` int(11) NOT NULL default '0',
`transact` varchar(32) NOT NULL default '',
`transnr` int(11) NOT NULL default '0',
`shopid` int(11) NOT NULL default '0',
`status` int(11) NOT NULL default '0',
`accept_date` timestamp NOT NULL default '0000-00-00 00:00:00',
`md5_key_declie` varchar(32) NOT NULL,
`declie_date` datetime NOT NULL default '0000-00-00 00:00:00',
`pay_points` int(1) NOT NULL default '0',
`cargo_fee` int(11) NOT NULL default '4900',
`first_time_discount` int(2) NOT NULL default '0',
`adm_fee` int(11) NOT NULL default '0',
`marketing_fee` int(11) NOT NULL default '0',
`kickback_fee` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `invoice` (
`id` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL default '0',
`per_fullname` varchar(64) NOT NULL,
`per_street` varchar(64) NOT NULL,
`per_zipcode` int(4) NOT NULL,
`per_city` varchar(64) NOT NULL,
`per_email` varchar(256) NOT NULL default '',
`fak_fullname` varchar(64) NOT NULL default '',
`fak_street` varchar(64) NOT NULL,
`fak_zipcode` int(4) NOT NULL,
`fak_city` varchar(64) NOT NULL,
`fak_email` varchar(256) NOT NULL default '',
`push_date` datetime NOT NULL default '0000-00-00 00:00:00',
`status` int(1) NOT NULL default '0',
`splits` int(11) NOT NULL default '0',
`dibs_ordreid` varchar(32) NOT NULL default '0',
`reftag` varchar(64) NOT NULL,
`vonchers_prices` int(11) NOT NULL default '0',
`ordre_complate` int(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
没有找到相关结果
已邀请:
2 个回复
鞋扣蚊冈借
中没有（或）列。顺便说一下，脚本仍然非常不完整，没有功能或测试数据，因此可测试性非常有限。 mysql给你的错误信息是什么？辰炔诚薯
DROP VIEW IF EXISTS view_shop_invoicer_list; CREATE VIEW view_shop_invoicer_list AS SELECT it.transamount AS beloeb, SUM( it.cargo_fee ) AS cargo, ( it.transamount - ( ( it.transamount - it.cargo_fee ) / 100 * ( ms.pay_adm_fee + ms.pay_marks_fee + ms.pay_kick_fee ) - ( it.adm_fee + it.marketing_fee + it.kickback_fee ) ) ) AS shop_payout, fc_return_fee( it.transamount , it.cargo_fee , ms.pay_adm_fee , it.adm_fee ) AS shop_adm_fee, fc_return_fee( it.transamount , it.cargo_fee , ms.pay_marks_fee , it.marketing_fee ) AS shop_marks_fee, fc_return_fee( it.transamount , it.cargo_fee , ms.pay_kick_fee , it.kickback_fee ) AS shop_kick_fee, it.shopid AS shopid, it.accept_date AS accept_date FROM invoice_trans it INNER JOIN invoice i ON it.orderid = i.ordreid WHERE i.status != 0 GROUP BY it.id;SELECT SUM( beloeb ) AS beloeb, SUM( cargo ) AS cargo, SUM( shop_payout ) AS shop_payout, SUM( shop_adm_fee ) AS shop_adm_fee, SUM( shop_marks_fee ) AS shop_marks_fee, SUM( shop_kick_fee ) AS shop_kick_fee, accept_date, shopid FROM view_shop_invoicer_list WHERE accept_date >= '". $this->from_date ."' AND accept_date <= '". $this->to_date ."' GROUP BY shopid