请帮忙实现这种查询

Question

------------------------------------------ one 视图开始------------------------------------------------------SELECT dbo.A_Product.Product_Name AS Expr1, dbo.A_Record.Record_Name AS Expr2, 
      dbo.employee.xm AS Expr3, dbo.employee.bm AS Expr5, dbo.A_days.ID, 
      dbo.A_days.bh, dbo.A_days.jihua, dbo.A_days.touru, dbo.A_days.lianpin, 
      dbo.A_days.kefanxiu, dbo.A_days.baofei, dbo.A_days.chae, dbo.A_days.dacheng, 
      dbo.A_days.jixiaogongshi, dbo.A_days.feijixiao, dbo.A_days.jihualv, 
      dbo.A_days.tourulv, dbo.A_days.lianpinlv, dbo.A_days.kefanxiulv, dbo.A_days.baoflv, 
      dbo.A_days.chaelv, dbo.A_days.dachenglv, dbo.A_days.rq, 
      dbo.A_Step.Step_Name
FROM dbo.A_days INNER JOIN
      dbo.A_Record ON dbo.A_days.Record_ID = dbo.A_Record.Record_ID INNER JOIN
      dbo.A_Product ON dbo.A_Record.Product_ID = dbo.A_Product.Product_ID INNER JOIN
      dbo.employee ON dbo.A_days.bh = dbo.employee.bh INNER JOIN
      dbo.A_Step ON dbo.A_days.Step_ID = dbo.A_Step.Step_ID
-------------------------------------------------------------------------------------- one 视图结束------------------------select * from one 后查询以下列:ID  Expr1   Expr2   EXpr3    Expr4    bh   jihua    touru     lianpin    kefanxiu    baofei  chae    dacheng   date  //后面省掉一些....-----
/* bh   jihua    touru     lianpin    kefanxiu    baofei  chae    dacheng   都是numreic类型 精度10,小数两位
/*Expr1   Expr2   EXpr3    Expr4    是外键
---------------------------------------------------------------------------------------------------------------------------我要查询以下列:
ID  Expr1   Expr2   EXpr3    Expr4    bh   jihua    touru     lianpin    kefanxiu    baofei  chae    dacheng   date  jihualv //后面省掉一些....还要
jihua touru     lianpin    kefanxiu    baofei  的合计, 就是sum(jihua)....    还有 jihualv的平均值,也就是 avg(jihualv)除合计外还要同时查询出select * from one本身查询出的东西,也就是没sum()列合计的单行数据.原因:因为在asp里一个CONN进程打开之后,只能查询一次.我要这些数据的详细列表。还要这些列表的总和.用asp来统计列的总和,分页后他只能计算到单前页的.请朋友们帮拖..多谢!实在劳烦大家,这几天发了很多这样的帖子.不过技术也好了一点,多谢大家之前的帮助.在此感谢!

Answer 1

------------------------------------------   one   视图开始------------------------------------------------------ SELECT   dbo.A_Product.Product_Name   AS   Expr1,   dbo.A_Record.Record_Name   AS   Expr2,   
            dbo.employee.xm   AS   Expr3,   dbo.employee.bm   AS   Expr5,   dbo.A_days.ID,   
            dbo.A_days.bh,   dbo.A_days.jihua,   dbo.A_days.touru,   dbo.A_days.lianpin,   
            dbo.A_days.kefanxiu,   dbo.A_days.baofei,   dbo.A_days.chae,   dbo.A_days.dacheng,   
            dbo.A_days.jixiaogongshi,   dbo.A_days.feijixiao,   dbo.A_days.jihualv,   
            dbo.A_days.tourulv,   dbo.A_days.lianpinlv,   dbo.A_days.kefanxiulv,   dbo.A_days.baoflv,   
            dbo.A_days.chaelv,   dbo.A_days.dachenglv,   dbo.A_days.rq,   
            dbo.A_Step.Step_Name 
FROM   dbo.A_days   INNER   JOIN 
            dbo.A_Record   ON   dbo.A_days.Record_ID   =   dbo.A_Record.Record_ID   INNER   JOIN 
            dbo.A_Product   ON   dbo.A_Record.Product_ID   =   dbo.A_Product.Product_ID   INNER   JOIN 
            dbo.employee   ON   dbo.A_days.bh   =   dbo.employee.bh   INNER   JOIN 
            dbo.A_Step   ON   dbo.A_days.Step_ID   =   dbo.A_Step.Step_ID 
--------------------------------------------------------------------------------------   one   视图结束------------------------ select   *   from   one   后查询以下列: ID     Expr1       Expr2       EXpr3         Expr4         bh       jihua         touru           lianpin         kefanxiu         baofei     chae         dacheng       date     //后面省掉一些.... ----- 
/*   bh       jihua         touru           lianpin         kefanxiu         baofei     chae         dacheng       都是numreic类型   精度10,小数两位 
/*Expr1       Expr2       EXpr3         Expr4         是外键 
--------------------------------------------------------------------------------------------------------------------------- 我要查询以下列: 
ID     Expr1       Expr2       EXpr3         Expr4         bh       jihua         touru           lianpin         kefanxiu         baofei     chae         dacheng       date     jihualv   //后面省掉一些.... 还要 
jihua   touru           lianpin         kefanxiu         baofei     的合计,   就是sum(jihua)....         还有   jihualv的平均值,也就是   avg(jihualv) 除合计外还要同时查询出select   *   from   one本身查询出的东西,也就是没sum()列合计的单行数据. 
原因: 因为在asp里一个CONN进程打开之后,只能查询一次.我要这些数据的详细列表。还要这些列表的总和. 用asp来统计列的总和,分页后他只能计算到单前页的. 请朋友们帮拖..多谢! 实在劳烦大家,这几天发了很多这样的帖子.不过技术也好了一点,多谢大家之前的帮助.在此感谢! 

Answer 2

灵魂漫步,你起的真早.SELECT jihua, lianpin,jihualv,
          (SELECT SUM(jihua)
         FROM one where bh = 4272) AS [jh],
          (SELECT SUM(lianpin)
         FROM one where bh = 4272) AS [lp],
          (SELECT avg(jihualv)
         FROM one where bh = 4272) AS [jhlv]
FROM dbo.one where bh = 4272
上面这段可以,但是放在ASP里.得到的值是错的.

Answer 3

上面这段可以, 但是放在ASP里.得到的值是错的.
--------------------------
不是起的早，是到了上班的点。没法，不能不早```
放在SQL里面没错，在asp里面得到的值错了？不过你这语句看起来怪怪的。。
应该是这样吧？
select jihua, lianpin,jihualv,sum(jihua) [jh],sum(lianpin)[lp],avg(jihualv)[jhlv]
from one where bh = 4272 group by jihua, lianpin,jihualv

Answer 4

大家看看上面的合计,怎么多了这么多?'''''''''''''''''''''''''''''读取查询值的SUM合计值''''''''''''''''''''''''''''''''''''''''''''''''
%>
<%
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = server.CreateObject("adodb.recordset")
strsql="PROVIDER=SQLOLEDB;DATA SOURCE=(local);UID=sa;PWD=8968;DATABASE=g3"
conn.Open strsqlsql="SELECT jihua, lianpin,Expr1,expr2,expr3,expr5,id,                    "&_
"       bh,kefanxiu,baofei,jixiaogongshi ,feijixiao,rq,                    "&_
"      chae,dacheng,jixiaogongshi,feijixiao,                              "&_
"     jihualv,lianpinlv,kefanxiulv,baoflv,chaelv,dachenglv,              "&_
"    rq,step_name,touru,tourulv,                                        "&_
"      (SELECT SUM(jihua)                                              "&_
"               FROM one ) AS [jh],                                              "&_
"     (SELECT SUM(lianpin)                                            "&_
"               FROM one ) AS [lp],                                               "&_
"  (SELECT avg(jihualv)                                             "&_
"                FROM one ) AS [jhlv],                                             "&_
"  (SELECT sum(kefanxiu)                                             "&_
"                FROM one ) AS [kfx],                                              "&_
"  (SELECT sum(baofei)                                             "&_
"                FROM one ) AS [bf],                                            "&_
"  (SELECT sum(touru)                                             "&_
"                FROM one ) AS [tr] ,                                             "&_
"  (SELECT sum(jixiaogongshi)                                             "&_
"                FROM one ) AS [jxgs] ,                                             "&_
"  (SELECT sum(feijixiao)                                             "&_
"                FROM one ) AS [fjx]                                              "&_
"          FROM dbo.one "ASP里的代码
%>SQL里的值不是这样的

Answer 5

我写的在 查询分析器里是对的值.但是我放在ASP值大了N倍.都看不出这个值怎么得出来的,

Answer 6

SELECT SUM(jihua) AS jh  FROM one 
如果单独写这一句,值就不会错

Answer 7

原因是这样写了就会出错。.     
SELECT  (SELECT SUM(jihua) FROM one ) AS [jh] FROM dbo.one 

Answer 8

SELECT     (SELECT   SUM(jihua)   FROM   one   )   AS   [jh]   FROM   dbo.one    查到了..  问题所在了.