c# - sql查询根据条件对一列中的相同值进行分组

Question

基于这张表

Field1 field2 field3
10     x      10.x.value
10     y      10.x.valua
11     x      11.x.value
11     y      11.x.value

对于每个 Field1(10 和 11)...检查 x 和 y 值(field3)。如果连 1 都失败，则 field1 没有用

我明白了

Field   index1       index2 
10      10.x.value   10.y.value  
11      11.x.value   11.y.value

到目前为止我有这个sql

select distinct a.id as 'Id Arquivo', b.idIndice, b.valor from tgpwebged.dbo.sistema_Documentos as a
join tgpwebged.dbo.sistema_Indexacao as b on a.id = b.idDocumento
join tgpwebged.dbo.sistema_DocType as c on a.idDocType = c.id
join tgpwebged.dbo.sistema_DocType_Index as d on c.id = d.docTypeId
where d.docTypeId = 40 and (b.idIndice = 11 AND b.valor = '11111111' OR b.idIndice = 12 AND b.valor = '11111' )

编辑:

这是我的结果集

id  idIndice    valor
13  11          11111111
13  12          11111
14  11          11111111
14  12          11111
16  12          11111

索引 16 没有 idIndice 11 = '11111111'所以不应该显示

Answer 1

编辑 #1:根据您的编辑，您可以使用如下内容:

select a.id as 'Id Arquivo', 
    b.idIndice, 
    b.valor
from tgpwebged.dbo.sistema_Documentos as a
join tgpwebged.dbo.sistema_Indexacao as b 
    on a.id = b.idDocumento
join tgpwebged.dbo.sistema_DocType as c 
    on a.idDocType = c.id
join tgpwebged.dbo.sistema_DocType_Index as d 
    on c.id = d.docTypeId
where d.docTypeId = 40 
    and (b.idIndice = 11 AND b.valor = '11111111' 
         OR b.idIndice = 12 AND b.valor = '11111')
group by a.id, b.idIndice, b.valor
having count(distinct b.idIndice) = 2

虽然还不清楚您要做什么。看起来你想要 PIVOT类似这样的数据:

select *
from
(
  select distinct a.id as 'Id Arquivo', 
    --b.idIndice, 
    b.valor,
    'index'+cast(row_number() over(partition by a.id order by b.idIndice) as varchar(10)) rn
  from tgpwebged.dbo.sistema_Documentos as a
  join tgpwebged.dbo.sistema_Indexacao as b 
    on a.id = b.idDocumento
  join tgpwebged.dbo.sistema_DocType as c 
    on a.idDocType = c.id
  join tgpwebged.dbo.sistema_DocType_Index as d 
    on c.id = d.docTypeId
  where d.docTypeId = 40 
    and (b.idIndice = 11 AND b.valor = '11111111' 
         OR b.idIndice = 12 AND b.valor = '11111')
) src
pivot
(
  max(valor)
  for rn in (Index1, Index2)
) piv

<罢工>