如果产品在不同地区的不同时间推出，如何获取最近一个季度推出的产品列表

Question

>我有一个表格

/---------------------------------------
|Region | Product | 1 |  2  |  3  |  4  |
|-------|---------|---|-----|-----|-----|
|   A   |   ABC   | 0 | 120 | 421 | 520 |
|   B   |   ABC   | 0 |   0 |   0 | 670 |
|   C   |   DEF   | 0 |   0 |   0 | 125 |
|   D   |   PQR   | 0 |   0 | 780 | 560 |
|   E   |   PQR   | 0 |   0 |   0 | 340 |
|   F   |   XYZ   | 0 |   0 |   0 | 780 |
|   G   |   XYZ   | 0 |   0 |   0 | 900 |
---------------------------------------/

在此表中，我需要找到第 4 季度推出的产品的名称。

查询应给出的结果是 DEF 和 XYZ

如果有人能帮忙，我将不胜感激

Answer 1

您需要按产品分组，并汇总(求和(每个产品每个季度的值，而不考虑区域：

select
Product
from #table
group by Product
having sum([4]) > 0
and sum([3]) = 0
and sum([2]) = 0
and sum([1]) = 0

用示例数据说明：

create table #table
(
Region varchar(1),
Product varchar(3),
[1] int,
[2] int,
[3] int,
[4] int
)
insert into #table
values 
('A','ABC',0,120,421,520),
('B','ABC',0,0,0,670),
('C','DEF',0,0,0,125),
('D','PQR',0,0,780,560),
('E','PQR',0,0,0,340),
('F','XYZ',0,0,0,780),
('G','XYZ',0,0,0,900)
select
Product
from #table
group by Product
having sum([4]) > 0
and sum([3]) = 0
and sum([2]) = 0
and sum([1]) = 0
drop table #table

输出：

/---------
| Product |
|---------|
|   DEF   |
|   XYZ   |
---------/

Answer 2

试试这个

select * 
from yourTableName a 
where a.field4 > 0 
and a.field3 = 0
and a.field2 = 0
and a.field1 = 0
and a.product not in (select b.product 
from yourTableName b 
where b.field3 >0 
or b.field2>0 
or b.field1>0) 

如果您只想在下面使用产品

select a.product 
from yourTableName a 
where a.field4 > 0 
and a.field3 = 0
and a.field2 = 0
and a.field1 = 0
and a.product not in (select b.product 
from yourTableName b 
where b.field3 >0 
or b.field2>0 
or b.field1>0) 

此处字段 4 作为第 4 季度 字段 3 作为第 3 季度，依此类推。