MSSQL查询省略特定的行值

本文关键字：查询省 MSSQL | 更新日期: 2023-09-27 18:10:01

我有以下查询来根据客户ID检索客户销售报告。它可以工作，但是我怎样才能省略重复的客户名称呢?

select Cu.CustomerName, Cu.City, pd.pname, 
Cs.qty, Cs.totalAmount, Cs.payed, Cs.credit, Cs.CreditEndDate
from Customer Cu 
inner join CreditSales Cs on Cu.ID=Cs.CustomerID
left join Product pd on pd.pid=Cs.pid
where Cu.ID=6
order by Cu.CustomerName

结果是:

+--------+-------+---------------+----+----------+----------+----------+------------+
| Halima | Jimma | Mouse         |  1 | 345.00   | 345.00   | 0.00     | 2015-08-29 |
| Halima | Jimma | Mobile        | 10 | 92000.00 | 40000.00 | 52000.00 | 2015-08-23 |
| Halima | Jimma | Iphone        |  2 | 13800.00 | 6500.00  | 7300.00  | 2015-08-28 |
| Halima | Jimma | Tape Recorder | 10 | 5175.00  | 4000.00  | 1175.00  | 2015-10-30 |
+--------+-------+---------------+----+----------+----------+----------+------------+

但我需要这样:

+--------+-------+---------------+----+----------+----------+----------+------------+
| Halima | Jimma | Mouse         |  1 | 345.00   | 345.00   | 0.00     | 2015-08-29 |
|        | Jimma | Mobile        | 10 | 92000.00 | 40000.00 | 52000.00 | 2015-08-23 |
|        | Jimma | Iphone        |  2 | 13800.00 | 6500.00  | 7300.00  | 2015-08-28 |
|        | Jimma | Tape Recorder | 10 | 5175.00  | 4000.00  | 1175.00  | 2015-10-30 |
+--------+-------+---------------+----+----------+----------+----------+------------+

这意味着客户名应该只显示一个实例。

帮忙吗?

MSSQL查询省略特定的行值

根据Milen Pavlov响应，您可以尝试如下:

SELECT CASE WHEN ROW_NUMBER() over (partition by [CustomerName] order by [CustomerName]) = 1 THEN [CustomerName] ELSE '' END
FROM Customer 
ORDER BY [CustomerName]

这可以完成这项工作(我需要您必须在over()子句中添加一个排序):

select case when a.rn > 1 then '' else a.CustomerName end as CustomerName, a.City, a.pname, a.qty, a.totalAmount, a.payed, a.credit, a.CreditEndDate
from 
(
    select Cu.CustomerName, Cu.City, pd.pname, 
       Cs.qty, Cs.totalAmount, Cs.payed, Cs.credit, Cs.CreditEndDate, ROW_NUMBER() over (partition by Cu.CustomerName order by  Cs.CreditEndDate) as rn
    from Customer Cu 
      inner join CreditSales Cs on Cu.ID=Cs.CustomerID
         left join Product pd on pd.pid=Cs.pid
    where Cu.ID=6       
) a 
order by a.CustomerName

不认为这是SQL的工作，但如果你坚持你可以创建两个表，第一个与所有的行和列，然后删除第一行，和其他表与第一行，只是做一个左连接，并使所有字段的连接。

相关文章：