是這樣的最近想做一種查詢內容大概是
我想做一種查詢，查2018/08/11~2018/11/11的全部資料
資料裡有身高、體重、BMI等等資料
如果說沒有資料沒變動，就只顯示一筆，有變動就顯示變動的那一筆
像這樣:
2018/08/11 身高180 體重:60 BMI:20
2018/09/11 身高180 體重:60 BMI:20
如果都沒變動只顯示最初的那筆，若有變動就顯示最初的那筆跟異動的那筆
目前我可以全部顯示出來跟選擇資料顯示的範圍，但這個我目前還沒頭緒，希望能有個方
向，謝謝。
已邀請的邦友 {{ invite_list.length }}/5
如果table schema (簡化只記錄 身高和體重)(BMI用算的不紀錄)
CREATE TABLE HEALTH_LOG(
id integer,
name text,
height integer,
weight integer
);
放入6筆資料
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 170, 50);
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 170, 50);
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 171, 50);
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 171, 51);
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 171, 52);
INSERT INTO HEALTH_LOG VALUES(1,'Tom', 171, 52);
撈出 體重+身高不同(代表資料有變動)的rows
SELECT DISTINCT (height+weight), *
FROM HEALTH_LOG
WHERE id = 1;
結果 (程式再取你要的欄位就好)
| 體重+身高 | id | 姓名 | 身高 | 體重 |
|---|---|---|---|---|
| 220 | 1 | Tom | 170 | 50 |
| 221 | 1 | Tom | 171 | 50 |
| 222 | 1 | Tom | 171 | 51 |
| 223 | 1 | Tom | 171 | 52 |
您好，不知道您是否會linq語法?我的語法目前是這樣
public List<HDOrderDto> FindHistory(string pat_guid,DateTime startDate, DateTime endDate)
{
var query = (from x in _repo.HD_ORDER
where x.PAT_GUID == pat_guid && x.ODR_DATE >= startDate && x.ODR_DATE <= endDate
orderby (x.ODR_DATE) descending
select new HDOrderDto
{
HDType = x.HD_TYPE,
OdrDate = x.ODR_DATE,
DryWtDesc = x.DRY_WT_DESC,
DryWtReasonDesc = x.DRY_WT_REASON_DESC
})ToList<HDOrderDto>();
}
加了distinct後
public List<HDOrderDto> FindHistory(string pat_guid,DateTime startDate, DateTime endDate)
{
if (String.IsNullOrWhiteSpace(pat_guid)) return null;
var query = (from x in _repo.HD_ORDER
where x.PAT_GUID == pat_guid && x.ODR_DATE >= startDate && x.ODR_DATE <= endDate
select new HDOrderDto
{
HDType = x.HD_TYPE,
OdrDate = x.ODR_DATE,
DryWtDesc = x.DRY_WT_DESC,
DryWtReasonDesc = x.DRY_WT_REASON_DESC
}).Distinct().OrderByDescending(x=>x.OdrDate).ToList<HDOrderDto>();
return query;
}
這樣好像無法完全消除掉重複的欄位，例如DryWtDesc這個欄位
我有找到一個網站，不知是否是這樣解
http://andy51002000.blogspot.com/2017/04/c-linq.html
最下面那個就是我想呈現的，只是我用的是linq+MVC
所以我不太清楚那個foreach該怎麼下，想請問是這樣解嗎?
C#語法我本身不太熟
我不太清楚他的function最後把SQL包裝成什麼樣子
如果能看到它原始的SQL，可以知道問題
了解，還是感謝。
MSSQL:
1.假設同一 UserID 同一天只有一筆資料
2.如果都沒變動只顯示最初的那筆，<==(顯示:最初的那筆)
若有變動就顯示最初的那筆跟異動的那筆 <==(顯示:最初的那筆及最後一次異動的那筆)
CREATE TABLE UserData(UserId VARCHAR(7),ModiDate DATE,High FLOAT,Weight FLOAT,BMI FLOAT);
INSERT INTO UserData(UserId,ModiDate,High,Weight,BMI)
VALUES
('A','2018/08/11',180,60,20),
('A','2018/09/11',180,60,20),
('A','2018/10/11',185,65,25),
('B','2018/11/11',190,70,30);
SELECT A.UserId,CONVERT(VARCHAR,A.ModiDate,111) AS ModiDate,A.High,A.Weight,A.BMI
FROM UserData AS A
INNER JOIN (
SELECT X.UserID,MAX(X.ModiDate) as MAXModiDate
FROM UserData AS X
WHERE 1=1
AND ModiDate BETWEEN '2018/08/11' AND '2018/11/11'
GROUP BY X.UserID
) AS B ON B.UserId=A.UserId AND B.MAXModiDate=A.ModiDate
UNION
SELECT C.UserId,CONVERT(VARCHAR,C.ModiDate,111) AS ModiDate,C.High,C.Weight,C.BMI
FROM UserData AS C
INNER JOIN (
SELECT Y.UserID,MIN(Y.ModiDate) as MINModiDate
FROM UserData AS Y
WHERE 1=1
AND ModiDate BETWEEN '2018/08/11' AND '2018/11/11'
GROUP BY Y.UserID
) AS D ON D.UserId=C.UserId AND D.MINModiDate=C.ModiDate
ORDER BY UserId,ModiDate
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=0cd11ce21a00d9d31fb680deaf0e4643
iThome鐵人賽
看影片追技術