为什么MS不能同一列中引用别名,Case 语句能不能多条件组合,高手们都进来一下
SELECT dbo.PiList.ProID, dbo.Pro.ProNumber AS 配件编号, dbo.Pro.ChiName AS 名称,
dbo.Pro.ChiDescription AS 描述, dbo.Pro.CustomProNumber AS 客户产品编号,
dbo.PiList.QTY AS 产品定单量, Main.Num AS 产品库存量,
CASE WHEN Main.Num IS NULL
THEN Dbo.pilist.qty ELSE dbo.PiList.QTY - Main.Num END AS 产品实需求量,
dbo.PiList.QTY * dbo.CostList.Num AS 配件定单量, CASE WHEN Main.Num IS NULL
THEN (CASE WHEN Costlist.num IS NULL
THEN ' ' ELSE pilist.qty * costlist.num END) ELSE (CASE WHEN costlist.num IS NULL
THEN ' ' ELSE (dbo.PiList.QTY - Main.Num) * dbo.CostList.Num END)
END AS 配件余定单量, Child.Num AS 配件库存量,
CASE WHEN Main.Num IS NOT NULL AND NOT CostList.Num IS NULL AND
Child.num IS NOT NULL THEN (dbo.PiList.QTY - Main.Num)
* dbo.CostList.Num - Child.Num WHEN Main.Num IS NOT NULL AND
CostList.Num IS NULL THEN ' ' WHEN Main.Num IS NOT NULL AND
CostList.Num IS NOT NULL AND Child.Num IS NULL
THEN (dbo.PiList.QTY - Main.Num) * dbo.CostList.Num WHEN Main.Num IS NULL AND
CostList.Num IS NOT NULL AND CHILD.Num IS NOT NULL
THEN dbo.PiList.QTY * dbo.CostList.Num - Child.Num WHEN Main.Num IS NULL AND
CostList.Num IS NULL THEN ' ' WHEN Main.Num IS NULL AND
CostList.Num IS NOT NULL AND child.Num IS NULL
THEN dbo.PiList.QTY * dbo.CostList.Num END AS AS 配件实需求量
FROM dbo.PiList INNER JOIN
dbo.Cost INNER JOIN
(SELECT dbo.CostList.CProID, SUM(dbo.PiList.QTY) AS 产品定单量
FROM dbo.Cost INNER JOIN
dbo.CostList ON dbo.Cost.CostID = dbo.CostList.CostID RIGHT OUTER JOIN
dbo.PiList ON dbo.Cost.ProID = dbo.PiList.ProID
WHERE (dbo.PiList.PINO = @PiNo)
GROUP BY dbo.CostList.CProID) TempTable LEFT OUTER JOIN
dbo.Storage Child ON TempTable.CproID = Child.ProID INNER JOIN
dbo.CostList ON dbo.CostList.CProID = TempTable.CProid ON
dbo.Cost.CostID = dbo.CostList.CostID ON
dbo.PiList.ProID = dbo.Cost.ProID INNER JOIN
dbo.Pro ON dbo.CostList.CProID = dbo.Pro.ProID LEFT OUTER JOIN
dbo.Storage Main ON dbo.PiList.ProID = Main.ProID
WHERE (dbo.PiList.PINO = @PiNo)
像类拟于这种存储过程
NOT 字段 IS NULL 和 字段 Is NOT NULL
都不行....应该怎么写
还有,我无法用到同一列的别名,公式只能重新再写,很是郁闷,我记得ACCESS是可以用别名的.
[解决办法]
SELECT dbo.PiList.ProID, dbo.Pro.ProNumber AS 配件编号, dbo.Pro.ChiName AS 名称,
dbo.Pro.ChiDescription AS 描述, dbo.Pro.CustomProNumber AS 客户产品编号,
dbo.PiList.QTY AS 产品定单量, Main.Num AS 产品库存量,
(CASE WHEN Main.Num IS NULL
THEN Dbo.pilist.qty ELSE dbo.PiList.QTY - Main.Num END) AS 产品实需求量,
dbo.PiList.QTY * dbo.CostList.Num AS 配件定单量, (CASE WHEN Main.Num IS NULL
THEN (CASE WHEN Costlist.num IS NULL
THEN ' ' ELSE pilist.qty * costlist.num END) ELSE (CASE WHEN costlist.num IS NULL
THEN ' ' ELSE (dbo.PiList.QTY - Main.Num) * dbo.CostList.Num END)
END) AS 配件余定单量, Child.Num AS 配件库存量,
(CASE WHEN Main.Num IS NOT NULL AND NOT CostList.Num IS NULL AND
Child.num IS NOT NULL THEN (dbo.PiList.QTY - Main.Num)
* dbo.CostList.Num - Child.Num WHEN Main.Num IS NOT NULL AND
CostList.Num IS NULL THEN ' ' WHEN Main.Num IS NOT NULL AND
CostList.Num IS NOT NULL AND Child.Num IS NULL
THEN (dbo.PiList.QTY - Main.Num) * dbo.CostList.Num WHEN Main.Num IS NULL AND
CostList.Num IS NOT NULL AND CHILD.Num IS NOT NULL
THEN dbo.PiList.QTY * dbo.CostList.Num - Child.Num WHEN Main.Num IS NULL AND
CostList.Num IS NULL THEN ' ' WHEN Main.Num IS NULL AND
CostList.Num IS NOT NULL AND child.Num IS NULL
THEN dbo.PiList.QTY * dbo.CostList.Num END ) AS 配件实需求量
FROM dbo.PiList INNER JOIN
dbo.Cost INNER JOIN
(SELECT dbo.CostList.CProID, SUM(dbo.PiList.QTY) AS 产品定单量
FROM dbo.Cost INNER JOIN
dbo.CostList ON dbo.Cost.CostID = dbo.CostList.CostID RIGHT OUTER JOIN
dbo.PiList ON dbo.Cost.ProID = dbo.PiList.ProID
WHERE (dbo.PiList.PINO = @PiNo)
GROUP BY dbo.CostList.CProID) TempTable LEFT OUTER JOIN
dbo.Storage Child ON TempTable.CproID = Child.ProID INNER JOIN
dbo.CostList ON dbo.CostList.CProID = TempTable.CProid ON
dbo.Cost.CostID = dbo.CostList.CostID ON
dbo.PiList.ProID = dbo.Cost.ProID INNER JOIN
dbo.Pro ON dbo.CostList.CProID = dbo.Pro.ProID LEFT OUTER JOIN
dbo.Storage Main ON dbo.PiList.ProID = Main.ProID
WHERE (dbo.PiList.PINO = @PiNo)
加上 ()就好了
