SQL：如何在SSMS中将值按类别排序 [英] SQL: How to sort values into categories in ssms

问题描述

我创建查询

SELECT Dt, CustomerName, ItemRelation, SaleCount, 
              DocumentNum, DocumentYear, IsPromo, CustomerType
              FROM [dbo].[promo_data]  where [CustomerType]='XY'

有股票数据[IsPromo](0-无股票，1为股票) 如何仅选择Customertype的这些OB XY 对于操作类别=1，它们只有一个值1

简单示例

[ItemRelation]  [SaleCount] IsPromo ,[DocumentNum]  [DocumentYear]
11203   8,85947691  0   138 2018
11203   9,450108704 0   138 2018
11203   12,40326767 1   138 2018
11202   8,85947691  0   137 2018
11202   9,450108704 0   137 2018
11202   12,40326767 1   137 2018
11202   25,98779894 1   137 2018
11202   63,19760196 1   137 2018

我必须选择

11203       1   138 2018

因为

11202       137 2018

有两个

推荐答案

您似乎需要：

SELECT ItemRelation, DocumentNum, DocumentYear, IsPromo
FROM [dbo].[promo_data]
WHERE CustomerType = 'XY' AND IsPromo = 1
GROUP BY ItemRelation, DocumentNum, DocumentYear, IsPromo
HAVING COUNT(*) = 1;

编辑：

若要检索所有列，请使用窗口函数：

SELECT *
FROM (SELECT pd.*,
             COUNT(*) OVER (PARTITION BY ItemRelation, DocumentNum, DocumentYear, IsPromo) as cnt
      FROM [dbo].[promo_data] pd
      WHERE CustomerType = 'XY' AND IsPromo = 1
     ) pd
WHERE cnt = 1;

这篇关于SQL：如何在SSMS中将值按类别排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！