使用division选择mysql中的所有元素

Question

食物可以是简单的，也可以是聚集的。如果它是简单的，那么它有一个类型..如果agregate它有一个简单食物的成分，我需要创建一个查询来选择所有出售具有食物type=meat的所有agregate菜肴的餐馆。我试了很多方法，但都不能解决问题。

    create table Restaurant(
        nameR VARCHAR(80),
        primary key (nameR));

    create table Sold(
        nameF VARCHAR(80),
        nameR VARCHAR(80),
        date VARCHAR(20);
        constraint c3 primary key(nameF,nameR,date),
        foreign key(nameF) references Dish(nameF),
        foreign key(nameR) references Restaurante(nameR),
        foreign key(date) references Data(date));

    create table Dishe(
        nameF VARCHAR(80),
        primary key (nameF),
        foreign key (nameF) references Food(nameF));

   create table Simple( 
       nameF VARCHAR(80), 
      type VARCHAR(80),
      primary key (nameF), 
       foreign key (nameF) references Food(nameF));

    create table Agregate(
       nameF VARCHAR(80),
       calorias double,
       primary key (nameF),
       foreign key (nameF) references Food(nameF));

    create table Composition(
       nameAgg VARCHAR(80),
        nameS VARCHAR(80),
        quantidade integer,
       constraint c1 primary key(nameAgg,nameS),
       foreign key(nameAgg) references Agregate(nameF),
      foreign key(nameS) references Simple(nameF));

    create table Food( 
         nameF varchar(80)
         primary key (nameF));

我认为我应该使用除法，对吗？告诉我你是怎么想的

谢谢

Answer 1

您的SQL在中有一些语法错误，而且它引用了"Sold“的外键中的数据表，我不确定为什么会存在"Dishe”表-它似乎只是存储与食品相同的数据？

无论如何，下面是用于创建表的已更正的SQL：

create table Restaurant(
    nameR VARCHAR(80),
    primary key (nameR)
);

create table Food( 
    nameF varchar(80), 
    type varchar(80),
    primary key (nameF)
);

create table Dishe(
    nameF VARCHAR(80),
    primary key (nameF),
    foreign key (nameF) references Food(nameF)
);

create table Sold(
    nameF VARCHAR(80),
    nameR VARCHAR(80),
    date VARCHAR(20),
    constraint c3 primary key(nameF,nameR,date),
    foreign key(nameF) references Dishe(nameF),
    foreign key(nameR) references Restaurant(nameR)
);

我们可以插入一些测试数据：

INSERT INTO Restaurant (nameR) VALUES ('McDonalds');
INSERT INTO Restaurant (nameR) VALUES ('Vital Ingredient');
INSERT INTO Restaurant (nameR) VALUES ('David Food');
INSERT INTO Food (nameF, type) VALUES ('Burger', 'meat');
INSERT INTO Food (nameF, type) VALUES ('Salad', 'veg');
INSERT INTO Dishe (nameF) VALUES ('Burger');
INSERT INTO Dishe (nameF) VALUES ('Salad');
INSERT INTO Sold VALUES ('Salad', 'David Food', '2013-12-05');
INSERT INTO Sold VALUES ('Burger', 'McDonalds', '2013-12-04');
INSERT INTO Sold VALUES ('Burger', 'McDonalds', '2013-12-05');
INSERT INTO Sold VALUES ('Burger', 'Vital Ingredient', '2013-12-05');
INSERT INTO Sold VALUES ('Salad', 'Vital Ingredient', '2013-12-04');

这就创建了三家餐厅，只有一家餐厅只卖肉菜。

我可以想到两种方法来返回那些卖了所有肉类菜肴的餐厅。第一个没有使用除法-它使用GROUP_CONCAT函数将每个餐厅提供的所有类型的菜肴粘合在一起，并查看这是否等于“肉”：

SELECT 
    Sold.nameR
FROM
    Sold
INNER JOIN
    Food
ON
    Food.nameF = Sold.nameF
GROUP BY 
    Sold.nameR
HAVING
    GROUP_CONCAT(DISTINCT Food.type) = 'meat'
;

第二种方法使用除法来计算每家餐厅售出的肉类菜肴的数量，并将其除以提供的菜肴总数，然后检查这是否等于1-即，提供的菜肴100%是肉类。由于子查询，此方法可能比上面的方法慢：

SELECT 
    Restaurant.nameR
FROM
    Restaurant
WHERE
    (
    SELECT 
        COUNT(*) 
    FROM 
        Sold 
    INNER JOIN 
        Food 
    ON 
        Food.nameF = Sold.nameF 
    WHERE 
        Food.type = 'meat' 
    AND 
        Sold.nameR = Restaurant.nameR 
    ) / (
    SELECT 
        COUNT(*) 
    FROM 
        Sold 
    INNER JOIN 
        Food 
    ON 
        Food.nameF = Sold.nameF 
    WHERE 
        Sold.nameR = Restaurant.nameR 
    ) = 1
;