1. Create report_table:
CREATE TABLE report_table
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
report_period date NOT NULL,
reporter_id int(11) unsigned NOT NULL,
dir varchar(20) NOT NULL,
report_content mediumblob NOT NULL,
PRIMARY KEY ( id ),
UNIQUE KEY ( reporter_id, report_period )
KEY ( reporter_id ),
KEY ( report_period )
);
2. Create report_summary
CREATE TABLE report_summary
(
id int(11) unsigned NOT NULL,
buy_amount double DEFAULT NULL,
sell_amount double DEFAULT NULL,
PRIMARY KEY ( id ),
CONSTRAINT FOREIGN KEY ( id ) REFERENCES report_table ( id )
ON DELETE CASCADE ON UPDATE CASCADE
);
3. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_summary.id = report_table.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';
4. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_table.id = report_summary.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';
1. Create report_table:
CREATE TABLE report_table
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
report_period date NOT NULL,
reporter_id int(11) unsigned NOT NULL,
dir varchar(20) NOT NULL,
report_content mediumblob NOT NULL,
PRIMARY KEY ( id ),
UNIQUE KEY ( reporter_id, report_period )
KEY ( reporter_id ),
KEY ( report_period )
);
2. Create report_summary
CREATE TABLE report_summary
(
id int(11) unsigned NOT NULL,
buy_amount double DEFAULT NULL,
sell_amount double DEFAULT NULL,
PRIMARY KEY ( id ),
CONSTRAINT FOREIGN KEY ( id ) REFERENCES report_table ( id )
ON DELETE CASCADE ON UPDATE CASCADE
);
3. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_summary.id = report_table.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';
4. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_table.id = report_summary.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';
To copy to clipboard, switch view to plain text mode
Bookmarks