請問這段SQL該如何下呢?

sql msql

tenno081 2022-03-17 14:47:58 ‧ 1249 瀏覽

分享至

這是我的一段SQL語法


		SELECT Top 1
			 mV.CID ,mV.HID ,mV.SYSTEM ,mV.VGUID ,mV.HisGUID 
			,mv.RDate
			,mv.RTime
			,mV.IpdGUID	
			,mv.AD_Ideal_Wei
			,mv.AD_Knee
			,mv.AD_Height
			,mv.AD_Regular_Wei
			,mv.AD_Current_Wei
			,mv.AD_IBW
			,mv.AD_Adjust_Wei
			,mv.AD_Oth
			,mv.Lab
			,mv.AD_BMI
			,ptype ='Second'
		FROM viewNIS_GetNutSecendVisitViewNew As mV	
		WHERE mV.CID= @CID AND mV.HID= @HID  AND mV.SYSTEM =  @System 
					AND mV.IpdGUID= @IpdGUID 
		ORDER BY mV.RDate DESC, mV.RTime DESC,mv.CreDate desc ,mv.ModDate desc


	

		SELECT Top 1
			 mV.CID ,mV.HID ,mV.SYSTEM ,mV.VGUID ,mV.HisGUID 
			,mv.RDate
			,mv.RTime
			,mV.IpdGUID	
			,mv.AD_Ideal_Wei
			,mv.AD_Knee
			,mv.AD_Height
			,mv.AD_Regular_Wei
			,mv.AD_Current_Wei
			,mv.AD_IBW
			,mv.AD_Adjust_Wei
			,mv.AD_Oth
			,mv.Lab
			,mv.AD_BMI
			,ptype ='First'
		FROM viewNIS_GetNutFirstVisitViewNew As mV	
		WHERE mV.CID= @CID AND mV.HID= @HID  AND mV.SYSTEM =  @System 
					AND mV.IpdGUID= @IpdGUID 
		ORDER BY mV.RDate DESC, mV.RTime DESC,mv.CreDate desc ,mv.ModDate desc

執行結果大略如圖

我能請問我該如何做才能只顯示最近日期的資料呢?
我有想過用union 來處理只是這樣我好像就不能用order by
目前顯示的都是已經篩選過的只差做判斷說要只顯示哪一筆

obarisk iT邦研究生 2 級 ‧ 2022-03-18 13:59:29 檢舉

這不是直接改 view 最好嗎？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2022-03-17 15:01:20

最佳解答

你可以把Order放在Row_Number()Over(order by ....)
然後Union起來~下where是排序 1 就可以帶出來~

select top 1 *
from ((
		SELECT 
			Row_Number()Over(ORDER BY mV.RDate DESC, mV.RTime DESC,mv.CreDate desc ,mv.ModDate desc) Sort
			,mV.CID ,mV.HID ,mV.SYSTEM ,mV.VGUID ,mV.HisGUID 
			,mv.RDate
			,mv.RTime
			,mV.IpdGUID	
			,mv.AD_Ideal_Wei
			,mv.AD_Knee
			,mv.AD_Height
			,mv.AD_Regular_Wei
			,mv.AD_Current_Wei
			,mv.AD_IBW
			,mv.AD_Adjust_Wei
			,mv.AD_Oth
			,mv.Lab
			,mv.AD_BMI
			,ptype ='Second'
		FROM viewNIS_GetNutSecendVisitViewNew As mV	
		WHERE mV.CID= @CID AND mV.HID= @HID  AND mV.SYSTEM =  @System 
					AND mV.IpdGUID= @IpdGUID 
)union all(
		SELECT 
			Row_Number()Over(ORDER BY ORDER BY mV.RDate DESC, mV.RTime DESC,mv.CreDate desc ,mv.ModDate desc) Sort
			,mV.CID ,mV.HID ,mV.SYSTEM ,mV.VGUID ,mV.HisGUID 
			,mv.RDate
			,mv.RTime
			,mV.IpdGUID	
			,mv.AD_Ideal_Wei
			,mv.AD_Knee
			,mv.AD_Height
			,mv.AD_Regular_Wei
			,mv.AD_Current_Wei
			,mv.AD_IBW
			,mv.AD_Adjust_Wei
			,mv.AD_Oth
			,mv.Lab
			,mv.AD_BMI
			,ptype ='First'
		FROM viewNIS_GetNutFirstVisitViewNew As mV	
		WHERE mV.CID= @CID AND mV.HID= @HID  AND mV.SYSTEM =  @System 
					AND mV.IpdGUID= @IpdGUID 
)) k
where Sort = 1
order by RDate desc

或者~簡單用in....

		SELECT top 1 
			mV.CID ,mV.HID ,mV.SYSTEM ,mV.VGUID ,mV.HisGUID 
			,mv.RDate
			,mv.RTime
			,mV.IpdGUID	
			,mv.AD_Ideal_Wei
			,mv.AD_Knee
			,mv.AD_Height
			,mv.AD_Regular_Wei
			,mv.AD_Current_Wei
			,mv.AD_IBW
			,mv.AD_Adjust_Wei
			,mv.AD_Oth
			,mv.Lab
			,mv.AD_BMI
			,ptype in('Second','First')
		FROM viewNIS_GetNutFirstVisitViewNew As mV	
		WHERE mV.CID= @CID AND mV.HID= @HID  AND mV.SYSTEM =  @System 
					AND mV.IpdGUID= @IpdGUID 
        ORDER BY ORDER BY mV.RDate DESC, mV.RTime DESC,mv.CreDate desc ,mv.ModDate desc

回應 5
分享
檢舉

看更多先前的回應...收起先前的回應...

tenno081 iT邦研究生 4 級 ‧ 2022-03-17 15:05:46 檢舉

謝謝，我來研究看看

tenno081 iT邦研究生 4 級 ‧ 2022-03-17 15:11:18 檢舉

不好意思，我執行出來變成兩個合併再一起
我這個是同一個TABLE裡的資料，但會有一個欄位會做區分
目前我篩選出來的就是區分好的，只是需要再做個判斷
我其實是有在C#那裏有做後續處理，只是覺得這個應該可以在SQL搞定

純真的人 iT邦大師 1 級 ‧ 2022-03-17 15:19:28 檢舉

那你的判斷的結果是什麼??因為瞎子摸象很難明白你要得最後畫面~
可以貼圖表示一下嗎~

tenno081 iT邦研究生 4 級 ‧ 2022-03-17 15:29:15 檢舉

抱歉抱歉
我是用RDATE判斷要顯示哪一筆
圖中有3/15號一筆3/17號一筆
3/17最近所以就只要顯示3/17的那筆就好

純真的人 iT邦大師 1 級 ‧ 2022-03-17 15:55:27 檢舉

那就是union後面再多下orderb by RDate desc就可以了
已在更新程式碼~

登入發表回應

rogeryao

iT邦超人 8 級 ‧ 2022-03-17 17:19:42

請參閱 : 如何查詢SQL重複資料最新一筆

CREATE TABLE TableX (
FA Nvarchar(20) NULL ,     -- 品名
FB Nvarchar(20) NULL,      -- 開始日期
FC Nvarchar(20) NULL,      -- 完成日期
FD Nvarchar(20) NULL);     -- 階段

INSERT INTO TableX (FA,FB,FC,FD)
VALUES 
(N'配電盤','2006-03-01','2006-03-15','1'),
(N'配電盤','2006-03-15','2006-03-18','2'),
(N'配電盤','2006-03-18','2006-03-20','3'),
(N'發電機','2006-03-05','2006-03-10','1');

CREATE TABLE TableY (
FA Nvarchar(20) NULL ,     -- 品名
FB Nvarchar(20) NULL,      -- 開始日期
FC Nvarchar(20) NULL,      -- 完成日期
FD Nvarchar(20) NULL);     -- 階段

INSERT INTO TableY (FA,FB,FC,FD)
VALUES 
(N'配電盤','2006-03-01','2006-03-15','1'),
(N'發電機','2006-03-05','2006-03-10','1'),
(N'發電機','2006-03-10','2006-03-15','2');

SELECT FA AS '品名',FB AS '開始日期',FC AS '完成日期', FD AS '階段'
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY FA ORDER BY FD DESC) AS ROWNUM,*
FROM (
SELECT FA,FB,FC,FD
FROM TableX
UNION ALL
SELECT FA,FB,FC,FD
FROM TableY) AS TempK
) AS TempM
WHERE ROWNUM = 1
ORDER BY FA DESC

Demo