Obtener el valor MÁXIMO de una columna de BITS
Tengo una solicitud de SELECCIÓN con 'unión interna' en la tabla unida es una columna con tipo de bit.
Quiero seleccionar 1 si en la tabla unida hay como máximo un valor con 1. Si no es el caso, el valor será 0.
Así que si tengo:
PERSID | NAME
1 | Toto
2 | Titi
3 | Tata
Y la segunda tabla
PERSID | BOOL
1 | 0
1 | 0
2 | 0
2 | 1
Me gustaría tener como resultado
Toto -> 0
Titi -> 1
Tata -> 0
Intento esto:
SELECT
sur.*
,MAX(bo.BOOL)
FROM SURNAME sur
INNER JOIN BOOL bo
ON bo.IDPERS = sur.IDPERS
Pero MAX no está disponible en la columna BIT.. Entonces, ¿cómo puedo hacer eso?
Gracias,
35
5 answers
Puede lanzarlo a un INT
, e incluso lanzarlo de nuevo a un BIT
si necesita
SELECT
sur.*
,CAST(MAX(CAST(bo.BOOL as INT)) AS BIT)
FROM SURNAME sur
INNER JOIN BOOL bo
ON bo.IDPERS = sur.IDPERS
58
Author: kenwarner,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:37:01
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:37:01
Intenta:
max(cast(bo.BOOL as int))
9
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:36:24
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:36:24
Unidireccional
SELECT
sur.*
,MAX(convert(tinyint,bo.BOOL))
FROM SURNAME sur
INNER JOIN BOOL bo
ON bo.IDPERS = sur.IDPERS
5
Author: SQLMenace,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:37:44
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 17:37:44
Si quieres solo aquellas personas con exactamente un bit de conjunto:
declare @Surname as Table ( PersId Int, Name VarChar(10) )
insert into @Surname ( PersId, Name ) values
( 1, 'Toto' ), ( 2, 'Titi' ), ( 3, 'Tata' ), ( 4, 'Tutu' )
declare @Bool as Table ( PersId Int, Bool Bit )
insert into @Bool ( PersId, Bool ) values
( 1, 0 ), ( 1, 0 ),
( 2, 0 ), ( 2, 1 ),
( 4, 1 ), ( 4, 0 ), ( 4, 1 )
select Sur.PersId, Sur.Name, Sum( Cast( Bo.Bool as Int ) ) as [Sum],
case Sum( Cast( Bo.Bool as Int ) )
when 1 then 1
else 0
end as [Only One]
from @Surname as Sur left outer join
@Bool as Bo on Bo.PersId = Sur.PersId
group by Sur.PersId, Sur.Name
order by Sur.Name
1
Author: HABO,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 18:13:11
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-29 18:13:11
Puedes evitar el doble reparto desordenado forzando un reparto implícito:
SELECT
sur.*
,CAST(MAX(1 * bo.BOOL) AS BIT)
FROM SURNAME sur
INNER JOIN BOOL bo
ON bo.IDPERS = sur.IDPERS
0
Author: Stuart Steedman,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-10-04 12:37:40
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-10-04 12:37:40