SQL запрос с подсчетом строк и другими операциями

[Alisandres]

День добрый!
У меня есть две таблицы: tabl1 и tabl2 в MySQL и/или MSSQL
Структура tabl1:
id name par1 par2 parn
1 АААА     1    5       12
2 BBBB     2    30      8
3 CCCC    4    15       10

Структура tabl2:
id name type
1  uuu     1
2  bbb     2
3  yyy      3
4  dad     1
5  уйк      2
6  dwd     3
7  ewe     2
8   2eqe   3

Подробней, допустим: tabl1 это таблица авторов, у которых есть некие параметры (родной город (par1), стиль написания (par2) и прочее (parn)), в таблице  tabl2 содержится значение этих данных с именованием: id - номер записи, подставляемый в имена параметров, name- значение параметра,type - тип параметра ( тип 1, это родной город, тип 2 этостиль написания ...)

Требуется при выводе всех типов параметров (select * FROM tabl2)  из  tabl2: посчитать количество авторов (строк в таблице  tabl1:)   у которых то или иное значение задействовано. Конкретно: у нас есть запись с id 1, именем Санкт-Петербург и типом параметра 1 (что означает город), такой город буде ту нас у 10 авторов, нужно получить массив:
id  name  type count
1   СПБ       1    10

Вот как-то так...

[Finch]

Думаю, что то типа этого

Код: (SQL)

SELECT Z.id AS ID, z.name AS Name, z.TYPE AS TYPE, COUNT(1) AS COUNT
FROM tabl1 AS A,  (SELECT * FROM tabl2 WHERE TYPE = 1) AS Z
WHERE Z.id = A.par1
GROUP BY Z.id;

[Sla]

group by нужно делать по все полям.

Я фигею от постанавки задачи.

Имею А, Б, В
Хочу Г, Д, Е
А=Д, В=Г
а ты сиди сопоставляй.

[Finch]

Sla, Зачем? id  точно уникально и соответствует названию города. Если у него будет один и тот же город под разными id, это его проблема.

[Sla]

Finch, а ты попробуй

[Alisandres]

Sla id уникально, что записано под name это роли не играет, хоть Санкт-Петербург, хоть санктпетербург... и при постановки задачи я расписал как можно точнее что бы было проще.

Finch это вложенный запрос? Может проще в цикле делать отдельно запросы на подсчет?

[Alisandres]

Делаю вывод что ни чего не получиться, потому что каждому числовому параметру type из 2 таблицы соответствует своя именованная колонка в 1 таблице. Если только считать строки по всей 1 таблице исключая поле id. Или переделать структуру базы данных к одному виду (числа или имена). Ваши мнения господа?

[Sla]

Alisandres, какое ты хочешь мнение?

Покажи реальные данные.
Сформулируй вопрос.

[Dimka]

Что-то я насчёт цикла не понял. Finch предложил разумное решение.

Делаю вывод что ни чего не получиться, потому что каждому числовому параметру type из 2 таблицы соответствует своя именованная колонка в 1 таблице.

С этого и начинать надо было. Задача получения "шахматки" или развёртывания строк в столбцы (перекрёстный запрос) - классическая. Решается при помощи динамических запросов (текст которых автоматически вычисляется).

У тебя задача обратная. Однако решается тоже при помощи динамических запросов. Если тебя даже вложенные запросы напрягают, то - да, лучше циклами пиши. Так тебе понятнее будет.

К тому же ты не сказал, какой СУБД пользуешься.

[x77]

Sla, Зачем? id  точно уникально и соответствует названию города. Если у него будет один и тот же город под разными id, это его проблема.

Sla имеет в виду, что в group by должны быть перечислены все поля из селекта, кроме аггрегатных. иначе непонятно, какие значения других полей выводить для группировочного поля.

[Finch]

x77, Будет подцепляться первое встреченное значение. Так как все остальные поля напрямую зависят от id и если не делать групировку то все строки при одинаковом id будут эквивалентны, то я не вижу разници между первой и другой любой строкой.

[Finch]

Собственно провел эксперемент Вот его результаты

Код:

mysql> show tables;
+----------------+
| Tables_in_prob |
+----------------+
| author         |
| svod           |
+----------------+
2 rows in set (0.00 sec)

mysql> desc author;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| par1  | int(11)     | YES  |     | NULL    |       |
| par2  | int(11)     | YES  |     | NULL    |       |
| parn  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc svod;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| type  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from author;
+------+------+------+------+------+
| id   | name | par1 | par2 | parn |
+------+------+------+------+------+
|    1 | ser  |    1 |    3 |    5 |
|    2 | art  |    2 |    4 |    6 |
|    3 | res  |    1 |    3 |    6 |
|    4 | tres |    2 |    4 |    5 |
+------+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from svod;
+------+--------+------+
| id   | name   | type |
+------+--------+------+
|    1 | Sanc   |    1 |
|    2 | Mosc   |    1 |
|    3 | Fant   |    2 |
|    4 | Detect |    2 |
|    5 | dev    |    3 |
|    6 | den    |    3 |
+------+--------+------+
6 rows in set (0.00 sec)

mysql> select Z.id as ID, Z.name as Name, Z.type as Type, count(1) as Count 
    -> from author as A,  (select id,name,type from svod where type = 1) as Z
    -> where Z.id = A.par1
    -> Group by Z.id;
+------+------+------+-------+
| ID   | Name | Type | Count |
+------+------+------+-------+
|    1 | Sanc |    1 |     2 |
|    2 | Mosc |    1 |     2 |
+------+------+------+-------+
2 rows in set (0.01 sec)

[Alisandres]

Dimka, не напрягают, не совсем понимаю как оно работает...

Версия MSSQL 2005 и /или mysql 5
Показываю реальные данные одной из таких таблиц - таблица описания компьютеров:
Таблица config, в ней конфигурационные записи:

id	name	type
1	Asus P5	1
2	Intel 345	1
3	Radeon x600	2
4	P4 3.0 Ghz	3
5	2x512 Pc 3200	4
6	Radeon x650	2
7	MSI K9N Ultra	1

где полю type соответствует номер типа записи:
1 - материнские платы
2 - видеокарты
3 - процессоры
4 - ОЗУ

Таблица pc, в ней записи о компьютерах

id	numer	moter	video	cpu	ozu
1	1312	1	3	4	5
2	1313	7	6	4	5

в котором согласно типу поля, например запись id2 и тип поля moter внесена запись из таблицы config под id 7

я сделал вычисление количества циклом при генерировании списка, хотелось бы сделать что бы эти данные считались запросом SQL.

[Dimka]

Alisandres, это новая разработка или доработка существующей программы?

Если новая, то разумнее будет перепроектировать базу данных. Если доработка существующей - писать запрос.

Перепроектирование: У тебя есть справочник config. У тебя где-то должен быть справочник type. Нужно ввести дополнительную сущность, например, pc_element, в которой записями формировать состав компьютеров. Это будет лучше, потому что комплектация компьютеров может быть разной, поэтому колонки в твоей таблице pc теоретически могут содержать NULL. Это будет лучше в том случае, когда у тебя появится новый тип оборудования - не нужно будет менять структуру таблиц.

type

id	descript
1	Motherboard
2	Video
3	CPU
4	RAM

config

id	name	type
1	Asus P5	1
2	Intel 345	1
3	Radeon x600	2
4	P4 3.0 Ghz	3
5	2x512 Pc 3200	4
6	Radeon x650	2
7	MSI K9N Ultra	1

pc

id	number
1	1312
2	1313

pc_element

pc	config
1	1
1	3
1	4
1	5
2	7
2	6
2	4
2	5

Запрос данных становится тривиальным.