MySql数据库结构适用于：基于单列和不同值进行搜索 [英] MySql database structure for : Search based on single column and different value

问题描述

我需要创建一个数据库，但我没有得到正确的逻辑。

I need to create a database but I'm not getting the correct logic.

这是一个故事，但我没有任何其他方法来解释。

It's like a story but I don't have any other way to explain it.

现场是：
有三个实体; City ，图书和图书馆。

我必须执行一个任务，我将获得 CityId 和数组 BookIds 作为输入，我必须根据以下条件抛出 LibraryNames ：

I have to perform a task in which I will get CityId and Array of BookIds as input and I have to throw the LibraryNames based on following criteria:

1. 图书馆应在给定的城市中。


2. 图书馆应该包含所有图书。

1. The Library should be in the given City.
2. The Library should have all the books.

类型
第一个 City （它将为我提供一个 CityName 或 CityId ）
和
其次是 Books （它将提供一个由多个 BookIds 或多个 BookNames ）。

I have two input types First for City (It will Provide me a single CityName or CityId) and Second is for Books (It will provide an array which consists of multiple BookIds or multiple BookNames).

如果很难理解，

这是我的一个严重关注。

It is a serious concern of me.

推荐答案

试试这个：

create table City
    (
     Id int,
     Name varchar(50)
    );

    insert into City (Id, Name) 
    VALUES 
    (1, 'Toronto'), 
    (2, 'Chicago')

    create table Libraries(
    Id int,
    Name varchar(50),
    CityId int
    );

    insert into Libraries (Id, Name, CityId) 
    VALUES 
    (1, 'Toronto Library 1', 1),
    (2, 'Toronto Library 2', 1),
    (3, 'Chicago Library 1', 2),
    (4, 'Chicago Library 2', 2)

    create table Books(
    Id int,
    Isbn varchar(12),
    LibraryId int
    );

    insert into Books (Id, Isbn, LibraryId) 
    Values
    (1, '1234567891', 1),
    (2, '13344555', 1),
    (3, 'x123sada', 1),
    (4, 'xasdsadas', 2),
    (5, 'axxzksda', 2)

    select DISTINCT b.Name 
    from Books a
    inner join Libraries b
    on a.LibraryId = b.Id
    where Isbn in ('1234567891', '13344555')
    and b.CityId = 1

编辑：
或4NF：

or 4NF:

create table City
(
 Id int,
 Name varchar(50)
);

insert into City (Id, Name) 
VALUES 
(1, 'Toronto'), 
(2, 'Chicago')

create table Libraries(
Id int,
Name varchar(50),
CityId int
);

insert into Libraries (Id, Name, CityId) 
VALUES 
(1, 'Toronto Library 1', 1),
(2, 'Toronto Library 2', 1),
(3, 'Chicago Library 1', 2),
(4, 'Chicago Library 2', 2)

create table Books(
Id int,
Isbn varchar(12),
);

insert into Books (Id, Isbn) 
Values
(1, '1234567891'),
(2, '13344555'),
(3, 'x123sada'),
(4, 'xasdsadas'),
(5, 'axxzksda')

create table LibraryBooks
(
LibraryId int,
BookId int
);

insert into LibraryBooks (LibraryId, BookId)
VALUES
(1, 1),
(1, 2),
(3, 1),
(2, 4),
(5, 2)

select DISTINCT c.Name
from Books a
inner join LibraryBooks b
on a.Id = b.BookId
inner join Libraries c on
c.Id = b.LibraryId
where Isbn in ('1234567891', '13344555')
and c.CityId = 1

这篇关于MySql数据库结构适用于：基于单列和不同值进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！