小白求助!!!

AcHerat 元老 2010-12-09 09:51:21


create table #r(customer int,amount decimal(12,2))
insert into #r
select customer,sum(totalAmount) as amount
from DM_DeliveryOrder a
where a.company in (select id from t)
and convert(varchar(7),a.createTime,120) = convert(varchar(7),'2010-10-12',120)
go

declare @id int
set @id = 6
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)

select * from #r
drop table #r




报错:对象名 't' 无效。

应该如何写,将t作为临时表的查询条件。
...全文
78 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
AcHerat 元老 2010-12-09
  • 打赏
  • 举报
回复
哦啦,问题解决,结贴!
dawugui 2010-12-09
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 acherat 的回复:]
两对象名t无效!!![/Quote]
不知道你怎么在用,参考如下例的用法.

/*
标题:SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-02-02
地点:新疆乌鲁木齐
*/

create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
go

DECLARE @ID VARCHAR(3)

--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇

(10 行受影响)
*/

--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
002 001 广州市
004 002 天河区

(2 行受影响)
*/

--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇

(7 行受影响)
*/

drop table tb

--注:除ID值不一样外,三个SQL语句是一样的。
AcHerat 元老 2010-12-09
  • 打赏
  • 举报
回复
两种方法都试了,依旧不行,没有数据显示,表里符合的数据很多



drop table #r
create table #r(customer int,amount decimal(12,2))

go

declare @id int
set @id = 6
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)

insert into #r
select customer,sum(totalAmount) as amount
from DM_DeliveryOrder a
where a.company in (select id from t)
and convert(varchar(7),a.createTime,120) = convert(varchar(7),'2010-10-12',120)
group by a.customer

select * from #r



没显示。。。。



drop table #r
create table #r(customer int,amount decimal(12,2))
insert into #r
select customer,sum(totalAmount) as amount
from DM_DeliveryOrder a
where a.company in (select id from t)
and convert(varchar(7),a.createTime,120) = convert(varchar(7),'2010-10-12',120)
group by a.customer
go

declare @id int
set @id = 6
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)
select * from t

select * from #r



对象名t无效!!!
AcHerat 元老 2010-12-09
  • 打赏
  • 举报
回复
我测试看看,谢大家帮忙,一会结贴!
飘零一叶 2010-12-09
  • 打赏
  • 举报
回复
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)
select * from t
中国风 2010-12-09
  • 打赏
  • 举报
回复
create table #r(customer int,amount decimal(12,2))
insert into #r
select customer,sum(totalAmount) as amount
from DM_DeliveryOrder a
where a.company in (select id from t)--是否存在
and convert(varchar(7),a.createTime,120) = convert(varchar(7),'2010-10-12',120)
----------------------
樓主應該是這樣改

create table #r(customer int,amount decimal(12,2))

go

declare @id int
set @id = 6
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)
insert into #r
select customer,sum(totalAmount) as amount
from DM_DeliveryOrder a
where a.company in (select id from t)
and convert(varchar(7),a.createTime,120) = convert(varchar(7),'2010-10-12',120)

select * from #r
drop table #r


dawugui 2010-12-09
  • 打赏
  • 举报
回复
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)

select * from t
水族杰纶 2010-12-09
  • 打赏
  • 举报
回复
t光创建了没使用
--try
declare @id int
set @id = 6
;with t as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)
select * from t

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧