问题描述
我必须编写一个查询来显示来自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
[]
推荐答案
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中的某些条件显示唯一注释？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！