Question

我希望有人能够帮助我。我对我的SQL很抱着新意,因此我没有完全接受它的所有能力。

我收到了一份以数据格式的电子表格:

child Name |Gender| dob| class|Parent name|parent phone|parent address

我将这3个表格分开,其中包括:

Child_details   Datatype    Required    Key
Child_id    INT AUTO_INCREMENT  NOT NULL    Primary
Child_fname VARCHAR(15)     NOT NULL     
Child_sname VARCHAR(15)     NOT NULL     
Gender  Enum(M,F) NOT NULL  NOT NULL     
DoB DATE NOT NULL   NOT NULL     
Parent/Carer id INTEGER NOT NULL    Foreign


Parent_details  Datatype    Required    Key
Parent_id   INT AUTO_INCREMENT  NOT NULL    Primary
Parent_title    CHAR(5)      
Paren _fname    VARCHAR(15)     NOT NULL     
Parent/_sname   VARCHAR(15)     NOT NULL     
Parent _Add1    VARCHAR(25)     NOT NULL     
Parent _Add2    VARCHAR(25)          
Parent _city    VARCHAR(25)          
Parent _Pcode   VARCHAR(15)     NOT NULL     
Parent _phone   VARCHAR(15)     NOT NULL     


Activity    Datatype    Required    Key
Activity_name   CHAR(8) NOT NULL     
Activity_day    CHAR(5) NOT NULL

然后用活动数据为滚动名单提供一个过渡表。部分儿童从事一等工作,有些则从事三类工作:

Schedule    Datatype    Required    Key
Child_id    INT NOT NULL    Foreign, Composite
Activity_id INT NOT NULL    Foreign, Composite

我试图撰写一个问题,如果我所输入的数据正确,我可以进行核查。我想能够以与电子表格相同的格式看待结果。这是我迄今提出的:

SELECT CONCAT_WS(   ,Child_fname,Child_sname) AS  Child Name ,
Child_gender AS  Gender ,
Child_dob AS  DoB ,
COUNT Activity_name AS Activities,
CONCAT_WS(   ,Parent_title,Parent_fname,Parent_sname) AS  Parent/Carer name ,
Parent_phone AS  Parent/Carer phone ,
CONCAT_WS(   ,Parent_add1,Parent_add2,Parent_city,Parent_pcode) AS  Parent/Carer Address 
FROM Child,Activity,Parent;

But the results I get show each child does all of the activities and that they all have each. Am I missing something obvious, do I need to use COUNT DISTINCT or GROUP BY, or have I created the tables wrong?

Any information anyone can provide would be greatly appreciated. I hope I have provided enough information, but not too much.

Answer 1

你们需要告诉我的SQL如何一起加入表格(没有这一信息,就把每张桌上的每一行与每张桌上的每一行一行一致,正如你所描述的那样):

SELECT
  ...
FROM
       Child
  JOIN Schedule USING (Child_id)
  JOIN Activity USING (Activity_id) -- does this column exist in Activity?
  JOIN Parent   USING (Parent_id)   -- does this column exist in Child?

您不妨遵循tutorials on JOIN。

友情链接