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

Откуда: AB
Сообщений: 7378
Нужно только оставить записи с максимально возможным набором полей.

WITH example_cte AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3
) 
SELECT * FROM example_cte

WBS1 WBS2 WB3

1 1
2
3 3 3

SQL 2008
19 авг 14, 00:41    [16459776]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
_human
Member

Откуда:
Сообщений: 560
Relic Hunter,
Relic Hunter
Нужно только оставить записи с максимально возможным набором полей.

собсна при такой формулировке.. 100% правильное решение будет select * from {..}

WITH example_cte(a,b,c) AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3
) 
, anal as (
	SELECT *
	, case when len(a) <> 0 then 1 else 0 end as aa
	, case when len(b) <> 0 then 1 else 0 end as bb
	, case when len(c) <> 0 then 1 else 0 end as cc

	, case when len(a) <> 0 then 1 else 0 end 
	+ case when len(b) <> 0 then 1 else 0 end 
	+ case when len(c) <> 0 then 1 else 0 end abc
FROM example_cte c )

select 
	a, b, c
from
	(
		select *
			, dense_rank() over(partition by a order by abc desc) as dr
		from anal
	) as q
where q.dr = 1
19 авг 14, 02:51    [16459891]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Mairos
Member

Откуда:
Сообщений: 555
_human, можно ещё так
WITH example_cte(a,b,c) AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3
), cte as (select a,b,c,convert(int,isnull(a,'0'))+ convert(int,isnull(b,'0'))+ convert(int,isnull(c,'0')) abc from example_cte c)
  select a,b,c 
    from(select *,rank() over(partition by a order by abc desc) as d from cte) as q 
    where q.d = 1
19 авг 14, 11:23    [16460986]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Mairos
можно ещё так
А если в данных будет '0', тогда как?
А если в данных будет '1', '123', '', '' и '1', '1', '2', '3', тогда как?
А если какое-то значение не конвертируется в int, тогда как?
19 авг 14, 11:49    [16461238]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
WITH example_cte(a,b,c) AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3
),
x as
(
 select
  e.*, row_number() over (partition by e.a order by b.cnt desc) as rn
 from
  example_cte e cross apply
  (select count(v) from (values (nullif(e.b, '')), (nullif(e.c, ''))) a(v)) b(cnt)
)
select
 a, b, c
from
 x
where
 rn = 1;
19 авг 14, 12:01    [16461374]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7378
invm,

Наверное не совсем правильно объяснил

WITH example_cte(a,b,c) AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '4' AS WBS3
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '5' AS WBS3
),
x as
(
 select
  e.*, row_number() over (partition by e.a order by b.cnt desc) as rn
 from
  example_cte e cross apply
  (select count(v) from (values (nullif(e.b, '')), (nullif(e.c, ''))) a(v)) b(cnt)
)
select
 a, b, c
from
 x
where
 rn = 1;

На выхлопе должно получится

WBS1 WBS2 WBS3
1         1     
2
3         3         3
3         3         4
3         3         5

Короче, не работает :)
19 авг 14, 20:11    [16464802]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Relic Hunter
Короче, не работает :)
Замените row_number на dense_rank.
19 авг 14, 20:26    [16464878]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Или на rank.
19 авг 14, 20:28    [16464890]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7378
_human,

BINGO!

Thank you.
19 авг 14, 20:31    [16464900]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7378
В продолжение темы. Существует-ли способ объединить строки по принципу узлов в дереве

+
WITH example_cte(a,b,c,d) AS
( 
  SELECT '1' AS WBS1, '' AS WBS2, '' AS WBS3, 'DESCRIPTION 1' As Descr
  UNION ALL
  SELECT '1' AS WBS1, '1' AS WBS2, '' AS WBS3, 'DESCRIPTION 1/2' As Descr
  UNION ALL
  SELECT '2' AS WBS1, '' AS WBS2, '' AS WBS3, 'DESCRIPTION 2' As Descr
  UNION ALL
  SELECT '3' AS WBS1, '' AS WBS2, '' AS WBS3, 'DESCRIPTION 3/1' As Descr
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '' AS WBS3, 'DESCRIPTION 3/2' As Descr
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '3' AS WBS3, 'DESCRIPTION 3/3' As Descr
  UNION ALL
  SELECT '3' AS WBS1, '3' AS WBS2, '4' AS WBS3, 'DESCRIPTION 3/4' As Descr
) 
Select 
  case
    when T1.a<>'' and T1.b='' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c<>'' then T1.a+T1.b
  end	
, Stuff(
        (
        Select ' | ' + T2.d  
        From example_cte As T2
        Where 
  case
    when T1.a<>'' and T1.b='' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c<>'' then T1.a+T1.b
  end	
  =
  case
    when T2.a<>'' and T2.b='' and T2.c='' then T2.a
	when T2.a<>'' and T2.b<>'' and T2.c='' then T2.a
	when T2.a<>'' and T2.b<>'' and T2.c<>'' then T2.a+T2.b
  end	

        For Xml Path(''), type 
        ).value('.', 'nvarchar(max)'), 1, 2, '') as descr 
From example_cte As T1
group by
  case
    when T1.a<>'' and T1.b='' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c='' then T1.a
	when T1.a<>'' and T1.b<>'' and T1.c<>'' then T1.a+T1.b
  end	


В выхлопе должно быть

A    B    C  Description

A A DESCRIPTION 1/1 | DESCRIPTION 1/2
B DESCRIPTION 2
C C D DESCRIPTION 3/1 | DESCRIPTION 3/2 | DESCRIPTION 3/3
C C E DESCRIPTION 3/1 | DESCRIPTION 3/2 | DESCRIPTION 3/4
20 авг 14, 22:21    [16471068]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить