从所有组中返回1行

Question

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=9e6f83edf836f4496afb509eb9411d4a

编辑后包含sql代码：

CREATE TABLE TMP_PRODUCTS (STORE INT, UPC INT, PROMOCODE CHAR(3), FORSALE CHAR(1))
INSERT INTO TMP_PRODUCTS VALUES
(100,1,'123','Y'),
(100,2,'123','Y'),
(100,3,'123','N'),
(100,4,'124','Y'),
(100,5,'124','N'),
(100,6,'124','N'),
(100,7,'125','N'),
(100,8,'125','N'),
(100,9,'125','N');
SELECT
STORE,
UPC,
PROMOCODE,
DENSE_RANK() OVER (PARTITION BY STORE ORDER BY PROMOCODE) AS 'GroupCode'
FROM
TMP_PRODUCTS
WHERE
FORSALE = 'Y'

我需要返回PROMOCODE所有组中FORSALE='Y'的所有行，以及FORSALE=`N'的所有组中至少一行。在这个例子中，组125中的所有产品都是FORSALE='N'，但我至少需要一行才能返回。这是我目前得到的输出：

STORE   UPC     PROMOCODE   GroupCode   FORSALE
100     1       123         1           Y
100     2       123         1           Y
100     4       124         2           Y

但这是我想要得到的理想输出：

STORE   UPC     PROMOCODE   GroupCode   FORSALE
100     1       123         1           Y
100     2       123         1           Y
100     4       124         2           Y
100     7       125         3           N

从PROMOCODE 123和124返回1行也是完全可以接受的，即使它们已经有一些项目是FORSALE='Y'。因此，这也是可以接受的结果：

STORE   UPC     PROMOCODE   GroupCode   FORSALE
100     1       123         1           Y
100     2       123         1           Y
100     3       123         1           N
100     4       124         2           Y
100     5       124         2           N
100     7       125         3           N

Answer 1

您可以通过一个额外的行号窗口函数来实现这一点，即无论是否，始终从每组中包括一行

select STORE, UPC, PROMOCODE, Dense_Rank() over (partition by STORE order by PROMOCODE) GROUPCODE, FORSALE
from (
select * , Row_Number() over(partition by STORE, PROMOCODE order by UPC) rn
from TMP_PRODUCTS
)x
where FORSALE = 'Y' or rn=1

Answer 2

如果我理解正确，你想要的逻辑是：

SELECT STORE, UPC, PROMOCODE,
DENSE_RANK() OVER (PARTITION BY STORE ORDER BY PROMOCODE) AS GroupCode
FROM (SELECT P.*,
ROW_NUMBER() OVER (PARTITION BY STORE, PROMOCODE, FORSALE ORDER BY (SELECT NULL)) as seqnum
FROM TMP_PRODUCTS P
) P
WHERE FORSALE = 'Y' OR seqnum = 1;