0

## SQL問題請教

2018/08/11 身高180 體重:60 BMI:20
2018/09/11 身高180 體重:60 BMI:20

tenno081 iT邦新手 4 級 ‧ 2018-11-18 18:37:04 檢舉

### 3 個回答

1
a4027971
iT邦新手 5 級 ‧ 2018-11-18 20:32:45

``````CREATE TABLE HEALTH_LOG(
id integer,
name text,
height integer,
weight integer
);
``````

``````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);
``````

``````SELECT DISTINCT (height+weight), *
FROM HEALTH_LOG
WHERE id = 1;
``````

220 1 Tom 170 50
221 1 Tom 171 50
222 1 Tom 171 51
223 1 Tom 171 52

tenno081 iT邦新手 4 級 ‧ 2018-11-18 21:53:32 檢舉

tenno081 iT邦新手 4 級 ‧ 2018-11-19 15:31:33 檢舉

`````` 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>();
}
``````

``````  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;
}
``````

http://andy51002000.blogspot.com/2017/04/c-linq.html

a4027971 iT邦新手 5 級 ‧ 2018-11-20 10:46:11 檢舉

C#語法我本身不太熟

tenno081 iT邦新手 4 級 ‧ 2018-11-20 11:46:00 檢舉

1
rogeryao
iT邦高手 4 級 ‧ 2018-11-18 20:12:27

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

``````select * from UserData a  where  ModiDate = (select max(x.ModiDate) from UserData x where x.UserID = a.UserID)
union
select * from UserData a  where  ModiDate = (select min(x.ModiDate) from UserData x where x.UserID = a.UserID)
``````
0
froce
iT邦大師 2 級 ‧ 2018-11-18 21:45:45