我的运行环境是:mysql+php
我有两张表,一张是销售菜肴的表:
sale(菜肴ID,单位,数量);(比如蛋炒饭,5份)
另一张表是菜肴中需要的食材:
菜肴dish(菜肴ID,食材1,数量1,食材2,数量2,食材3,数量3,续行标志);
因为一个菜肴可能需要的食材超过3张食材,所以菜肴dish表的一个菜肴ID会有
多条记录。
现在我的销售表里销售了不同的菜肴,我需要统计中各种不同食材的数量,
请问能用什么办法?能否给个思路?
我有两张表,一张是销售菜肴的表:
sale(菜肴ID,单位,数量);(比如蛋炒饭,5份)
另一张表是菜肴中需要的食材:
菜肴dish(菜肴ID,食材1,数量1,食材2,数量2,食材3,数量3,续行标志);
因为一个菜肴可能需要的食材超过3张食材,所以菜肴dish表的一个菜肴ID会有
多条记录。
现在我的销售表里销售了不同的菜肴,我需要统计中各种不同食材的数量,
请问能用什么办法?能否给个思路?
还是先看数据量,我猜菜肴的总数应该不会很多?所以dish表也不会很大。
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| col1 | varchar(10) | YES | | NULL | |
| col1_num | tinyint(4) | YES | | NULL | |
| col2 | varchar(10) | YES | | NULL | |
| col2_num | tinyint(4) | YES | | NULL | |
| col3 | varchar(10) | YES | | NULL | |
| col3_num | tinyint(4) | YES | | NULL | |
| r_flag | tinyint(4) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> select * from sam;
+------+------+----------+------+----------+------+----------+--------+
| id | col1 | col1_num | col2 | col2_num | col3 | col3_num | r_flag |
+------+------+----------+------+----------+------+----------+--------+
| 1 | a | 2 | b | 3 | c | 4 | 1 |
| 1 | d | 1 | NULL | NULL | NULL | NULL | 0 |
| 2 | b | 2 | a | 3 | c | 4 | 1 |
| 2 | d | 2 | e | 3 | f | 4 | 1 |
| 2 | g | 2 | h | 3 | i | 4 | 0 |
| 3 | a | 1 | NULL | NULL | NULL | NULL | 0 |
+------+------+----------+------+----------+------+----------+--------+
6 rows in set (0.00 sec)mysql> select a.name,sum(a.num) from (
-> select col1 as name,col1_num as num from sam
-> union all
-> select col2 as name,col2_num as num from sam
-> union all
-> select col3 as name,col3_num as num from sam
-> ) a group by a.name;
+------+------------+
| name | sum(a.num) |
+------+------------+
| NULL | NULL |
| a | 6 |
| b | 5 |
| c | 8 |
| d | 3 |
| e | 3 |
| f | 4 |
| g | 2 |
| h | 3 |
| i | 4 |
+------+------------+
10 rows in set (0.00 sec)