# MySQL-Front Dump 2.5
#
# Host: localhost Database: cards
# --------------------------------------------------------
# Server version 4.0.13-nt# create database tmpCREATE DATABASE IF NOT EXISTS tmp;# use database tmpUSE tmp;#
# Table structure for table 'card_money'
#DROP TABLE IF EXISTS card_money;
CREATE TABLE card_money (
card_id varchar(8) NOT NULL default '',
limit_money decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_money'
#INSERT INTO card_money (card_id, limit_money) VALUES("00000001", "1000.00");
INSERT INTO card_money (card_id, limit_money) VALUES("00000002", "890.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000003", "1523.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000004", "254.80");
INSERT INTO card_money (card_id, limit_money) VALUES("00000005", "5789.20");
#
# Table structure for table 'card_spent'
#DROP TABLE IF EXISTS card_spent;
CREATE TABLE card_spent (
card_id varchar(8) NOT NULL default '',
spent_money decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_spent'
#INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "56.00");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000003", "452.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "7894.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "184.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "154.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "1548.20");
#
# Table structure for table 'card_subscription'
#DROP TABLE IF EXISTS card_subscription;
CREATE TABLE card_subscription (
card_id varchar(8) NOT NULL default '',
card_subscription decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_subscription'
#INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "58.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000004", "15.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000005", "14.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "157.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000002", "548.20");
#
# Host: localhost Database: cards
# --------------------------------------------------------
# Server version 4.0.13-nt# create database tmpCREATE DATABASE IF NOT EXISTS tmp;# use database tmpUSE tmp;#
# Table structure for table 'card_money'
#DROP TABLE IF EXISTS card_money;
CREATE TABLE card_money (
card_id varchar(8) NOT NULL default '',
limit_money decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_money'
#INSERT INTO card_money (card_id, limit_money) VALUES("00000001", "1000.00");
INSERT INTO card_money (card_id, limit_money) VALUES("00000002", "890.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000003", "1523.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000004", "254.80");
INSERT INTO card_money (card_id, limit_money) VALUES("00000005", "5789.20");
#
# Table structure for table 'card_spent'
#DROP TABLE IF EXISTS card_spent;
CREATE TABLE card_spent (
card_id varchar(8) NOT NULL default '',
spent_money decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_spent'
#INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "56.00");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000003", "452.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "7894.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "184.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "154.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "1548.20");
#
# Table structure for table 'card_subscription'
#DROP TABLE IF EXISTS card_subscription;
CREATE TABLE card_subscription (
card_id varchar(8) NOT NULL default '',
card_subscription decimal(8,2) default NULL
) TYPE=MyISAM;#
# Dumping data for table 'card_subscription'
#INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "58.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000004", "15.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000005", "14.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "157.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000002", "548.20");
除非使用 MySQL 4.1 ,
它支持子查询
LEFT JOIN
(
SELECT card_id, SUM(spent_money) FROM card_spent GROUP BY card_id
) tB ON tA.card_id = tB.card_id
LEFT JOIN
(
SELECT card_id, SUM(card_subscription) FROM card_subscription GROUP BY card_id
) tC ON tA.card_id = tC.card_id
a.card_id,a.limit_money,b.spent_money,c.card_subscription
from
(select cardid ,limit_money from card_money) as a,
(select cardid,sum(spent_money) as spent_money) as b,
(select cardid,sum(card_subscription) as card_subscription) as c,
where
a.cardid*=b.cardid and a.cardid*=c.cardid;