SQL2005排序问题

[ck_邬]

现在遇到个怪问题,
SQL2005的排序规则是SQL_Latin1_General_CP1_CI_AS,
而我建的数据库排序用的是Chinese_PRC_CI_AS,
我建了一个视图的内容如下:

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

我界面用的cxGrid,现在界面里,点击'国家'排序,定单号就会出现分离情况,就是同一国家,同一单号的没有走在一起,
现在找不着方法了,不知道怎么回事,
如果我SQL2005用的Chinese_PRC_CI_AS貌似就没有问题,但实际的环境中不允许我把SQL2005的排序变为Chinese_PRC_CI_AS,
请各位大大帮我看一下啊~

[xiaowei_001]

关注..

[olddown]

放到临时表或者内存中再排序呢？

[ai_li7758521]

点【国家】？什么意思

[sdhdy]

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 国家 COLLATE Chinese_PRC_CI_AS,定单号 COLLATE Chinese_PRC_CI_AS,产品物料代码,允许生产完成日期,实际装柜日期 Asc,B.num

[sdhdy]

把排序规则放到 order by 的字段后面。

[ck_邬]

[Quote=引用 4 楼 sdhdy 的回复:]
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…
[/Quote]
你这个提示
消息 207，级别 16，状态 1，过程 v_rpDeliveryPlan_OEE，第 41 行
列名 '国家' 无效。
消息 207，级别 16，状态 1，过程 v_rpDeliveryPlan_OEE，第 41 行
列名 '定单号' 无效。

[ck_邬]

[Quote=引用 3 楼 ai_li7758521 的回复:]
点【国家】？什么意思
[/Quote]
我的控件点一下列点就会自动排序的,
意思就是我点一下国家,他会自动按国家排序,然后再按第二列的列名排,如此类推..

[sdhdy]

--try:

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 

[ck_邬]

[Quote=引用 8 楼 sdhdy 的回复:]
--try:

SQL codeSELECT 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 [样/赠品],
IsNu…
[/Quote]
还是一样的出错信息啊~

[ck_邬]

我用sp_help查看这个视图。。结果如下：

国家		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

[sdhdy]

不是一个连接查询吗？怎么又变成视图了？

--这样再嵌套一层，试试。
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 

[sdhdy]

你直接从这个视图，order by 呢？
[Quote=引用 10 楼 chadwick 的回复:]
我用sp_help查看这个视图。。结果如下：


SQL code
国家 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
实际装柜日期 N…
[/Quote]