我需要INSERT命令的帮助，两个表

I have two tables:

我有两张桌子:

SUBJECTS IDsubjects username subject

主题ID主题用户名主题

GRADES IDgrades IDsubject grade

GRADES IDgradesIDubject grade

Where SUBJECTS.IDsubjects and GRADES.IDsubject are connected forming a relational database. I want to insert a grade based on a user's subject but I don't know how to do that.

SUBJECTS.IDsubjects和GRADES.IDsubject连接在一起形成关系数据库。我想根据用户的主题插入成绩,但我不知道该怎么做。

I have three values: - username - subject - grade

我有三个值: - 用户名 - 主题 - 成绩

I want to INSERT a grade into GRADES table based on a subject because there will be many subjects and I want a specific grade to be applied to a specific subject.

我想根据主题将成绩插入到GRADES表中,因为会有很多科目,我希望将特定成绩应用于特定科目。

4 个解决方案

#1

INSERT INTO GRADES (IDSUBJECT, GRADE)
  (
    (SELECT SubjID
     FROM SUBJECTS
     WHERE USERNAME = [username]
     AND SUBJECT = [subject]),
    [grade]
  )

Mind you, is there a reason you don't have the Subject ID but only the subject name? Also, do you really want the username to be a field in the subjects table and not the grades table?

请注意,您是否有理由不拥有主题ID但只有主题名称?另外,您真的希望用户名是主题表中的字段而不是成绩表吗?

#2

UPDATE Grades SET grade='F' WHERE subject IN(SELECT subject FROM *)

#3

I'm assuming your GRADES.IDgrades is an auto_increment key and will be automatically generated. Also, in the SELECT statement the value 'A' is where you will put your actual grade value. It isn't actually selecting this from the SUBJECTS table, simply selecting the explicit value specified. I also used 'math' and 'billy' as your values for grade and subject, respectively.

我假设你的GRADES.IDgrades是一个auto_increment键,将自动生成。此外,在SELECT语句中,值“A”是您放置实际成绩值的位置。它实际上并没有从SUBJECTS表中选择它,只需选择指定的显式值即可。我还分别用'数学'和'比利'作为你的成绩和科目的价值观。

INSERT into GRADES (IDsubject, grade)
    SELECT IDsubjects, 'A'
    FROM SUBJECTS 
    WHERE username = 'billy' 
        AND subject = 'math'

This will insert a new row in GRADES with the IDsubject as selected from the IDsubjects table.

这将在GRADES中插入一个新行,其中IDubject从IDsubjects表中选择。

#4

Assuming that IDGrades is automatically generated ID,

假设IDGrades是自动生成的ID,

INSERT INTO GRADES (IDSubject, GRADE)
SELECT  s.IDsubjects,
        @GRADE
FROM    SUBJECTS s
WHERE   s.USERNAME = @USERNAME
    AND s.[SUBJECT] = @SUBJECT

Very strange naming convention with plural ID columns (IDSubjects)

具有多个ID列的非常奇怪的命名约定(IDSubjects)

#1

INSERT INTO GRADES (IDSUBJECT, GRADE)
  (
    (SELECT SubjID
     FROM SUBJECTS
     WHERE USERNAME = [username]
     AND SUBJECT = [subject]),
    [grade]
  )