根据2个字段cosmos数据库的唯一组合选择非唯一记录

Question

我在Cosmos DB容器中有许多记录，其结构如下(示例(：

{
"id": "aaaa",
"itemCode": "1234",
"itemDesc": "TEST",
"otherfileds": ""
}
{
"id": "bbbb",
"itemCode": "1234",
"itemDesc": "TEST2",
"otherfileds": ""
}
{
"id": "cccc",
"itemCode": "5678",
"itemDesc": "HELLO",
"otherfileds": ""
}
{
"id": "dddd",
"itemCode": "5678",
"itemDesc": "HELLO",
"otherfileds": ""
}
{
"id": "eeee",
"itemCode": "9012",
"itemDesc": "WORLD",
"otherfileds": ""
}
{
"id": "ffff",
"itemCode": "9012",
"itemDesc": "WORLD",
"otherfileds": ""
}

现在，我想从中选择项目代码具有非唯一项目描述的记录。基于上面的示例记录，我希望返回项目代码1234，因为它在其他记录中具有不同的项目描述值。

{
"id": "aaaa",
"itemCode": "1234",
"itemDesc": "TEST",
"otherfileds": ""
}
{
"id": "bbbb",
"itemCode": "1234",
"itemDesc": "TEST2",
"otherfileds": ""
}

我尝试过下面的查询，但意识到，它将返回只有相同项目代码和描述的重复条目。

select count(1) from (select distinct value d.itemCode FROM (SELECT
c.itemCode, c.itemDesc, COUNT(1) as dupcount
FROM
c where c.itemCode<>null
GROUP BY
c.itemCode, c.itemDesc) d where d.dupcount>1 )

但我需要找到相同商品代码具有不同商品描述的记录(上面的查询将只返回具有多个商品代码/描述的记录，即商品代码9012和5678(

编辑

我想我成功地形成了查询，通过2个子查询来过滤这些结果(不过我认为这可以改进(。

select e.itemCode from (select  d.itemCode, count(1) as dupcount FROM 
(SELECT
c.itemCode, c.itemDesc
FROM
c where c.itemCode<>null
GROUP BY
c.itemCode, c.itemDesc) d group by d.itemCode )e where e.dupcount>1

Answer 1

我想我成功地形成了查询，通过2个子查询来过滤这些结果(我认为这可以改进(。

select distinct e.itemCode from (select  d.itemCode, count(1) as dupcount FROM 
(SELECT
c.itemCode, c.itemDesc
FROM
c where c.itemCode<>null
GROUP BY
c.itemCode, c.itemDesc) d group by d.itemCode )e where e.dupcount>1