全部資料列表

# 疾病名 食物 是否可食

糖尿病+高血壓可食物列表(未篩)

# 疾病名 食物 是否可食

糖尿病+高血壓可食物列表(已篩)

# 食物



food.sql內容

DROP TABLE IF EXISTS food;
CREATE TABLE food(f_id INTEGER PRIMARY KEY, f_name TEXT);
INSERT INTO food(f_id,f_name) VALUES('1','蘋果');
INSERT INTO food(f_id,f_name) VALUES('2','甘蔗');
INSERT INTO food(f_id,f_name) VALUES('3','生梨');
INSERT INTO food(f_id,f_name) VALUES('4','橘子');
INSERT INTO food(f_id,f_name) VALUES('5','鳳梨');

DROP TABLE IF EXISTS disease;
CREATE TABLE disease(d_id INTEGER PRIMARY KEY, d_name TEXT);
INSERT INTO disease(d_id,d_name) VALUES('1','糖尿病');
INSERT INTO disease(d_id,d_name) VALUES('2','高血壓');
INSERT INTO disease(d_id,d_name) VALUES('3','心臟病');

DROP TABLE IF EXISTS eatable;
CREATE TABLE eatable(e_id INTEGER PRIMARY KEY, f_id INTEGER, d_id INTEGER, e_stat INTEGER);
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('1','1','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('2','2','1','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('3','3','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('4','4','1','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('5','5','1','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('6','1','2','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('7','2','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('8','3','2','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('9','4','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('10','5','2','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('11','1','3','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('12','2','3','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('13','3','3','1');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('14','4','3','0');
INSERT INTO eatable(e_id,f_id,d_id,e_stat) VALUES('15','5','3','0');

SQL全部資料列表

SELECT eatable.e_id AS e_id,eatable.e_stat AS e_stat, food.f_name AS f_name,disease.d_name AS d_name 
FROM eatable LEFT JOIN food on eatable.f_id=food.f_id LEFT JOIN disease on eatable.d_id=disease.d_id 
ORDER BY d_name,e_stat DESC,f_name;

SQL糖尿病+高血壓可食未篩

SELECT eatable.e_id AS e_id,eatable.e_stat AS e_stat, food.f_name AS f_name,disease.d_name AS d_name 
FROM eatable LEFT JOIN food on eatable.f_id=food.f_id LEFT JOIN disease on eatable.d_id=disease.d_id 
WHERE (disease.d_id=1 AND e_stat=1) OR (disease.d_id=2 AND e_stat=1);

SQL糖尿病+高血壓可食已篩

SELECT eatable.f_id AS f_id,food.f_name FROM eatable LEFT JOIN food ON food.f_id=eatable.f_id 
WHERE d_id=1 AND e_stat=1 
INTERSECT 
SELECT eatable.f_id AS f_id,food.f_name FROM eatable LEFT JOIN food ON food.f_id=eatable.f_id 
WHERE d_id=2 AND e_stat=1;