Блог

Главная - Вопросы по программированию - Сводный запрос, дающий значения null для других обязательных столбцов

Сводный запрос, дающий значения null для других обязательных столбцов

#mysql #sql

Вопрос:

У меня есть следующая таблица:

  create table table1 (
      Id int not null auto_increment,
      JobId varchar(255) not null,
      FieldOrder int(11) not null,
      FieldName varchar(255) not null,
      FieldValue varchar(255) not null,
      primary key (Id)
    );

Он содержит следующие данные:

 | Id  | JobId | FieldOrder | FieldName    | FieldValue |
| --- | ----- | ---------- | ------------ | ---------- |
| 1   | 1     | 1          | Customer Id  | C01        |
| 2   | 1     | 2          | Order Number | 3923       |
| 3   | 1     | 3          | Architect Id | DK         |
| 4   | 2     | 1          | Customer Id  | C02        |
| 5   | 2     | 2          | Order Number | 23         |
| 6   | 2     | 3          | Architect Id | AJ         |
| 7   | 3     | 1          | Customer Id  | C03        |
| 8   | 3     | 2          | Plot Id      | 3          |
| 9   | 3     | 3          | Architect Id |            |

Я пытаюсь написать сводный запрос, чтобы получить следующее:

  ------------- -------------- -------------- 
| customer_id | order_number | architect_id |
 ------------- -------------- -------------- 
| C01         | 3923         | DK           |
| C02         | 23           | AJ           |
| C03         |              |              |
 ------------- -------------- --------------

ТЕКУЩИЙ ПРОГРЕСС

Пока мой запрос выглядит так:

 select case
           when FieldName = 'Customer Id' then FieldValue
       end as "customer_id",
      case
          when FieldName = 'Order Number' then FieldValue
      end as "order_number",
      case
          when FieldName = 'Architect Id' then FieldValue
      end as "architect_id"
from table1
group by JobId;

Локально это дает мне следующее:

  ------------- -------------- -------------- 
| customer_id | order_number | architect_id |
 ------------- -------------- -------------- 
| C01         | NULL         | NULL         |
| C02         | NULL         | NULL         |
| C03         | NULL         | NULL         |
 ------------- -------------- --------------

Я подозреваю, потому что, когда я это делаю group by , у меня больше нет доступа к моим полям, поэтому я застрял, я не совсем уверен, как я могу заставить это работать.

Я тоже создал db-скрипку, хотя она даже не запускается из-за некоторых настроек по умолчанию.

Комментарии:

1. Ваш запрос неверен. Вы группируетесь по идентификатору задания, но выбираете выражение для имени поля и значения поля. Возможно, существует много имен полей и значений полей для каждого идентификатора задания, так что следует использовать СУБД? Похоже, вы находитесь в печально известном режиме обмана MySQL. Это плохая идея. SET sql_mode = 'ONLY_FULL_GROUP_BY'; чтобы убедиться, что вы получите синтаксическую ошибку при таких недопустимых запросах.

2. Разве не ужасно работать с таблицами ключей/значений? Представьте себе таблицу заданий, которая просто содержит идентификатор пользователя, номер заказа и идентификатор архитектуры. Разве это не было бы замечательно? Это даже помешает вам вводить идентификаторы клиентов, номера заказов или архитекторов, которых не существует.

3. Вы правы, это ужасно 🙁 Хотя я не создавал эту таблицу, я просто пишу запрос, чтобы получить некоторые сведения. В идеале, нам бы дали время на рефакторинг вещей.

4. Возможно, тогда вы сможете создать представление, основанное на подходе Гордона. Тогда вам не нужно делать это примерно в каждом запросе, который работает с этой таблицей.

5. Это хороший крик, я так и сделаю.

Ответ №1:

Вам нужна агрегация:

 select jobid,
       max(case when FieldName = 'Customer Id' then FieldValue
           end) as customer_id,
       max(case when FieldName = 'Order Number' then FieldValue
           end) as order_number,
       max(case when FieldName = 'Architect Id' then FieldValue
           end) as architect_id
from table1
group by JobId;

Примечания:

  • Я добавил jobid к select этому . Обычно вы хотели бы различать строки.
  • Не экранируйте идентификаторы, которые не нужно экранировать. Я убрал двойные кавычки. Они не нужны.
Метки: ,