TCS54321 Ответов: 1

Нужна помощь в запросе mysql

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

# ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status_
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
'1', NULL, NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, '2', NULL, NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, '35', NULL, NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, '56', NULL, NULL, NULL
NULL, NULL, NULL, NULL, '3', NULL, NULL
NULL, NULL, NULL, NULL, '4', NULL, NULL
NULL, NULL, NULL, NULL, '2', NULL, NULL
NULL, NULL, NULL, NULL, '1', NULL, NULL
NULL, NULL, NULL, NULL, '5', NULL, NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, '100', NULL
NULL, NULL, NULL, NULL, NULL, NULL, '1'
NULL, NULL, NULL, NULL, NULL, NULL, '1'
NULL, NULL, NULL, NULL, NULL, NULL, '0'
NULL, NULL, NULL, NULL, NULL, NULL, '0'
NULL, NULL, NULL, NULL, NULL, NULL, '0'



но мне нужен такой вывод:-

# ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status_
'1', 2, 35, 56, 3, 100, 1
'1', 2, 35, 56, 4, 100, 1
'1', 2, 35, 56, 2, 100, 0
'1', 2, 35, 56, 1, 100, 0
'1', 2, 35, 56, 5, 100, 0


Что я уже пробовал:

select  max(ticket_type_id),max(category_id),max(request_type_id),max(item_id),max(department_id),max(created_by),max(status_)
from temp2 tbl  ;


используйте max, но это не работает для меня.

1 Ответов

Рейтинг:
1

_Asif_

Ну, эти данные не кажутся логичными, поскольку между подмножествами данных не определена никакая связь. Но в любом случае требуемый результат можно получить, выполнив некоторые изменения в запросе. Попробовать это

DECLARE @TBL TABLE
(
	TEMP_ID INT IDENTITY(1, 10),
	ticket_type_id VARCHAR(10),
	category_id VARCHAR(10),
	request_type_id VARCHAR(10),
	item_id VARCHAR(10),
	department_id VARCHAR(10),
	created_by VARCHAR(10),
	[status] VARCHAR(10)
)

INSERT INTO @TBL (ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, [status])
SELECT '1' as ticket_type_id, NULL as category_id, NULL as request_type_id, NULL as item_id, NULL as department_id, NULL as created_by, NULL as status
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '3', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '4', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '2', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '1', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '5', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'

SELECT A.ID, B.category_id, C.request_type_id, D.item_id, E.department_id, F.created_by, G.[status]
FROM
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, ticket_type_id
	from @TBL
	where ticket_type_id is not null
) A
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, category_id
	from @TBL
	where category_id is not null
) B ON A.ID = B.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, request_type_id
	from @TBL
	where request_type_id is not null
) C ON A.ID = C.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, item_id
	from @TBL
	where item_id is not null
) D ON A.ID = D.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, department_id
	from @TBL
	where department_id is not null
) E ON A.ID = E.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, created_by
	from @TBL
	where created_by is not null
) F ON A.ID = F.ID
INNER JOIN
(
	SELECT  top 5 ROW_NUMBER() OVER (ORDER BY TEMP_ID) ID, status
	from @TBL
	where status is not null
) G ON A.ID = G.ID


Обновление!

версия MySQL

CREATE TABLE TEST
(
	TEMP_ID INT NOT NULL AUTO_INCREMENT,
	ticket_type_id VARCHAR(10),
	category_id VARCHAR(10),
	request_type_id VARCHAR(10),
	item_id VARCHAR(10),
	department_id VARCHAR(10),
	created_by VARCHAR(10),
	status VARCHAR(10),
	PRIMARY KEY (TEMP_ID)
);

INSERT INTO TEST (ticket_type_id, category_id, request_type_id, item_id, department_id, created_by, status)
SELECT '1' as ticket_type_id, NULL as category_id, NULL as request_type_id, NULL as item_id, NULL as department_id, NULL as created_by, NULL as status
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT '1', NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, '2', NULL, NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, '35', NULL, NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, '56', NULL, NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '3', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '4', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '2', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '1', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, '5', NULL, NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, '100', NULL
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '1'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
UNION ALL 
SELECT NULL, NULL, NULL, NULL, NULL, NULL, '0'
;

set @row_number := 0;
set @row_number1 := 0;
set @row_number2 := 0;
set @row_number3 := 0;
set @row_number4 := 0;
set @row_number5 := 0;
set @row_number6 := 0;


SELECT A.ID, B.category_id, C.request_type_id, D.item_id, E.department_id, F.created_by, G.status
FROM
(
	SELECT  (@row_number:=@row_number + 1) as ID, ticket_type_id
	from TEST
	where ticket_type_id is not null
) A
INNER JOIN
(
	SELECT  (@row_number1:=@row_number1 + 1) as ID, category_id
	from TEST
	where category_id is not null
) B ON A.ID = B.ID
INNER JOIN
(
	SELECT  (@row_number2:=@row_number2 + 1) as ID, request_type_id
	from TEST
	where request_type_id is not null
) C ON A.ID = C.ID
INNER JOIN
(
	SELECT  (@row_number3:=@row_number3 + 1) as ID, item_id
	from TEST
	where item_id is not null
) D ON A.ID = D.ID
INNER JOIN
(
	SELECT  (@row_number4:=@row_number4 + 1) as ID, department_id
	from TEST
	where department_id is not null
) E ON A.ID = E.ID
INNER JOIN
(
	SELECT  (@row_number5:=@row_number5 + 1) as ID, created_by
	from TEST
	where created_by is not null
) F ON A.ID = F.ID
INNER JOIN
(
	SELECT  (@row_number6:=@row_number6 + 1) as ID, status
	from TEST
	where status is not null
) G ON A.ID = G.ID

TCS54321

я использую MySQL Workbench. вы предлагаете мне SQL-запрос . Это не работает для mysql. Ну что ж, спасибо вам за ваше решение.

_Asif_

насколько трудно было бы изменить его на mysql?

TCS54321

ROW_NUMBER() не работает в mysql

_Asif_

Добавлена версия MySQL