求解SQL语句(100分)

liuchong · 2008-02-26

在SQL Server2000中 以下SQL语句能正确执行： --------------------------------------------------- SELECT E.日期, E.商品类别, E.商品名称, E.商品型号, (CASE WHEN SUM(E.进货数量)>0 THEN ROUND(SUM(E.进货单价*E.进货数量)/SUM(E.进货数量),4) ELSE 0 END) AS 进货单价, SUM(E.进货数量) AS 进货数量, SUM(E.进货总价) AS 进货总价, ROUND(SUM(E.销售单价*(CASE WHEN E.销售数量=0 THEN 1 ELSE E.销售数量 END))/(CASE WHEN SUM(E.销售数量)=0 THEN 1 ELSE SUM(E.销售数量) END),4) AS 销售单价, SUM(E.销售数量) AS 销售数量,SUM(E.销售总价) AS 销售总价, SUM(E.利润) AS 利润,SUM(E.向供货商返货数量) AS 向供货商返货数量 FROM ( SELECT X.A1 AS 日期,Y.A1 AS 商品类别,Y.A2 AS 商品名称, Y.A3 AS 商品型号,Y.A5 AS 进货单价, (CASE WHEN Y.A4>0 THEN Y.A4 ELSE 0 END) AS 进货数量,(CASE WHEN Y.A4>0 THEN ROUND(Y.A5*Y.A4,4) ELSE 0 END) AS 进货总价, 0 AS 销售单价,0 AS 销售数量,0 AS 销售总价,(CASE WHEN A4<0 THEN -A4 ELSE 0 END) AS 向供货商返货数量,0 AS 利润 FROM AA X,AAA Y WHERE (X.ID=Y.MID) AND (X.A1 BETWEEN '2008-02-26' AND '2008-02-26') UNION ALL SELECT C.B1 AS 日期,D.B1 AS 商品类别,D.B2 AS 商品名称, D.B3 AS 商品型号, 0 AS 进货单价, 0 AS 进货数量,0 AS 进货总价,(CASE WHEN B4>0 THEN ROUND(B5/B4,4) ELSE B5 END) AS 销售单价, ISNULL(B4,0) AS 销售数量,ISNULL(B5,0) AS 销售总价, 0 AS 向供货商返货数量, ROUND(B5-B6*B4,4) AS 利润 FROM BB C,BBB D WHERE (C.ID=D.MID) AND (C.B1 BETWEEN '2008-02-26' AND '2008-02-26') ) AS E GROUP BY E.日期,E.商品类别,E.商品名称,E.商品型号 ORDER BY E.日期 -------------------------------------------------------------------------------- 在最外层加入一个SELECT之后，就出错了，错提示：“除非指定了TOP，否则ORDER BY子句在视图，内嵌函数，派生表和子查询中无效” 代码如下： ------------------------------------------------------------- SELECT 日期,商品类别,商品名称,商品型号,CC.C7 AS 单位,进货单价, 进货数量,进货总价,销售单价,销售数量,销售总价,利润,向供货商返货数量 FROM ( SELECT E.日期, E.商品类别, E.商品名称, E.商品型号, (CASE WHEN SUM(E.进货数量)>0 THEN ROUND(SUM(E.进货单价*E.进货数量)/SUM(E.进货数量),4) ELSE 0 END) AS 进货单价, SUM(E.进货数量) AS 进货数量, SUM(E.进货总价) AS 进货总价, ROUND(SUM(E.销售单价*(CASE WHEN E.销售数量=0 THEN 1 ELSE E.销售数量 END))/(CASE WHEN SUM(E.销售数量)=0 THEN 1 ELSE SUM(E.销售数量) END),4) AS 销售单价, SUM(E.销售数量) AS 销售数量,SUM(E.销售总价) AS 销售总价, SUM(E.利润) AS 利润,SUM(E.向供货商返货数量) AS 向供货商返货数量 FROM ( SELECT X.A1 AS 日期,Y.A1 AS 商品类别,Y.A2 AS 商品名称, Y.A3 AS 商品型号,Y.A5 AS 进货单价, (CASE WHEN Y.A4>0 THEN Y.A4 ELSE 0 END) AS 进货数量,(CASE WHEN Y.A4>0 THEN ROUND(Y.A5*Y.A4,4) ELSE 0 END) AS 进货总价, 0 AS 销售单价,0 AS 销售数量,0 AS 销售总价,(CASE WHEN A4<0 THEN -A4 ELSE 0 END) AS 向供货商返货数量,0 AS 利润 FROM AA X,AAA Y WHERE (X.ID=Y.MID) AND (X.A1 BETWEEN '2008-02-26' AND '2008-02-26') UNION ALL SELECT C.B1 AS 日期,D.B1 AS 商品类别,D.B2 AS 商品名称, D.B3 AS 商品型号, 0 AS 进货单价, 0 AS 进货数量,0 AS 进货总价,(CASE WHEN B4>0 THEN ROUND(B5/B4,4) ELSE B5 END) AS 销售单价, ISNULL(B4,0) AS 销售数量,ISNULL(B5,0) AS 销售总价, 0 AS 向供货商返货数量, ROUND(B5-B6*B4,4) AS 利润 FROM BB C,BBB D WHERE (C.ID=D.MID) AND (C.B1 BETWEEN '2008-02-26' AND '2008-02-26') ) AS E GROUP BY E.日期,E.商品类别,E.商品名称,E.商品型号 ORDER BY E.日期 ),CC WHERE (商品类别=CC.C1) AND (商品名称=CC.C2) AND (商品型号=CC.C3)

liuchong · 2008-02-26

在外层随便写： ------------------------------------ SELECT 日期 FROM ( SELECT E.日期, E.商品类别, E.商品名称, E.商品型号, (CASE WHEN SUM(E.进货数量)>0 THEN ROUND(SUM(E.进货单价*E.进货数量)/SUM(E.进货数量),4) ELSE 0 END) AS 进货单价, SUM(E.进货数量) AS 进货数量, SUM(E.进货总价) AS 进货总价, ROUND(SUM(E.销售单价*(CASE WHEN E.销售数量=0 THEN 1 ELSE E.销售数量 END))/(CASE WHEN SUM(E.销售数量)=0 THEN 1 ELSE SUM(E.销售数量) END),4) AS 销售单价, SUM(E.销售数量) AS 销售数量,SUM(E.销售总价) AS 销售总价, SUM(E.利润) AS 利润,SUM(E.向供货商返货数量) AS 向供货商返货数量 FROM ( SELECT X.A1 AS 日期,Y.A1 AS 商品类别,Y.A2 AS 商品名称, Y.A3 AS 商品型号,Y.A5 AS 进货单价, (CASE WHEN Y.A4>0 THEN Y.A4 ELSE 0 END) AS 进货数量,(CASE WHEN Y.A4>0 THEN ROUND(Y.A5*Y.A4,4) ELSE 0 END) AS 进货总价, 0 AS 销售单价,0 AS 销售数量,0 AS 销售总价,(CASE WHEN A4<0 THEN -A4 ELSE 0 END) AS 向供货商返货数量,0 AS 利润 FROM AA X,AAA Y WHERE (X.ID=Y.MID) AND (X.A1 BETWEEN '2008-02-26' AND '2008-02-26') UNION ALL SELECT C.B1 AS 日期,D.B1 AS 商品类别,D.B2 AS 商品名称, D.B3 AS 商品型号, 0 AS 进货单价, 0 AS 进货数量,0 AS 进货总价,(CASE WHEN B4>0 THEN ROUND(B5/B4,4) ELSE B5 END) AS 销售单价, ISNULL(B4,0) AS 销售数量,ISNULL(B5,0) AS 销售总价, 0 AS 向供货商返货数量, ROUND(B5-B6*B4,4) AS 利润 FROM BB C,BBB D WHERE (C.ID=D.MID) AND (C.B1 BETWEEN '2008-02-26' AND '2008-02-26') ) AS E GROUP BY E.日期,E.商品类别,E.商品名称,E.商品型号 ORDER BY E.日期 ) ------------------------------- 这样也出错

轻舞肥羊 · 2008-02-26

错误提示已经给出原因了啊 order by 不能出现在不包含top子句的子查询中 把它移到最外层

china_peng · 2008-02-26

提示已经很明白的告诉你了，解决办法： 1.去掉 ORDER BY E.日期 2.如果你非要保留 ORDER BY E.日期 请在最外一层的 SELECT 之后加上 [red]TOP 100 PERCENT[/red]

gotiger · 2008-02-26

没耐心看,换个提问方式

liuchong · 2008-02-27

SELECT 日期,商品类别,商品名称,商品型号,CC.C7 AS 单位,进货单价, 进货数量,进货总价,销售单价,销售数量,销售总价,利润,向供货商返货数量 FROM ( SELECT E.日期, E.商品类别, E.商品名称, E.商品型号, (CASE WHEN SUM(E.进货数量)>0 THEN ROUND(SUM(E.进货单价*E.进货数量)/SUM(E.进货数量),4) ELSE 0 END) AS 进货单价, SUM(E.进货数量) AS 进货数量, SUM(E.进货总价) AS 进货总价, ROUND(SUM(E.销售单价*(CASE WHEN E.销售数量=0 THEN 1 ELSE E.销售数量 END))/(CASE WHEN SUM(E.销售数量)=0 THEN 1 ELSE SUM(E.销售数量) END),4) AS 销售单价, SUM(E.销售数量) AS 销售数量,SUM(E.销售总价) AS 销售总价, SUM(E.利润) AS 利润,SUM(E.向供货商返货数量) AS 向供货商返货数量 FROM ( SELECT X.A1 AS 日期,Y.A1 AS 商品类别,Y.A2 AS 商品名称, Y.A3 AS 商品型号,Y.A5 AS 进货单价, (CASE WHEN Y.A4>0 THEN Y.A4 ELSE 0 END) AS 进货数量,(CASE WHEN Y.A4>0 THEN ROUND(Y.A5*Y.A4,4) ELSE 0 END) AS 进货总价, 0 AS 销售单价,0 AS 销售数量,0 AS 销售总价,(CASE WHEN A4<0 THEN -A4 ELSE 0 END) AS 向供货商返货数量,0 AS 利润 FROM AA X,AAA Y WHERE (X.ID=Y.MID) AND (X.A1 BETWEEN '2008-02-26' AND '2008-02-26') UNION ALL SELECT C.B1 AS 日期,D.B1 AS 商品类别,D.B2 AS 商品名称, D.B3 AS 商品型号, 0 AS 进货单价, 0 AS 进货数量,0 AS 进货总价,(CASE WHEN B4>0 THEN ROUND(B5/B4,4) ELSE B5 END) AS 销售单价, ISNULL(B4,0) AS 销售数量,ISNULL(B5,0) AS 销售总价, 0 AS 向供货商返货数量, ROUND(B5-B6*B4,4) AS 利润 FROM BB C,BBB D WHERE (C.ID=D.MID) AND (C.B1 BETWEEN '2008-02-26' AND '2008-02-26') ) AS E GROUP BY E.日期,E.商品类别,E.商品名称,E.商品型号 )TTT,CC WHERE (商品类别=CC.C1) AND (商品名称=CC.C2) AND (商品型号=CC.C3) ORDER BY 日期

求解SQL语句(100分)

liuchong

Unregistered / Unconfirmed

liuchong

Unregistered / Unconfirmed

轻舞肥羊

Unregistered / Unconfirmed

china_peng

Unregistered / Unconfirmed

gotiger

Unregistered / Unconfirmed

liuchong

Unregistered / Unconfirmed

Similar threads