一个复杂SQL 语句问题(300)

M

mzr

Unregistered / Unconfirmed
GUEST, unregistred user!
下面这个SQL语句，在SQL Server 2000中调试通过，在ACCESS中不能通过。提示错误：该特定字段'a.kh'可以参考SQL语句中FROM列表子句中的多个表.SELECT TOP 100 PERCENT a.DY, a.PH, a.KH, a.KX, (SELECT COUNT(dz) AS Zh1 FROM View_WmResult b WHERE (a.kh = b.kh) AND (dz < 50) AND (dh <> 'fk')) AS Zh1, (SELECT COUNT(dz) AS Zh2 FROM View_WmResult b WHERE (a.kh = b.kh) AND (dz >= 50) AND (dz < 100) AND (dh <> 'fk')) AS Zh2FROM VIEW_SumWmresult AS aWHERE (a.DY='01')ORDER BY a.DY, a.PH, a.KX, a.KH
 
是不是如下表结构？View_WmResult kh dz dhVIEW_SumWmresultkh dy ph kx你的语句我在access测试通过~~
 
你用的是access 2003吗？
 
View_WMResult和View_SumwmResult都是查询
 
是的~~
 
我的也是,你有邮箱吗，我把数据库发给你，麻烦你帮我看一下
 
QQ:314925688
 
已给你消息，怎么没发过来啊？
 
消息一下子没有看到啊，写在这上面吧[:D]
 
帮你顶，接个小分
 
SELECT TOP 100 PERCENT a.DY, a.PH, a.KH, a.KX, (SELECT COUNT(dz) AS Zh1 FROM View_WmResult b[red],VIEW_SumWmresult [/red]WHERE ([blue]VIEW_SumWmresult[/blue].kh = b.kh) AND (dz < 50) AND (dh <> 'fk')) AS Zh1, (SELECT COUNT(dz) AS Zh2 FROM View_WmResult b[red],VIEW_SumWmresult [/red]WHERE ([blue]VIEW_SumWmresult[/blue].kh = b.kh) AND (dz >= 50) AND (dz < 100) AND (dh <> 'fk')) AS Zh2FROM VIEW_SumWmresult AS aWHERE (a.DY='01')ORDER BY a.DY, a.PH, a.KX, a.KH;测试通过~~
 
语句测试通过，但结果不对，ZH1，ZH2字段每条记录数字都相同
 
SELECT COUNT(dz) AS Zh2 FROM View_WmResult b,VIEW_SumWmresult WHERE (VIEW_SumWmresult.kh = b.kh) AND (dz >= 50) AND (dz < 100) AND (dh <> 'fk')结果为0SELECT COUNT(dz) AS Zh1FROM View_WmResult AS b, VIEW_SumWmresultWHERE (b.kh=VIEW_SumWmresult.kh) And (dz<50) And (dh<>'fk');结果仍为0以上两句，我只是把你的语句分拆而已~~
 
关键是如何让 View_SumWmResult.KH与View_WmResult.kh建立起一一对应的关联
 
能发个数据库给吗？hctdnjy@163.com没数据不好测。
 
已发，hctdnjy@163.com，请查收
 
SELECT TOP 100 PERCENT a.DY, a.PH, a.KH, a.KX, (SELECT Count(dz) AS Zh1FROM VIEW_SumWmresult INNER JOIN View_WmResult AS d ON VIEW_SumWmresult.KH = d.KHWHERE (((d.KH)=[VIEW_SumWmresult].[kh]) AND ((d.dz)<50) AND ((d.DH)<>'fk'))) AS Zh1, (SELECT Count(dz) AS Zh2FROM VIEW_SumWmresult INNER JOIN View_WmResult AS b ON VIEW_SumWmresult.KH = b.KHWHERE (((VIEW_SumWmresult.KH)=.[kh]) AND ((b.dz)>=50 And (b.dz)<100) AND ((b.DH)<>'fk'))) AS Zh2FROM VIEW_SumWmresult AS aWHERE (a.DY='01')ORDER BY a.DY, a.PH, a.KX, kh;是不是这个效果？兄弟，你的VIEW_SumWmresult、VIEW_SumWmresult需要优化一下~~打开它们很慢啊~~
 
下面是正确结果：dy ph kh kx zh1 zh201 下游排 wm-01 I序孔 0 201 下游排 wm-02 III序孔 1 001 下游排 wm-03 II序孔 0 0
 
而你的结果是：肯定不对啊。dy ph kh kx zh1 zh201 下游排 wm-01 I序孔 1 201 下游排 wm-02 III序孔 1 201 下游排 wm-03 II序孔 1 2
 
查询View_SumWmResult记录如下：dy ph kh kx 01 下游排 wm-01 I序孔 01 下游排 wm-02 III序孔 01 下游排 wm-03 II序孔 查询View_WmResult记录如下：KH dh dzwm-01 01 767.33wm-01 02 906.4wm-01 03 727.8wm-01 04 179.6wm-01 05 130.8wm-01 06 69wm-01 07 51.33wm-01 fk 4.84wm-02 01 760.44wm-02 02 246.4wm-02 03 44.4wm-02 fk 8.13wm-03 01 120.66主要是把各KH的dz的分布计算出来，然后增加到View_SumWmresult的ZH1,ZH2字段中，ZH1为dz<50的个数，Zh2 为50<= dz <100的数最后结果是：dy ph kh kx zh1 zh201 下游排 wm-01 I序孔 0 201 下游排 wm-02 III序孔 1 001 下游排 wm-03 II序孔 0 0
 
您必须登录或注册才可回复。

Similar threads

S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
842
SUNSTONE的Delphi笔记
S
Q
帮忙写一个SQL语句(100)
回复
8
查看
499
antonyzhang
A
D
SQL SERVER查询问题(100)
回复
7
查看
437
zzbubble
Z
顶部