如何获取具有两种不同值源类型的零件？

发布于 2025-01-10 11:09:11 字数 824 浏览 3 评论 0原文

我在 sql server 2017 上工作，我需要获取具有两种不同源类型的部件 ID 每个相同的部分。

但每个部件有两种不同的源类型

，并且它必须具有等于 8901 的两种源类型中的一种源类型。

示例数据

create table #temp
     (
     partid int,
     sourcetypeid int
     )
     insert into #temp(partid,sourcetypeid)
     values
     (1290,5012),
     (1290,5012),
     (1290,8901),
     (3501,5402),
     (3501,74430),
     (7001,8901),
     (7321,8900),
     (2040,5090),
     (2040,5400),
     (7321,7400),
     (9110,8901),
     (9110,8901)

我尝试的

select partid from #temp
 where sourcetypeid=8901
 group by partid
 having count(distinct sourcetypeid)=2

，但它返回 null

预期结果

有两个不同源的partid类型至少源类型 8901 必须存在

原文

I work on sql server 2017 i need to get part id that have two different source type
per same part .

but part have two different source type per part

and it must have one source type from two source type equal 8901 .

sample data

create table #temp
     (
     partid int,
     sourcetypeid int
     )
     insert into #temp(partid,sourcetypeid)
     values
     (1290,5012),
     (1290,5012),
     (1290,8901),
     (3501,5402),
     (3501,74430),
     (7001,8901),
     (7321,8900),
     (2040,5090),
     (2040,5400),
     (7321,7400),
     (9110,8901),
     (9110,8901)

what i try

select partid from #temp
 where sourcetypeid=8901
 group by partid
 having count(distinct sourcetypeid)=2

but it return null

expected result

partid that have two different source type at least source type 8901 must exist

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅忆 2025-01-17 11:09:11

但它返回 null

当前查询失败，因为 WHERE 子句排除了除 one 之外的所有 sourceTypeId：8901。

PartId | SourceTypeId
-----: | -----------:
  1290 |         8901
  7001 |         8901
  9110 |         8901
  9110 |         8901

因此，当 HAVING 子句搜索具有两 (2) 个 SourceTypeId 值的部分时，找不到匹配项，因为计数始终为 1：

PartId | COUNT(DISTINCT t.SourceTypeId)
-----: | -----------------------------:
  1290 |    1
  7001 |    1
  9110 |    1

相反，请尝试使用 EXISTS 来确保两个 SourceTypeId 之一为 8901：

SELECT t.PartId
FROM   #temp t 
WHERE  EXISTS (
       SELECT 1
       FROM  #temp r
       WHERE r.SourceTypeId = 8901
       AND   r.partId = t.PartId
)
GROUP BY  t.PartId
HAVING COUNT(DISTINCT t.SourceTypeId)=2

结果：

| PartId |
| -----: |
|   1290 |

but it return null

The current query fails because the WHERE clause excludes all sourceTypeId except one: 8901.

PartId | SourceTypeId
-----: | -----------:
  1290 |         8901
  7001 |         8901
  9110 |         8901
  9110 |         8901

So when the HAVING clause searches for parts with two (2) SourceTypeId values, no matches are found, because the count is always 1:

PartId | COUNT(DISTINCT t.SourceTypeId)
-----: | -----------------------------:
  1290 |    1
  7001 |    1
  9110 |    1

Instead, try using EXISTS to ensure that one of two SourceTypeId's is 8901:

SELECT t.PartId
FROM   #temp t 
WHERE  EXISTS (
       SELECT 1
       FROM  #temp r
       WHERE r.SourceTypeId = 8901
       AND   r.partId = t.PartId
)
GROUP BY  t.PartId
HAVING COUNT(DISTINCT t.SourceTypeId)=2

Result: