如果存在重复行，如何在sql中显示空白记录 [英] How to show blank record in sql if duplicate rows exists

问题描述

我的 SQL 查询结果，

<前>EmployeeID EmployeeName 部门1 约翰销售2 罗伯特销售3 Sudhir 销售4 Roj 发展

输出应该是

<前>EmployeeID EmployeeName 部门1 约翰销售2 罗伯特3 苏迪尔4 Roj 发展

解决方案

您可以这样做:

;带CTE作为(选择员工 ID、员工姓名、部门、ROW_NUMBER() OVER(PARTITION BY Department ORDER BY(SELECT 1)) rownum从表 1)选择员工ID，员工姓名，案例行号WHEN 1 THEN 部门别的 ''END AS 部门从 CTE按员工 ID 订购；

SQL Fiddle 演示

请注意:尽量不要在 SQL 中执行此类格式化操作.SQL 与无关.

My SQL Query Results,

EmployeeID    EmployeeName    Department
1             John            Sales
2             Robert          Sales
3             Sudhir          Sales
4             Roj             Development

Output Should be

EmployeeID    EmployeeName    Department
1             John            Sales
2             Robert          
3             Sudhir          
4             Roj             Development

解决方案

You can do this:

;WITH CTE
AS
( 
   SELECT EmployeeID, EmployeeName, Department,
     ROW_NUMBER() OVER(PARTITION BY Department ORDER BY(SELECT 1)) rownum
  FROM table1
)
SELECT
  EmployeeId,
  EmployeeName,
  CASE rownum
    WHEN 1 THEN Department
    ELSE ''
  END AS Department
FROM CTE
ORDER BY EmployeeID;

SQL Fiddle Demo

Note that: Try not to do these sort of formatting stuff in SQL. SQL is not about formatting.

这篇关于如果存在重复行，如何在sql中显示空白记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！