Блог

Главная - Вопросы по программированию - группировка с помощью Microsoft query

группировка с помощью Microsoft query

#excel #ms-access #microsoft-query

#excel #ms-access #microsoft-запрос

Вопрос:

У меня есть такая таблица в Excel

 ID | NAME | JOB     | PRICE
1  | alex | Java    | 100
1  | alex | C       | 100
1  | alex | PHP     | 500
2  | road | Android | 400
2  | road | Ruby    | 400
3  | brit | Java    | 200
3  | brit | PHP     | 500
3  | brit | C       | 100
3  | brit | DotNet  | 300

Мне нужен вывод в следующем формате

 ID | NAME | JOB                   | PRICE
1  | alex | Java,C  ,PHP          | 700
2  | road | Android,Ruby          | 800
3  | brit | Java, PHP, C, DotNet  | 1100

итак, я использую Microsoft Query для генерации этого вывода, используя следующий запрос

 Select ID, NAME , GROUP_CONCAT(JOB) ,Sum(PRICE) from Table Group By ID;

но он продолжает показывать мне ошибку .. любое предложение

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

1. GROUP_CONCAT (и без указания всех неагрегированных столбцов в GROUP BY ) поддерживается MySQL, но ни Access, ни Excel AFAIK.

2. Не существует чистого SQL с использованием MS Access SQL для достижения желаемого результата, предполагающего, что это ваш источник данных. Может потребоваться комбинация VBA.

3. у меня есть данные в моем файле excel, поэтому я использую Microsoft Query, чтобы разрешить мне использовать sql-запрос в этих данных для создания таблицы, подобной этой

4. Тогда почему вы помечаете ms-access?

Ответ №1:

Возможно, самое близкое, что вы можете получить с помощью SQL, — это запрос перекрестной таблицы, который доступен на диалекте Jet / ACE SQL. Jet / ACE — это набор Windows.dll-файлы, используемые для подключений ODBC / OLEDB к базам данных MS Access и электронным таблицам Excel. Обратите внимание, что Jet / ACE устанавливает максимальное ограничение в 255 столбцов.

В частности, ниже вычисляется текущее количество заданий в алфавитном порядке (с максимальным количеством повторений), а затем выполняется поворот из этого источника:

 TRANSFORM Max(main.Job) As MaxOfJob
SELECT main.ID, main.[Name], Sum(main.PRICE) AS SumOfPrice
FROM (
   SELECT i.*, (SELECT Count(*) FROM [Sheet1$] sub
                WHERE sub.ID = i.ID AND sub.JOB <= i.JOB) As RunCount
   FROM [Sheet1$] i
) AS main
GROUP BY main.ID, main.[Name]
PIVOT main.RunCount;

-- ID   Name    SumOfPrice  1        2       3      4
-- 1    alex    700         C        Java    PHP    
-- 2    road    800         Android  Ruby       
-- 3    brit    1100        C        DotNet  Java   PHP

Получив завершенный набор результатов, объедините все пронумерованные ячейки столбца в разделенный запятыми столбец ЗАДАНИЯ с помощью Excel CONCATENATE() или выполните цикл по каждой ячейке столбца с помощью VBA.

Ответ №2:

То, как вы хотите, чтобы ваша таблица выглядела, включает в себя «Комбинированное поле», которое не одобряется при проектировании базы данных. Ваш первоначальный дизайн правильный в том смысле, что каждый язык занимает по 1 записи в таблице.