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

Откуда:
Сообщений: 70
Добрый день,

Есть следующая табличка (результат запроса).

A1 A2 A3 A4 A5 A6 A7 A8 A9
74 0 0 1 0 39 0 0 0
74 0 0 1 0 63 1 0 0
74 0 0 1 0 0 1834 0 0
74 0 0 1 0 0 0 706 0
74 0 0 1 0 0 0 405 0
74 0 0 1 0 0 2405 0 0
74 0 0 1 0 0 2626 0 0
74 0 0 1 0 0 2543 0 0
74 0 0 1 0 41 14 0 0
74 0 0 1 0 0 2664 0 0
74 0 0 1 0 0 1790 0 0
74 0 0 1 0 0 1736 0 0
74 0 0 1 0 0 0 723 0
74 0 0 1 0 0 0 724 0
74 0 0 1 0 0 0 753 0
74 0 0 1 0 0 0 792 0
74 0 0 1 0 63 0 0 0


Основываясь на этих значениях, необходимо найти наибольшую одинаковую во всех строках "голову" (набор столбцов А1,А2,А3,... по порядку), а остальные столбцы забить нулями. Так, для приведенной таблицы результат должен быть следующим.

A1 A2 A3 A4 A5 A6 A7 A8 A9
74 0 0 1 0 0 0 0 0
13 фев 14, 13:17    [15562424]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
Добрый Э - Эх
Guest
версия сервера какая?
13 фев 14, 13:31    [15562540]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
PM2010
Member

Откуда:
Сообщений: 70
Добрый Э - Эх,

2008R2
13 фев 14, 13:36    [15562578]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
Добрый Э - Эх
Guest
как-то так попробуй:
+ <-- километровая портянка
with t (A1,	A2,	A3,	A4,	A5,	A6,	A7,	A8,	A9) as 
(
select *
  from (
      values
       (74,	0,	0,	1,	0,	39,	0,	0,	0),
(74,	0,	0,	1,	0,	63,	1,	0,	0),
(74,	0,	0,	1,	0,	0,	1834,	0,	0),
(74,	0,	0,	1,	0,	0,	0,	  706,	0),
(74,	0,	0,	1,	0,	0,	0,	405,	0),
(74,	0,	0,	1,	0,	0,	2405,	0,	0),
(74,	0,	0,	1,	0,	0,	2626,	0,	0),
(74,	0,	0,	1,	0,	0,	2543,	0,	0),
(74,	0,	0,	1,	0,	41,	14,	0,	0),
(74,	0,	0,	1,	0,	0,	2664	,0,	0),
(74,	0,	0,	1,	0,	0,	1790	,0,	0),
(74,	0,	0,	1,	0,	0,	1736,	0,	0),
(74,	0,	0,	1,	0,	0,	0,	723,	0),
(74,	0,	0,	1,	0,	0,	0,	724,	0),
(74,	0,	0,	1,	0,	0,	0,	753,	0),
(74,	0,	0,	1,	0,	0,	0,	792,	0),
(74,	0,	0,	1,	0,	63,	0,	0,	0)
) v(q,w,e,r,t,y,u,i,o)
)

select 
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a1 as varchar), 15) 
               + cast(a1 as varchar)
             )
            
          ,16,100
       ) as a1,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a2 as varchar), 15) 
               + cast(a2 as varchar)
             )
            
          ,16,100
       ) as a2,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a3 as varchar), 15) 
               + cast(a3 as varchar)
             )
            
          ,16,100
       ) as a3,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a4 as varchar), 15) 
               + cast(a4 as varchar)
             )
            
          ,16,100
       ) as a4,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a5 as varchar), 15) 
               + cast(a5 as varchar)
             )
            
          ,16,100
       ) as a5,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a6 as varchar), 15) 
               + cast(a6 as varchar)
             )
            
          ,16,100
       ) as a6,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a7 as varchar), 15) 
               + cast(a7 as varchar)
             )
            
          ,16,100
       ) as a7,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a8 as varchar), 15) 
               + cast(a8 as varchar)
             )
            
          ,16,100
       ) as a8,
 substring
       (  max(   right(replicate('0', 15) + cast(cnt_a9 as varchar), 15) 
               + cast(a9 as varchar)
             )
            
          ,16,100
       ) as a9

  from (
select t.*, 
       count(1) over(partition by a1) as cnt_a1,
       count(1) over(partition by a2) as cnt_a2,
       count(1) over(partition by a3) as cnt_a3,
       count(1) over(partition by a4) as cnt_a4,
       count(1) over(partition by a5) as cnt_a5,
       count(1) over(partition by a6) as cnt_a6,
       count(1) over(partition by a7) as cnt_a7,
       count(1) over(partition by a8) as cnt_a8,
       count(1) over(partition by a9) as cnt_a9
  from t) v
on-line проверка на sqlfiddle.com
13 фев 14, 13:58    [15562754]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
with t (A1,	A2,	A3,	A4,	A5,	A6,	A7,	A8,	A9) as 
(
select *
  from (
      values
       (74,	0,	0,	1,	0,	39,	0,	0,	0),
(74,	0,	0,	1,	0,	63,	1,	0,	0),
(74,	0,	0,	1,	0,	0,	1834,	0,	0),
(74,	0,	0,	1,	0,	0,	0,	  706,	0),
(74,	0,	0,	1,	0,	0,	0,	405,	0),
(74,	0,	0,	1,	0,	0,	2405,	0,	0),
(74,	0,	0,	1,	0,	0,	2626,	0,	0),
(74,	0,	0,	1,	0,	0,	2543,	0,	0),
(74,	0,	0,	1,	0,	41,	14,	0,	0),
(74,	0,	0,	1,	0,	0,	2664	,0,	0),
(74,	0,	0,	1,	0,	0,	1790	,0,	0),
(74,	0,	0,	1,	0,	0,	1736,	0,	0),
(74,	0,	0,	1,	0,	0,	0,	723,	0),
(74,	0,	0,	1,	0,	0,	0,	724,	0),
(74,	0,	0,	1,	0,	0,	0,	753,	0),
(74,	0,	0,	1,	0,	0,	0,	792,	0),
(74,	0,	0,	1,	0,	63,	0,	0,	0)
) v(q,w,e,r,t,y,u,i,o)
)
select
(select top 1 A1 from t GROUP BY A1 order by COUNT(*) desc) as A1
,(select top 1 A2 from t GROUP BY A2 order by COUNT(*) desc) as A2
,(select top 1 A3 from t GROUP BY A3 order by COUNT(*) desc) as A3
,(select top 1 A4 from t GROUP BY A4 order by COUNT(*) desc) as A4
,(select top 1 A5 from t GROUP BY A5 order by COUNT(*) desc) as A5
,(select top 1 A6 from t GROUP BY A6 order by COUNT(*) desc) as A6
,(select top 1 A7 from t GROUP BY A7 order by COUNT(*) desc) as A7
,(select top 1 A8 from t GROUP BY A8 order by COUNT(*) desc) as A8
,(select top 1 A9 from t GROUP BY A9 order by COUNT(*) desc) as A9
13 фев 14, 14:37    [15563013]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
select q, w, e, r, t, y, u, i, o
from (
   select 
      min(q)qmin,max(q)qmax, 
      min(w)wmin,max(w)wmax, 
      min(e)emin,max(e)emax, 
      min(r)rmin,max(r)rmax, 
      min(t)tmin,max(t)tmax, 
      min(y)ymin,max(y)ymax, 
      min(u)umin,max(u)umax, 
      min(i)imin,max(i)imax, 
      min(o)omin,max(o)omax
   from (values
      (74,	0,	0,	1,	0,	39,	0,	0,	0),
      (74,	0,	0,	1,	0,	63,	1,	0,	0),
      (74,	0,	0,	1,	0,	0,	1834,	0,	0),
      (74,	0,	0,	1,	0,	0,	0,	  706,	0),
      (74,	0,	0,	1,	0,	0,	0,	405,	0),
      (74,	0,	0,	1,	0,	0,	2405,	0,	0),
      (74,	0,	0,	1,	0,	0,	2626,	0,	0),
      (74,	0,	0,	1,	0,	0,	2543,	0,	0),
      (74,	0,	0,	1,	0,	41,	14,	0,	0),
      (74,	0,	0,	1,	0,	0,	2664	,0,	0),
      (74,	0,	0,	1,	0,	0,	1790	,0,	0),
      (74,	0,	0,	1,	0,	0,	1736,	0,	0),
      (74,	0,	0,	1,	0,	0,	0,	723,	0),
      (74,	0,	0,	1,	0,	0,	0,	724,	0),
      (74,	0,	0,	1,	0,	0,	0,	753,	0),
      (74,	0,	0,	1,	0,	0,	0,	792,	0),
      (74,	0,	0,	1,	0,	63,	0,	0,	0)
      ) v(q,w,e,r,t,y,u,i,o)
   )g
cross apply(select case when qmin=qmax then      qmin end q,case when qmin=qmax then      0 end z)fq
cross apply(select case when wmin=wmax then fq.z+wmin end w,case when wmin=wmax then fq.z+0 end z)fw
cross apply(select case when emin=emax then fw.z+emin end e,case when emin=emax then fw.z+0 end z)fe
cross apply(select case when rmin=rmax then fe.z+rmin end r,case when rmin=rmax then fe.z+0 end z)fr
cross apply(select case when tmin=tmax then fr.z+tmin end t,case when tmin=tmax then fr.z+0 end z)ft
cross apply(select case when ymin=ymax then ft.z+ymin end y,case when ymin=ymax then ft.z+0 end z)fy
cross apply(select case when umin=umax then fy.z+umin end u,case when umin=umax then fy.z+0 end z)fu
cross apply(select case when imin=imax then fu.z+imin end i,case when imin=imax then fu.z+0 end z)fi
cross apply(select case when omin=omax then fi.z+omin end o,case when omin=omax then fi.z+0 end z)fo
qwertyuio
740010NULLNULLNULLNULL
13 фев 14, 15:42    [15563489]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
упрощение формул:
cross apply(select case when qmin=qmax then qmin              end q)fq,
cross apply(select case when wmin=wmax then wmin+0/(abs(q)+1) end w)fw,
cross apply(select case when emin=wmax then emin+0/(abs(w)+1) end e)fe,
cross apply(select case when rmin=wmax then rmin+0/(abs(e)+1) end r)fr,
cross apply(select case when tmin=wmax then tmin+0/(abs(r)+1) end t)ft,
cross apply(select case when ymin=wmax then ymin+0/(abs(t)+1) end y)fy,
cross apply(select case when umin=wmax then umin+0/(abs(y)+1) end u)fu,
cross apply(select case when imin=wmax then imin+0/(abs(u)+1) end i)fi,
cross apply(select case when omin=wmax then omin+0/(abs(i)+1) end o)fo 
13 фев 14, 15:56    [15563609]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
PM2010
Member

Откуда:
Сообщений: 70
LexusR, Ваше решение дает неверный результат, если, например, все нули в столбце А7 заменить на 1, кроме последней строки. Естественно, интересует более общее решение, чем просто для приведенной в первом посте таблицы.

Cygapb-007,
SELECT 
	CASE (MAX(A1) - MIN(A1)) WHEN 0 THEN MAX(A1) ELSE 0 END AS A1,
	CASE (MAX(A2) - MIN(A2)) WHEN 0 THEN MAX(A2) ELSE 0 END AS A2,
	CASE (MAX(A3) - MIN(A3)) WHEN 0 THEN MAX(A3) ELSE 0 END AS A3,
	CASE (MAX(A4) - MIN(A4)) WHEN 0 THEN MAX(A4) ELSE 0 END AS A4,
	CASE (MAX(A5) - MIN(A5)) WHEN 0 THEN MAX(A5) ELSE 0 END AS A5,
	CASE (MAX(A6) - MIN(A6)) WHEN 0 THEN MAX(A6) ELSE 0 END AS A6,
	CASE (MAX(A7) - MIN(A7)) WHEN 0 THEN MAX(A7) ELSE 0 END AS A7,
	CASE (MAX(A8) - MIN(A8)) WHEN 0 THEN MAX(A8) ELSE 0 END AS A8,
	CASE (MAX(A9) - MIN(A9)) WHEN 0 THEN MAX(A9) ELSE 0 END AS A9
13 фев 14, 16:11    [15563682]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
Cygapb-007
Member

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

неверно, например, для
1234
1234
1204
13 фев 14, 16:17    [15563718]     Ответить | Цитировать Сообщить модератору
 Re: Поиск наиболее общей части с "головы"  [new]
PM2010
Member

Откуда:
Сообщений: 70
Cygapb-007,
Да, вы правы. Моё решение верно, только если ввести условие, что если есть последовательность вида abcd, то обязательно существует и последовательности вида abc0, ab00, a000 и a, b, c, d - натуральные.
13 фев 14, 16:24    [15563762]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить