我用
with
a as
( select *..)
,
b as
( select *..)
where a.xx=b.xx...
这样得到一个结果集,可是没法
select * from
(
with
a as
( select *..)
,
b as
( select *..)
where a.xx=b.xx...)
这样用
没法这样用的话也就没法把这个结果集放到临时表里面去处理了
with
a as
( select *..)
,
b as
( select *..)
where a.xx=b.xx...
这样得到一个结果集,可是没法
select * from
(
with
a as
( select *..)
,
b as
( select *..)
where a.xx=b.xx...)
这样用
没法这样用的话也就没法把这个结果集放到临时表里面去处理了
with a as
( select *..)
,
b as
( select *..)
where a.xx=b.xx...--使用
select * from a
as();with b
as()
select * from a
select * from b
a和b只是我用with定义的两个结果集,类似子表
然后我需要根据a和b的关联条件再得到一个结果集select * from a或者是b
不就是我定义的
with a as (...)这个处理前的结果集么?
a.xx=b.xx之后的这个a和b的一个子集啊
with a as
( select *..),
b as
( select *..)SELECT * FROM a,b where a.xx=b.xx
可是我现在要做的不仅仅是算出这个结果集,我真正需要的是类似
insert into #temp
select * from
(
with a as
( select *..),
b as
( select *..) SELECT * FROM a,b where a.xx=b.xx)这样的,也就是说“SELECT * FROM a,b where a.xx=b.xx”的这个结果集,我需要把放到游标里面,或者插入临时表,然后还需要再做进一步的处理
可是现在with定义的东西好像没法再做处理的
1. “视图”:跟视图一样,可以在查询中直接SELECT,实际是读取视力声明中的表。
2. “临时”:这个所谓视图只能用于这一个语句。
; --因为SQLServer中已经有了WITH子句,为了能够识别这个WITH是声明CTE的而不是上一个语句的子句,WITH需要在前面加“;”,除非这是批或块里的第一个语句。
WITH CTE1 AS (...),
CTE2 AS (...), --CTE2的定义中可以引用CTE1
CTE3 AS (...), --CTE3的定义中可以引用CTE1和CTE2
...
CTEn AS (...) --最后一个CTE后面不要逗号
SELECT * FROM CTE1,CTE2,CTE3,...CTEn --这里可以使用上面声明的所有CTE。
SELECT * FROM CTE1,CTE2,CTE3,...CTEn --CTE只能用于一个语句,这里再使用就会报错。
with a as
( select *..),
b as
( select *..)
SELECT a.*,b.x FROM a,b where a.xx=b.xx
可以,有一个结果集,可是我这样SELECT a.*,b.x FROM a,b
from
(
select *..
)a,
(
select *..)b
where a.xx=b.xx
就不行,说第一列对象名b无效。愁死了
with a as
( select *..),
b as
( select *..)
SELECT *
INTO #temp
FROM a,b
where a.xx=b.xx
GROUP BY ...
ORDER BY ...
使用CTE的查询,除了SELECT头上多了一个WITH,其他部分跟一般的SELECT完全一样。
aa as
( select tp.ob_seccode_0007 as ob_seccode_0007,--'证券代码',
tp.ob_secid_0007 as ob_secid_0007, --证劵id,关联用
tp.ob_secname_0007 as '证券简称',
tt.f002n_0160 as f002n_0160,--'昨日收盘',
dateadd(day,1,ob_tradedate_0160) as ob_tradedate_0160 --'交易日期'
from juchao.cpdb.dbo.tb_public_0007 tp,juchao.cpdb.dbo.tb_trade_0160 tt
where tt.ob_tradedate_0160>'2010-05-26 00:00:00'
and tp.ob_seccode_0007 = tt.ob_seccode_0160 --证券代码
),
bb as
(
select tc.ob_orgid_0290,tc.f001d_0290,(isnull(tc.f070n_0290,0)-isnull(tc.f067n_0290,0)) as equity
from juchao.cpdb.dbo.tb_company_0290 tc
where f003v_0290='合并本期'
),
cc as
(
select tcp.ob_orgid_0020,tcp.f003n_0020,tcp.ob_varydate_0020
from juchao.cpdb.dbo.tb_company_0020 tcp
)
select aa.*,
(select top 1 equity from bb where bb.ob_orgid_0290=aa.ob_secid_0007 and bb.f001d_0290<aa.ob_tradedate_0160 order by bb.f001d_0290 desc) as equity,
(select top 1 f001d_0290 from bb where bb.ob_orgid_0290=aa.ob_secid_0007 and bb.f001d_0290<aa.ob_tradedate_0160 order by bb.f001d_0290 desc) as equitydate,
(select top 1 f003n_0020 from cc where cc.ob_orgid_0020=aa.ob_secid_0007 and cc.ob_varydate_0020<aa.ob_tradedate_0160 order by cc.ob_varydate_0020 desc) as total,
(select top 1 ob_varydate_0020 from cc where cc.ob_orgid_0020=aa.ob_secid_0007 and cc.ob_varydate_0020<aa.ob_tradedate_0160 order by cc.ob_varydate_0020 desc) as totaldate
这样得到的结果集是没问题的,可是当我反过来写之后:
select aa.*,
(select top 1 equity from bb where bb.ob_orgid_0290=aa.ob_secid_0007 and bb.f001d_0290<aa.ob_tradedate_0160 order by bb.f001d_0290 desc) as equity,
(select top 1 f001d_0290 from bb where bb.ob_orgid_0290=aa.ob_secid_0007 and bb.f001d_0290<aa.ob_tradedate_0160 order by bb.f001d_0290 desc) as equitydate,
(select top 1 f003n_0020 from cc where cc.ob_orgid_0020=aa.ob_secid_0007 and cc.ob_varydate_0020<aa.ob_tradedate_0160 order by cc.ob_varydate_0020 desc) as total,
(select top 1 ob_varydate_0020 from cc where cc.ob_orgid_0020=aa.ob_secid_0007 and cc.ob_varydate_0020<aa.ob_tradedate_0160 order by cc.ob_varydate_0020 desc) as totaldate
from
( select tp.ob_seccode_0007 as ob_seccode_0007,--'证券代码',
tp.ob_secid_0007 as ob_secid_0007, --证劵id,关联用
tp.ob_secname_0007 as '证券简称',
tt.f002n_0160 as f002n_0160,--'昨日收盘',
dateadd(day,1,ob_tradedate_0160) as ob_tradedate_0160 --'交易日期'
from juchao.cpdb.dbo.tb_public_0007 tp,juchao.cpdb.dbo.tb_trade_0160 tt
where tt.ob_tradedate_0160>'2010-05-26 00:00:00'
and tp.ob_seccode_0007 = tt.ob_seccode_0160 --证券代码
)aa,
(
select tc.ob_orgid_0290,tc.f001d_0290,(isnull(tc.f070n_0290,0)-isnull(tc.f067n_0290,0)) as equity
from juchao.cpdb.dbo.tb_company_0290 tc
where f003v_0290='合并本期'
)bb,
(
select tcp.ob_orgid_0020,tcp.f003n_0020,tcp.ob_varydate_0020
from juchao.cpdb.dbo.tb_company_0020 tcp
)cc
它就说我
消息 208,级别 16,状态 1,第 1 行
对象名 'bb' 无效。怎么办啊??
SELECT a.*,b.x
INTO #temp
from
(
select *..
) a,
(
select *.. --这里不能使用a
) b
where a.xx=b.xx
这样也行。只是在声明b的子查询里不能使用a。
INTO #temp
这个语法不对吧?
我写了报错说
“数据库中已存在名为 '#temp_stock' 的对象。”
select *....
这样就可以了,谢谢
bb.xx --可以引用bb的字段
subquery_from_bb = (SELECT TOP(1) bb.xx FROM bb WHERE ...) --这个子查询中不知道bb是什么
FROM
( ... ) aa,
( ... ) bb --这个派生表的别名bb只能在这一级的SELECT语句中使用,高一级或低一级都不行。
WHERE ...
没有编码规范的代码看起来真累。
--不存在#temp,根据查询结果生成临时表并插入数据
SELECT ...
INTO #temp
FROM ...--存在#temp,将查询结果插入临时表
INSERT INTO #temp
SELECT ...
FROM ...
能不能推荐几本sql语句的书啊
2. 用FROM a JOIN b ON a.xx = b.xx代替FROM a,b WHERE a.xx = b.xx。把联接条件和查询条件放在不同位置,代码逻辑更清晰。且JOIN的功能更强(支持外联接、联接提示)。
3. 尽量不要在SELECT子句中用子查询,效率很低。
入门:《SQL Server 2005数据库管理与应用高手修炼指南》
精通:《SQL Server 2005技术内幕》4本(至少前两本是必需的)、《SQL Server 2005性能调校》
参考:联机帮助更多请参考我的数据库书单:http://book.douban.com/doulist/257824/
确实需要好好的读读t-sql的书