如何根据数据仅显示表的几列 [英] How to display only few columns of a table based on the data

问题描述

Consider the table below

Name partno.  sch_date    WO#   owed panels
 aa   1234     08/22/2017  121   22    26
 aa   1234     08/22/2017  222   22    27
 aa   1234     08/22/2017  242   22    27
 aa   1234     08/29/2017  152   20    24
 aa   1234     08/29/2017  167   20    24
 aa   1234     08/29/2017  202   20    26

Is it possible to display the data in such way that when i read through the data if the first partno. in the table has the number of panels  greater than owed, then i don't won't to dispaly the other partno. schedule on the same date(sch_date).

Expected Result

Name partno.  sch_date    WO#   owed panels
 aa   1234     08/22/2017  121   22    26
 aa   1234     08/29/2017  152   20    24

我尝试了什么：



我不知道如何在这里暗示案例函数。

What I have tried:

I am not sure how to imply the case function here.

推荐答案

试试以下..

Try following ..

select TOP 1  Name,partno,sch_date,WO#,owed,panels from TableName
ORDER BY
    ROW_NUMBER() OVER(PARTITION BY sch_date ORDER BY WO# ASC)

您好，

查看以下解决方案bas编辑ROW_NUMBER（）。



SELECT ROW_NUMBER（）OVER（PARTITION BY [Name]，[sch_date] ORDER BY [Name]，[sch_date]）AS RN， *

INTO #Temp

FROM #YourTable WHERE面板>欠



SELECT * FROM #Temp



RN名称partno＃sch_date WO＃欠板

1 aa 1234 2017-08-22 00：00：00.000 121 22 26
2 aa 1234 2017-08-22 00：00：00.000 222 22 27

3 aa 1234 2017-08-22 00：00：00.000 242 22 27

1 aa 1234 2017-08-29 00：00：00.000 152 20 24

2 aa 1234 2017-08-29 00：00：00.000 167 20 24

3 aa 1234 2017-08-29 00：00：00.000 202 20 26



SELECT * FROM #Temp WHERE RN = 1



结果：



RN名称partno＃sch_date WO＃欠板

1 aa 1234 2017-08- 22 00：00：00.000 121 22 26

1 aa 1234 2017-08-29 00：00：00.000 152 20 24





谢谢和问候，

普拉迪普Magati。

Hello,
Check the below solution based on ROW_NUMBER().

SELECT ROW_NUMBER() OVER (PARTITION BY [Name],[sch_date] ORDER BY [Name],[sch_date]) AS RN,*
INTO #Temp
FROM #YourTable WHERE panels>owed

SELECT * FROM #Temp

RN Name partno# sch_date WO# owed panels
1 aa 1234 2017-08-22 00:00:00.000 121 22 26
2 aa 1234 2017-08-22 00:00:00.000 222 22 27
3 aa 1234 2017-08-22 00:00:00.000 242 22 27
1 aa 1234 2017-08-29 00:00:00.000 152 20 24
2 aa 1234 2017-08-29 00:00:00.000 167 20 24
3 aa 1234 2017-08-29 00:00:00.000 202 20 26

SELECT * FROM #Temp WHERE RN=1

Result:

RN Name partno# sch_date WO# owed panels
1 aa 1234 2017-08-22 00:00:00.000 121 22 26
1 aa 1234 2017-08-29 00:00:00.000 152 20 24


Thanks&Regards,
Pradeep Magati.

;WITH TempTable (Name,partno,sch_date,WO#,owed,panels, duplicateRecCount)
AS
(SELECT Name,partno,sch_date,WO#,owed,panels,ROW_NUMBER() OVER(PARTITION by Name,partno,sch_date,WO#,owed,panels ORDER BY Name)
AS duplicateRecCount FROM [Table Name]	
Select Name,partno,sch_date,WO#,owed,panels from TempTable where duplicateRecCount=1

这篇关于如何根据数据仅显示表的几列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！