Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Нужна помощь

Такое задание
Даны два параметра:
@1
@2

Первый параметр это количество чисел
Второй параметр это число, которое с помощью сумму количества чисел получить это число
Например
@1 - 2
@2 - 8

Результат:
1 7
2 6
3 5
4 4
5 3
6 2
7 1

Нужно написать это с помощью MySQL. Мысль такая у меня создать таблицу, которая в зависимости от первого параметра создаёт новые столбцы и заносит туда сразу значения, которые будут удовлетворять результату(8).Но как это сделать. Помогитее!
Либо
@1 - 4
@2 - 10

1 2 3 4
2 3 4 1
3 ..
...
7 июн 19, 14:49    [21904581]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
Jonsnow
Нужно написать это с помощью MySQL.
Ну а зачем тогда было в MS SSql тему создавать?

Jonsnow
Мысль такая у меня создать таблицу

Фигня мысль... достаточно обычного CTE.
7 июн 19, 14:58    [21904591]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Akina,

Нужно это написать на microsoft sql server management studio перепутал


Можно по подробнее? Пожалуйста
7 июн 19, 15:15    [21904625]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
Поковыряться действительно оказалось интересно...

set @sum:=10;
set @cnt:=3;

WITH RECURSIVE
cte1 AS (SELECT 1 x
         UNION ALL
         SELECT x+1 FROM cte1 WHERE x < @sum-@cnt+1
		),
cte2 AS (SELECT 1 y
         UNION ALL
         SELECT y+1 FROM cte2 WHERE y < pow(2,@sum-@cnt+1) - 1
		),
cte3 AS (SELECT y
         FROM cte1, cte2
         GROUP BY y
         HAVING @sum = SUM(CASE WHEN y & pow(2, x-1) THEN x ELSE 0 END)
		)
SELECT GROUP_CONCAT(x ORDER BY x)
FROM cte1, cte3
WHERE y & pow(2, x-1)
GROUP BY y
HAVING LENGTH(GROUP_CONCAT(x)) - LENGTH(REPLACE(GROUP_CONCAT(x), ',', '')) = @cnt-1 ;


fiddle

Вот только глубина рекурсии получается офигительная...
7 июн 19, 15:32    [21904635]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
Jonsnow
Нужно это написать на microsoft sql server management studio перепутал

Ааа... ну тады пусть тут остаётся.

Посмотри, разберись, адаптируй, буде не лень. Хотя алгоритм далёк от оптимального.
7 июн 19, 15:34    [21904637]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Akina
Поковыряться действительно оказалось интересно...

set @sum:=10;
set @cnt:=3;

WITH RECURSIVE
cte1 AS (SELECT 1 x
         UNION ALL
         SELECT x+1 FROM cte1 WHERE x < @sum-@cnt+1
		),
cte2 AS (SELECT 1 y
         UNION ALL
         SELECT y+1 FROM cte2 WHERE y < pow(2,@sum-@cnt+1) - 1
		),
cte3 AS (SELECT y
         FROM cte1, cte2
         GROUP BY y
         HAVING @sum = SUM(CASE WHEN y & pow(2, x-1) THEN x ELSE 0 END)
		)
SELECT GROUP_CONCAT(x ORDER BY x)
FROM cte1, cte3
WHERE y & pow(2, x-1)
GROUP BY y
HAVING LENGTH(GROUP_CONCAT(x)) - LENGTH(REPLACE(GROUP_CONCAT(x), ',', '')) = @cnt-1 ;



fiddle

Вот только глубина рекурсии получается офигительная...
Как минимум, в MSSQL нет RECURSIVE
Хотя, было бы очень полезно. Упростило бы мучительное разглядывание CTE в поисках рекурсии...
7 июн 19, 15:47    [21904654]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Кесарь
Member

Откуда:
Сообщений: 453
iap
Как минимум, в MSSQL нет RECURSIVE
Хотя, было бы очень полезно. Упростило бы мучительное разглядывание CTE в поисках рекурсии...


Вот не могу не поддержать!
7 июн 19, 16:22    [21904699]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3440
Jonsnow
Akina,

Нужно это написать на microsoft sql server management studio перепутал


Можно по подробнее? Пожалуйста


скоро иссякнут интересные вопросы
7 июн 19, 16:25    [21904702]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Что делать тогда, если resursive нет? Как быть? Может кто ещё подскажет решение этого задания?
Спасибо, что помогаете, но решение какое т сложноватое на первый взгля:(
7 июн 19, 18:38    [21904862]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Akina,

Что делать тогда, не получается?(RECURSIVE
9 июн 19, 00:35    [21905276]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Jonsnow
Что делать тогда, если resursive нет? Как быть? Может кто ещё подскажет решение этого задания?
Спасибо, что помогаете, но решение какое т сложноватое на первый взгля:(
С RECURSIVE всё просто - в MSSQL просто его не писать!

В скрипте Akina есть и другие неточности - например, в MSSQL нет функции LENGTH.
Зато есть LEN() и DATALENGTH().
Найдите в микрософтовсом хэлпе. И про рекурсивное CTE - там же.
9 июн 19, 15:11    [21905444]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
iap,

Спасибо большое, сейчас буду пробовать это как-то сделать, а таблицы же создавать тоже нужно ?

Может знаете оптимальное решение какое-нибудь?
9 июн 19, 18:50    [21905494]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Jonsnow,

Ничего не работает(
9 июн 19, 19:02    [21905495]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
DECLARE  @sum int 
        ,@cnt int
set @sum=10;
set @cnt=3;
WITH 
cte1 AS (SELECT 1 x
         UNION ALL
         SELECT x+1 FROM cte1 WHERE x < @sum-@cnt+1
		),
cte2 AS (SELECT 1 y
         UNION ALL
         SELECT y+1 FROM cte2 WHERE y < power(2,@sum-@cnt+1) - 1
		),
cte3 AS (SELECT y
         FROM cte1, cte2
         GROUP BY y
         HAVING @sum = SUM(CASE WHEN y & power(2,x-1) THEN x ELSE 0 END)
		)
SELECT GROUP_CONCAT(x ORDER BY x)
FROM cte1, cte3
WHERE y & pow(2, x-1)
GROUP BY y
HAVING LEN(GROUP_CONCAT(x)) - LEN(REPLACE(GROUP_CONCAT(x), ',', '')) = @cnt-1 ;
Jonsnow,


Msg 4145, Level 15, State 1, Line 82
Рядом с "THEN" в контексте, где ожидается условие, указано выражение типа, отличного от логического.
Msg 156, Level 15, State 1, Line 84
Неправильный синтаксис около ключевого слова "ORDER".
9 июн 19, 20:34    [21905517]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Jonsnow,
ВОТ МОЁ Решение, но оно не практично, так как только для одного случая работает
CREATE DATABASE zadanie
GO 
USE zadanie
CREATE TABLE sum_cyfr(		
id int not null identity,
par1 int not null,
par2 int not null,
res1 int ,
res2 int ,
)
SELECT * FROM sum_cyfr

CREATE PROCEDURE suma
@par1 int,
@par2 int,
@res1 int,
@res2 int
AS 
BEGIN 
	INSERT INTO sum_cyfr([par1],[par2],[res1],[res2])
	VALUES (@par1,@par2,@res1,@res2);
END
EXECUTE suma 2,8,1,7 
EXECUTE suma 2,8,2,6 
EXECUTE suma 2,8,1,7 
EXECUTE suma 2,8,1,10 
SELECT s.res1+s.res2 as suma FROM sum_cyfr s
WHERE s.res1+s.res2 = s.par2
GROUP BY s.res1,s.res2


id	par1	par2	res1	res2
1	2	8	1	7
2	2	8	1	7
3	2	8	1	10
4	2	8	2	6


Результат
suma
8
8
9 июн 19, 21:52    [21905545]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3440
Jonsnow
Jonsnow,
ВОТ МОЁ Решение, но оно не практично, так как только для одного случая работает


да, не то слово.
А на кого вы собрались выучиться если не секрет?
10 июн 19, 09:10    [21905667]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20181
iap
Как минимум, в MSSQL нет RECURSIVE

Кесарь
Вот не могу не поддержать!

iap
В скрипте Akina есть и другие неточности - например, в MSSQL нет функции LENGTH.
Зато есть LEN() и DATALENGTH().

Други мои! Товарищ изначально написал, что MySQL. Я делал именно под MySQL - в надежде, что тема будет перенесена. То, что ему таки нужен MS SQL, я увидел лишь после публикации решения для MySQL (что видно по следующему после решения посту, кстати). Что ж вы так прям на меня все набросились-то? Ей-ей, не со зла!

Тем более что вся адаптация и сводится к удалению лишнего с точки зрения SQL Server слова да замене имён функций... ерунда же ж. На алгоритм (и на его "вымороченность", впрочем) это никак не влияет.
10 июн 19, 09:29    [21905686]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Jonsnow
Member

Откуда:
Сообщений: 54
Akina,

Спасибо большое, вот только когда переделываю до mssql, но высверливаются такие ошибки как
Msg 4145, Level 15, State 1, Line 82
Рядом с "THEN" в контексте, где ожидается условие, указано выражение типа, отличного от логического.
Msg 156, Level 15, State 1, Line 84
Неправильный синтаксис около ключевого слова "ORDER".

Очень благодарен
10 июн 19, 09:40    [21905702]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
lion2006
Member

Откуда:
Сообщений: 1
Jonsnow,

Вот через таблицу решение в "лоб". Чем больше кол-во чисел тем дольше обработка.

declare @icount int, @isum int
declare @step int, @massbegin int, @massend int
declare @str nvarchar(max), @ch nvarchar(max), @pos int, @length int
declare @sql nvarchar(MAX), @colname nvarchar(20), @mass_col nvarchar(max), @mass_sum nvarchar(max)

Set @icount=4
Set @isum=10
Set @step=1
SET @massbegin=1
SET @mass_col=''
SET @mass_sum=''

if (not object_id('tempdb..#tab1') IS NULL)
drop table #tab1

Create table #tab1 (mass_sum int)

While @step<@icount
begin
SET @massbegin=@massbegin*10
SET @massbegin=@massbegin+1
SET @step=@step+1
end
SET @massend=@massbegin*9
Set @step=1

While @step<=@icount
begin
SET @colname='col'+CONVERT(nvarchar(17),@step)
SET @sql = 'Alter Table #tab1 ADD '+@colname+' int'
EXEC (@SQL)
SET @step = @step+1
SET @mass_col=@mass_col+@colname+','
SET @mass_sum=@mass_sum+@colname+'+'
end

SET @mass_col = LEFT(@mass_col,LEN(@mass_col)-1)
SET @mass_sum = LEFT(@mass_sum,LEN(@mass_sum)-1)

While @massbegin<=@massend
begin
SET @str = CONVERT(nvarchar(max),@massbegin)
IF PATINDEX('%0%',@str)=0
begin
SET @length=LEN(@str)
SET @pos=@length
SET @step=1
While @step<@icount
begin
SET @str = STUFF(@str,@pos,0,',')
SET @pos=@pos-1
SET @step=@step+1
end
SET @sql = 'INSERT INTO #tab1(mass_sum,'+@mass_col+') VALUES('+CONVERT(char,@isum)+','+@str+')'
EXEC(@sql)
end
SET @massbegin=@massbegin+1
end

SET @sql='Select * FROM #tab1 WHERE '+@mass_sum+'=mass_sum'
EXEC(@sql)

DROP table #tab1
10 июн 19, 10:13    [21905719]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36687
Модератор: Так что именно надо: MSSQL или MySQL?


Сообщение было отредактировано: 10 июн 19, 10:29
10 июн 19, 10:28    [21905733]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ну если на входе просто числа 0-9 то это просто

DECLARE 
	@a INT = 2,
	@B INT = 8

;WITH T AS
(
	SELECT A.n + 10*B.n + 100*C.n + 1000*D.n as Value
	FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) D(n)
	WHERE A.n + 10*B.n + 100*C.n + 1000*D.n <= POWER(10,@a)
	
), D AS
(
	SELECT 
	Value,
	   S = 
		V.Value % POWER(10, 1) / POWER(10, 0)
		+ V.Value % POWER(10, 2) / POWER(10, 1)
		+ V.Value % POWER(10, 3) / POWER(10, 2)
		+ V.Value % POWER(10, 4) / POWER(10, 3)
		+ V.Value % POWER(10, 5) / POWER(10, 4)
		+ V.Value % POWER(10, 6) / POWER(10, 5)
		+ V.Value % POWER(10, 7) / POWER(10, 6)
		+ V.Value % POWER(10, 8) / POWER(10, 7)
	FROM T v
) 
SELECT 
	RIGHT('0000000000000000000000'+CAST(Value AS VARCHAR(3)),@a)
FROM D 
WHERE S = @B
10 июн 19, 10:38    [21905744]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
С дубликатами и разделением не заморачивался, для студента и так сойдет.
DECLARE @sum INT = 10
DECLARE @count INT = 4
;
DECLARE @numbers TABLE ( [n] INT )
;
WITH
n AS (
  SELECT 
    [n] = 1
  UNION ALL
  SELECT
    [n] = [n] + 1
  FROM
    n
  WHERE
    [n] < @sum - @count
)
INSERT
INTO
  @numbers
SELECT
  *
FROM
  n
;
WITH
c AS (
  SELECT
    [step]  = 1,
    [path]  = CONVERT( VARCHAR(MAX), '_' + CONVERT( VARCHAR(10), n.[n] ) ),
    [sum]   = n.[n]
  FROM
    @numbers n
  UNION ALL
  SELECT
    [step]  = c.[step] + 1,
    [path]  = c.[path] + '_' + CONVERT( VARCHAR(10), n.[n] ),
    [sum]   = c.[sum] + n.[n]
  FROM
    c
    CROSS APPLY @numbers n
  WHERE
    c.[step] < @count
)
SELECT
  *
FROM
  c
WHERE
      [step] = @count
  AND [sum] = @sum
;
10 июн 19, 10:44    [21905749]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
TaPaK,

или проще, можно добавить что бы не все разряды учитывало, но мне лень

DECLARE 
	@a INT = 4,
	@B INT = 19

;WITH T AS
(
	SELECT 
		Value  = A.n + 10*B.n + POWER(10,2)*C.n + POWER(10,3)*D.n + POWER(10,4)*E.N + POWER(10,5)*F.N
				+ POWER(10,6)*G.N + POWER(10,7)*H.N,	 
		S	= A.n + B.n + C.n + D.n + E.N + F.N + G.N  + H.N
	FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) D(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) E(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) F(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) G(n),
	     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) H(n)
	WHERE 	
		A.n + 10*B.n + POWER(10,2)*C.n + POWER(10,3)*D.n + POWER(10,4)*E.N + POWER(10,5)*F.N
		+ POWER(10,6)*G.N + POWER(10,7)*H.N <= POWER(10,@a)
) 
SELECT 
	RIGHT(REPLICATE('0',@a)+CAST(Value AS VARCHAR(255)),@a)
FROM T
WHERE S = @B
10 июн 19, 11:15    [21905771]     Ответить | Цитировать Сообщить модератору
 Re: Интересное задание по SQL  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3440
Jonsnow
Что делать тогда, если resursive нет? Как быть? Может кто ещё подскажет решение этого задания?
Спасибо, что помогаете, но решение какое т сложноватое на первый взгля:(


Один из путей такой: написать в поддержку SQL Server и указать на этот недостаток, они могут даже быстро исправить.
Важно указать, что, мол, очень важная возможность для запросов.
10 июн 19, 14:13    [21905938]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить