如何根据SQL Server中的某些条件显示唯一注释？ [英] How to display unique comments based on certain conditions in SQL Server?

问题描述

我必须编写一个查询来显示来自customer表的唯一客户评论，如下所示：



CUSTOMER TABLE

I have to write a query to display unique customer comments from the customer table as shown below:

CUSTOMER TABLE

CustomerID	SequenceNo	Comments
1	1	ABC D
1	2	CDE
1	3	ABC
1	4	ABC D
1	5	CDE
1	6	abc
2	7	ABC DEF
2	8	
2	9	ABC DEF
2	10	DEF
2	11	XYZ 123
2	12	ABC
3	13	PQ RST

上述客户表的定义将是

Definition of above Customer table will be

CustomerID	INT
SequenceNo	INT
Comments	VARCHAR(MAX)

我想写一个SQL查询来过滤客户表中的记录，并显示每个客户的唯一评论历史。



标准：

1.仅显示所有客户的客户表中的唯一注释，

2.如果注释相同n显示具有最大SequenceNo的行

3.注释可以为空。



约会表的输出如下所示：

I want to write a SQL query to filter the records in the Customer Table and display unique comment history for each customer.

Criterias:
1. Display only Unique Comments from Customer Table for all the customers,
2. If Comments are same then display the row which has maximum SequenceNo
3. Comments can be blank.

Output of the about table would be as shown below:

CustomerID	SequenceNo	Comments
1	3	ABC
1	4	ABC D
1	5	CDE
1	6	abc
2	8	
2	9	ABC DEF
2	10	DEF
2	11	XYZ 123
2	12	ABC
3	13	PQ RST

https://www.dropbox.com/s/h0t3xvvvlaqirk3/11698890_10154036087194466_3495075191431781854_o.jpg?dl=0 [ ^ ]

推荐答案

查看示例：

Have a look at sample:

DECLARE @tmp TABLE(CustomerID   INT, SequenceNo INT, Comments   VARCHAR(MAX))

INSERT INTO @tmp (CustomerID, SequenceNo, Comments)
VALUES(1, 1, 'ABC D'),
(1, 2, 'CDE'),
(1, 3, 'ABC'),
(1, 4, 'ABC D'),
(1, 5, 'CDE'),
(1, 6, 'abc'),
(2, 7, 'ABC DEF'),
(2, 8, NULL),
(2, 9, 'ABC DEF'),
(2, 10, 'DEF'),
(2, 11, 'XYZ 123'),
(2, 12, 'ABC'),
(3, 13, 'PQ RST')

SELECT CustomerID, SequenceNo, Comments
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SequenceNo ASC) AS RowNo
    FROM @tmp
    ) AS T
WHERE RowNo > 1

以上查询返回：

Above query returns:

CustomerID	SequenceNo	Comments
1	2	CDE
1	3	ABC
1	4	ABC D
1	5	CDE
1	6	abc
2	8	NULL
2	9	ABC DEF
2	10	DEF
2	11	XYZ 123
2	12	ABC

这篇关于如何根据SQL Server中的某些条件显示唯一注释？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！