SQL查询计数有条件

发布于 2025-02-13 20:16:22 字数 926 浏览 0 评论 0原文

我第一次使用SQL查询并学习。我有这样的桌子：

juestname	productCompanyID
2001	ID 1
2001	ID 1
2001	ID 2
2001	ID 1
2001	ID 1 2002 ID 1
2002	ID 1
2002	ID 1
2002 ID 2 2002	ID 2
2002	ID 2
2003	ID 2

，我想计算多少次产品companyid出现，但仅计算一次。（对不起，我的英语不是我的语言，我可能不清楚）

意思是我写了这个SQL：

  SELECT DISTINCT(productcompanyid),
    COUNT(productcompanyid)
  FROM mydatabase
  GROUP BY productcompanyid

这给了我ID 1：6和ID 2：4。

我的意思是，我的拥有ID 1：2（在2001年和2002年至少看来一次）和ID 2：3（由于2001年，2002年和2003年至少看来一次）

感谢您的帮助。

原文

I'm using SQL Queries for the first time and learning it.
I've got a table like this:

yearName	productcompanyID
2001	ID 1
2001	ID 1
2001	ID 2
2001	ID 1
2001	ID 1
2002	ID 1
2002	ID 1
2002	ID 2
2002	ID 2
2003	ID 2

And I would like to count how many times a productcompanyID appear but counting it only once for a year. (Sorry my English is not my language and I might not be clear)

What I mean, for the moment I've writtent this SQL:

  SELECT DISTINCT(productcompanyid),
    COUNT(productcompanyid)
  FROM mydatabase
  GROUP BY productcompanyid

And it gives me the result as ID 1: 6 and ID 2 : 4.

What I would like to have is ID 1 : 2 (as it appears at least once in year 2001 and year 2002) and ID 2: 3 (as it appears at least once in year 2001, year 2002 and year 2003)

Thanks for your help.

分享到QQ

分享到微博

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

发布评论

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

天生の放荡 2025-02-20 20:16:23

实际上，您需要结合数量和截然不同的东西，类似的东西：

select productcompanyID, count(distinct yearName) as distinctYears
from mydatabase
group by productcompanyID

You actually need to combine count and distinct, something like this:

select productcompanyID, count(distinct yearName) as distinctYears
from mydatabase
group by productcompanyID

回复收藏 0 原文

且行且努力 2025-02-20 20:16:23

最简单的查询和一些额外的性能是使用2级聚合，例如：

--create temp table named #table1 and add row1

select 2001 yearName,   'ID 1' productcompanyID into #table1


--add remaining rows to #table1

insert into #table1

select 2001,    'ID 1' union all

select 2001,    'ID 2' union all

select 2001,    'ID 1' union all

select 2001,    'ID 1' union all

select 2002,    'ID 1' union all

select 2002,    'ID 1' union all

select 2002,    'ID 2' union all

select 2002,    'ID 2' union all

select 2003,    'ID 2' 


--/query to count how many times a productcompanyID appear each a year*/


select productcompanyID, count(1) cnt

from (

    select yearName, productcompanyID

    from #table1 

    group by yearName, productcompanyID

)a

group by productcompanyID

它应该给出您对问题的要求。

The simplest query and have some additional performance are using 2 level aggregation, eg:

--create temp table named #table1 and add row1

select 2001 yearName,   'ID 1' productcompanyID into #table1


--add remaining rows to #table1

insert into #table1

select 2001,    'ID 1' union all

select 2001,    'ID 2' union all

select 2001,    'ID 1' union all

select 2001,    'ID 1' union all

select 2002,    'ID 1' union all

select 2002,    'ID 1' union all

select 2002,    'ID 2' union all

select 2002,    'ID 2' union all

select 2003,    'ID 2' 


--/query to count how many times a productcompanyID appear each a year*/


select productcompanyID, count(1) cnt

from (

    select yearName, productcompanyID

    from #table1 

    group by yearName, productcompanyID

)a

group by productcompanyID

It should give the result as what you asking on the question.