 |
| |
C# WinApp ช่วยดู SQL ให้หน่อยครับ ออกมาไม่ตรงตามที่คิดไว้ |
| |
 |
|
|
 |
 |
|
Code (SQL)
SELECT tblReagntList.rlID,
tblReagntList.rl_name,
IIf(IsNull(Sum(tblReagent_r.Count)),0,Sum(tblReagent_r.Count)) AS SumOf_r,
IIf(IsNull(Sum(tblReagent_r.Sum_page)),0,Sum(tblReagent_r.Sum_page)) AS SumOf_rb,
IIf(IsNull(Sum(tblReagent_o.Count)),0,Sum(tblReagent_o.Count)) AS SumOf_o,
SumOf_r-SumOf_o AS BL,
tblReagntList.lessthan
FROM (tblReagntList LEFT JOIN tblReagent_o ON tblReagntList.rlID = tblReagent_o.rrID) INNER JOIN tblReagent_r ON tblReagntList.rlID = tblReagent_r.rlID
GROUP BY tblReagntList.rlID, tblReagntList.rl_name, tblReagntList.lessthan;
1. คือถ้าผมอยากตัด IIf ออกให้เหลือแค่ IsNull เพื่อให้โค้ดมันสั้นลง ต้องเขียนยังไงครับ
2. โค้ดนี้หลังจากผลออกมาเป็นแบบนี้ครับ
แต่ความจริงต้องได้ 8 ครับ
เลยงง ๆ ว่าผม join ตรงไหนผิดไปรึป่าว
Tag : .NET, Win (Windows App), C#
|
|
|
 |
 |
 |
 |
| Date :
2017-11-17 17:57:39 |
By :
lamaka.tor |
View :
778 |
Reply :
4 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
น่าจะแบบนี้
Code (SQL)
SELECT
rl.rlID, rl.rl_name,
sum(coalesce(r.Count, 0)) AS SumOf_r,
sum(coalesce(r.Sum_page,0)) AS SumOf_rb,
sum(coalesce(o.Count,0)) AS SumOf_o,
SumOf_r-SumOf_o AS BL, # บันทัดนี้ น่าใช้ โค๊ดหลัก บวกเอา
rl.lessthan
FROM tblReagntList as rl
LEFT JOIN tblReagent_o as o ON rl.rlID = o.rrID
INNER JOIN tblReagent_r as r ON rl.rlID = r.rlID
GROUP BY rl.rlID, rl.rl_name, rl.lessthan;
|
| |
|
|
| |
| Date :
2017-11-17 18:47:44 |
By :
Chaidhanan |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ผมทำแบบนี้ดันได้ครับ ยาวกว่าเดิมอีก
Code (SQL)
SELECT tblReagntList.rlID, tblReagntList.rl_name,
(iif(isnull( (SELECT sum( tblReagent_r.Count) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_r.Count) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )) ) AS SumOf_r,
(iif(isnull( (SELECT sum( tblReagent_r.Sum_page) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_r.Sum_page) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )) ) AS SumOf_rb, (iif(isnull( (SELECT sum( tblReagent_o.Count) from tblReagent_o where tblReagent_o.rrID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_o.Count) from tblReagent_o where tblReagent_o.rrID =tblReagntList.rlID )) ) AS SumOf_o, SumOf_r-SumOf_o AS BL, tblReagntList.lessthan
FROM tblReagntList;
|
| |
|
|
| |
| Date :
2017-11-17 20:09:31 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
coalesce( field, default)
สำหรับ access ใช้ Nz( field, default)
|
| |
|
|
| |
| Date :
2017-11-17 23:18:24 |
By :
Chaidhanan |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
Load balance : Server 00
