27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT TOP 100 PERCENT
(select CEName from country where CountryCode = A.SellTo) COLLATE Chinese_PRC_CI_AS AS '国家',
A.OurOrderNO COLLATE Chinese_PRC_CI_AS AS 定单号,
B.FCode COLLATE Chinese_PRC_CI_AS AS 产品物料代码,
B.PCName COLLATE Chinese_PRC_CI_AS AS 产品中文名称,
B.Description COLLATE Chinese_PRC_CI_AS AS 产品规格,
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) AS [定单数量/箱],
b.CHECKQTY AS [样/赠品],
IsNull(B.QTY,0) AS [实际完成数量],
B.NetWeight / 1000 AS [净重/箱],
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) * B.NetWeight / 1000 AS [总净重/公斤],
A.AllowProducedDate AS 允许生产完成日期,
B.ConfirmDate AS 生产预计完成日期,
A.FactStowagedDate 实际装柜日期,
A.ModeOfShipment AS [货柜情况],
A.VeryImportant AS 定单备注,
B.VeryImportant AS 产品备注
FROM dbo.OrderMaster A INNER JOIN
dbo.OrderDetail B ON A.OurOrderNO = B.OurOrderNO --and B.Qty > 0
WHERE (A.State > 0) AND (A.State <= 7) --确认->装柜确认
ORDER BY 国家,定单号,产品物料代码,允许生产完成日期,实际装柜日期 Asc,B.num
--这样再嵌套一层,试试。
select * from
(SELECT TOP 100 PERCENT
(select CEName from country where CountryCode = A.SellTo) '国家',
A.OurOrderNO AS 定单号,
B.FCode AS 产品物料代码,
B.PCName AS 产品中文名称,
B.Description AS 产品规格,
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) AS [定单数量/箱],
b.CHECKQTY AS [样/赠品],
IsNull(B.QTY,0) AS [实际完成数量],
B.NetWeight / 1000 AS [净重/箱],
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) * B.NetWeight / 1000 AS [总净重/公斤],
A.AllowProducedDate AS 允许生产完成日期,
B.ConfirmDate AS 生产预计完成日期,
A.FactStowagedDate 实际装柜日期,
A.ModeOfShipment AS [货柜情况],
A.VeryImportant AS 定单备注,
B.VeryImportant AS 产品备注
FROM dbo.OrderMaster A INNER JOIN
dbo.OrderDetail B ON A.OurOrderNO = B.OurOrderNO --and B.Qty > 0
WHERE (A.State > 0) AND (A.State <= 7)) b --确认->装柜确认
ORDER BY 国家 COLLATE Chinese_PRC_CI_AS,定单号 COLLATE Chinese_PRC_CI_AS,产品物料代码 COLLATE Chinese_PRC_CI_AS,允许生产完成日期,实际装柜日期 Asc,B.num
国家 Chinese_PRC_CI_AS
定单号 Chinese_PRC_CI_AS
产品物料代码 Chinese_PRC_CI_AS
产品中文名称 Chinese_PRC_CI_AS
产品规格 Chinese_PRC_CI_AS
定单数量/箱 NULL
样/赠品 NULL
实际完成数量 NULL
净重/箱 NULL
总净重/公斤 NULL
允许生产完成日期 NULL
生产预计完成日期 NULL
实际装柜日期 NULL
货柜情况 Chinese_PRC_CI_AS
定单备注 Chinese_PRC_CI_AS
产品备注 Chinese_PRC_CI_AS
SELECT TOP 100 PERCENT
(select CEName from country where CountryCode = A.SellTo) '国家',
A.OurOrderNO AS 定单号,
B.FCode AS 产品物料代码,
B.PCName AS 产品中文名称,
B.Description AS 产品规格,
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) AS [定单数量/箱],
b.CHECKQTY AS [样/赠品],
IsNull(B.QTY,0) AS [实际完成数量],
B.NetWeight / 1000 AS [净重/箱],
(CASE WHEN IsNull(B.QTY,0) =0 THEN IsNull(B.PlanQTY,0) Else B.QTY END) * B.NetWeight / 1000 AS [总净重/公斤],
A.AllowProducedDate AS 允许生产完成日期,
B.ConfirmDate AS 生产预计完成日期,
A.FactStowagedDate 实际装柜日期,
A.ModeOfShipment AS [货柜情况],
A.VeryImportant AS 定单备注,
B.VeryImportant AS 产品备注
FROM dbo.OrderMaster A INNER JOIN
dbo.OrderDetail B ON A.OurOrderNO = B.OurOrderNO --and B.Qty > 0
WHERE (A.State > 0) AND (A.State <= 7) --确认->装柜确认
ORDER BY 国家 COLLATE Chinese_PRC_CI_AS,定单号 COLLATE Chinese_PRC_CI_AS,产品物料代码 COLLATE Chinese_PRC_CI_AS,允许生产完成日期,实际装柜日期 Asc,B.num