SQL 为什么不能对TRANSFORM结果进行再查询

visualPaul

好像不能对Transform进行再次查询，有何办法解决？

或者有没有办法不用Transform，用别的SQL语句实现得到我要的结果呢
见附件

sunsoncheng

http://club.excelhome.net/viewth ... ight=%2Bsunsoncheng

这是我想申请加精却被拒绝的，

个人认为，变通后可达到你要的结果

gly1126

这样?
transform sum(pj.pjz+cyw) SELECT yb.CCY from (SELECT LEFT(RIC,3) AS CCY,IIF(MID(RIC,4,LEN(RIC)-4)='','SPOT',MID(RIC,4,LEN(RIC)-4)) as fl,iif(fl='SPOT',0,(BID+ASK)/2/10000) as cyw  FROM [Data$]) as yb,(SELECT LEFT(RIC,3) AS CCY,(BID+ASK)/2 as pjz from [Data$] where MID(RIC,4,LEN(RIC)-4)='') as pj where yb.ccy=pj.ccy group by yb.ccy pivot fl

visualPaul

学习学习啦，都是高手啊。 看来我思维太僵化了，以后得好好练练

另外，还有个与Transform相关的问题，语法中 有PIVOT。。。IN 。。。
为什么我试验中，IN后面只能使用常量，而不能用 IN (SELECT .... FROM ....)，也就是为什么在这里的IN后面不能是一个自查询

gly1126

Public Function TakeList(Rst As ADODB.Recordset)
    TakeList = "'" & Rst.GetString(, , , "','") & "'"
End Function
Sub test()
    Dim conn As New ADODB.Connection
    Dim Rst As New ADODB.Recordset
    conn.Open "dsn=mychgl"
    Set Rst = conn.Execute("select 存货编号 from chzd where 存货编号 like '0001%'")
    Set Rst = conn.Execute("select 存货名称 from chzd where 存货编号 in(" & TakeList(Rst) & ")")
    MsgBox TakeList(Rst)
End Sub

Scarlett_88

我的SQL语句如下：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col,sum(0) as data3
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
group by CCY
PIVOT col

detergent181

SQL可以这样自定义函数,谢谢.