MySQL Join并创建新的列值 [英] MySQL Join and create new column value

问题描述

我有乐器清单和老师乐器清单.

I have an instrument list and teachers instrument list.

我想获得带有ID和名称的完整工具清单.

I would like to get a full instrument list with id and name.

然后检查教师表表中的乐器，如果有特定的老师有乐器，则在新列中添加NULL或1值.

Then check the teachers_instrument table for their instruments and if a specific teacher has the instrument add NULL or 1 value in a new column.

然后我可以用它来遍历Codeigniter中的某些工具复选框，从数据库中提取所需数据似乎更有意义，但是正在努力编写查询.

I can then take this to loop over some instrument checkboxes in Codeigniter, it just seems to make more sense to pull the data as I need it from the DB but am struggling to write the query.

teaching_instrument_list

- id
- instrument_name

 teachers_instruments

- id
- teacher_id
- teacher_instrument_id

 SELECT
  a.instrument,
  a.id
 FROM
   teaching_instrument_list a
 LEFT JOIN
 (
    SELECT teachers_instruments.teacher_instrument_id
    FROM teachers_instruments
    WHERE teacher_id = 170
 ) b ON a.id = b.teacher_instrument_id  

我的查询如下:

 instrument name    id   value
 ---------------    --   -----
 woodwinds          1    if the teacher has this instrument, set 1
 brass              2     0
 strings            3     1

推荐答案

一种可能的方法:

    SELECT i.instrument_name, COUNT(ti.teacher_id) AS used_by 
      FROM teaching_instrument_list AS i
 LEFT JOIN teachers_instruments AS ti
        ON ti.teacher_instrument_id = i.id
  GROUP BY ti.teacher_instrument_id
  ORDER BY i.id;

这是 SQL小提琴 (表的命名是有点不同).

Here's SQL Fiddle (tables' naming is a bit different).

说明:在instrument_id上使用LEFT JOIN时，我们将为每个乐器获得与使用它的老师一样多的teacher_id值-或只有一个NULL值(如果没有人使用的话).下一步是使用GROUP BY和COUNT()来按工具对结果集进行分组并计算其用户(不包括NULL值的行).

Explanation: with LEFT JOIN on instrument_id we'll get as many teacher_id values for each instrument as teachers using it are - or just a single NULL value, if none uses it. The next step is to use GROUP BY and COUNT() to, well, group the result set by instruments and count their users (excluding NULL-valued rows).

如果要显示所有乐器和一些标志以显示是否正在使用老师，则需要另一个LEFT JOIN:

If what you want is to show all the instruments and some flag showing whether or now a teacher uses it, you need another LEFT JOIN:

    SELECT i.instrument_name, NOT ISNULL(teacher_id) AS in_use
      FROM teaching_instrument_list AS i
 LEFT JOIN teachers_instruments AS ti
        ON ti.teacher_instrument_id = i.id
       AND ti.teacher_id = :teacher_id;

演示 .

这篇关于MySQL Join并创建新的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！