 |
| |
C# WinApp ช่วยแปลง SQL นี้ให้อยู่ในรูปแบบ IIF(ISNULL(column),0,column) หน่อยครับ |
| |
 |
|
|
 |
 |
|
Code (SQL)
IIF(ISNULL(column),0,column);
Code (SQL)
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1,
(SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID ) AS sumR,
(SELECT sum( tblProductSale.Count) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID ) AS sumS,
(sumR-sumS) AS sumC
FROM tblProductType;
column ที่อยากแปลง คือ sumR sumS sumC ครับ
ปล.ขอแบบสั้นๆนะครับ
Tag : .NET, Win (Windows App), C#, VS 2013 (.NET 4.x)
|
|
|
 |
 |
 |
 |
| Date :
2017-07-23 10:34:45 |
By :
lamaka.tor |
View :
913 |
Reply :
3 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
ที่ผมได้มามันค่อนข้างยาวครับ
Code (SQL)
SELECT tblProductType.pdtID, tblProductType.Barcode, tblProductType.ProductType_1,
(iif(isnull( (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )),0, (SELECT sum( tblProductReceive.Count) from tblProductReceive where tblProductReceive.pdtID = tblProductType.pdtID )) ) AS sumR,
(iif(isnull((SELECT sum( tblProductSale.Count) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID )),0,(SELECT sum( tblProductSale.Count) from tblProductSale where tblProductSale.pdtID = tblProductType.pdtID ))) AS sumS,
(sumR-sumS) AS sumC
FROM tblProductType;
|
| |
|
|
| |
| Date :
2017-07-23 10:37:16 |
By :
lamaka.tor |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ไม่รู้จะถูกไหมน่ะครับ
Code (SQL)
SELECT p.pdtID, p.Barcode, p.ProductType,
sum(isnull(r.Count,0)) as SumR,
sum(isnull(S.Count,0)) as SumS,
sum(isnull(r.Count,0))-sum(isnull(S.Count,0)) as SumC
from tblProductType p
left join tblProductReceive r on p.pdtID =r.pdtID
left join tblProductSale s on p.pdtID = s.pdtID
|
| |
|
|
| |
| Date :
2017-07-25 13:57:03 |
By :
fonfire |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
MSSQL
SELECT t.*, ISNULL(t.sumR,0)-ISNULL(t.sumS,0) As sumC
FROM (SELECT p.pdtID, p.Barcode, p.ProductType_1,
(SELECT sum(x.Count) from tblProductReceive As x where x.pdtID = p.pdtID ) AS sumR,
(SELECT sum(x.Count) from tblProductSale As x where x.pdtID = p.pdtID ) AS sumS,
(sumR-sumS) AS sumC
FROM tblProductType As p) As t;
|
| |
|
|
| |
| Date :
2017-07-26 12:29:52 |
By :
watcharop |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
