求一动态表的查询方法

[ss__328]

有一索引表：CmDYDY 用作保存所有数据采集终端属性

如下所示:
UnitCode(终端编号) Address(地址) UserName(用户名)....
M01A02010003 11391662 用户1
M01A02010004 11391634 用户2
M01A02010005 11391711 用户3

由于每个用户终端都有大量数据,因此每个终端数据动态创建表储存,其表名创建规则为UnitCode+XX(年),如采集终端M01A02010003 06年的数据表为M01A0201000306
表结构大致为:
RefreshTime(采集时间) Data(数据).......


现想做以下查询,请教下各位大大最优化的查询方式:

查询每个终端06年的最大数据
UnitCode(终端编号) Address(地址) UserName(用户名) MaxData
M01A02010003 11391662 用户1
M01A02010004 11391634 用户2
M01A02010005 11391711 用户3

请问应该如何实现?

我想做成:
SELECT *,
(SELECT MAX(Data) AS Data
FROM ~~~此处根据UNITCODE动态生成表名~~) AS MaxData
FROM (SELECT ADDRESS, UNITCODE, UserName
FROM CmDYDY) t
ORDER BY UNITCODE
不知如何解决UNITCODE与表名的动态生成,求教这种方法可行么?

[WangZWang]

select UnitCode,Address,UserName,max(data) as maxdata
from CmDYDY
group by UnitCode,Address,UserName

[ss__328]

楼上的理解错误，CmDYDY表中没有Data字段阿，是每个终端都有对应的数据表储存数据，只是储存数据的表名和终端索引表中的UnitCode 字段有关系而已

~~~~~~~~~~
由于每个用户终端都有大量数据,因此每个终端数据动态创建表储存,其表名创建规则为UnitCode+XX(年),如采集终端M01A02010003 06年的数据表为M01A0201000306
表结构大致为:
RefreshTime(采集时间) Data(数据).......
~~~~~~~~~~

[hellowork]

这样试试:
if object_id('tb') is not null
drop table tb
----创建测试数据
create table tb(UnitCode varchar(20),Address varchar(20),UserName varchar(20))
insert tb
select 'M01A02010003','11391662','用户1' union all
select 'M01A02010004','11391634','用户2' union all
select 'M01A02010005','11391711','用户3'
----创建动态UNION语句
declare @str varchar(8000)
set @str = ''
select @str = @str + ' union all
select *,(select max(data) as data from ' + UnitCode + '06) as MaxData From
(SELECT ADDRESS, UNITCODE, UserName FROM CmDYDY) t ' from tb
set @str = stuff(@str,1,10,'') + ' ORDER BY UNITCODE'
----查看SQL语句
print @str
----执行SQL
exec(@str)

[hellowork]

抱歉,上面的代码中表名称不一致,tb应该为CmDYDY.更正一下:
if object_id('CmDYDY_test') is not null
drop table CmDYDY_test
----创建测试数据
create table CmDYDY_test(UnitCode varchar(20),Address varchar(20),UserName varchar(20))
insert CmDYDY_test
select 'M01A02010003','11391662','用户1' union all
select 'M01A02010004','11391634','用户2' union all
select 'M01A02010005','11391711','用户3'
----创建动态UNION语句
declare @str varchar(8000)
set @str = ''
select @str = @str + ' union all
select *,(select max(data) as data from ' + UnitCode + '06) as MaxData From
(SELECT ADDRESS, UNITCODE, UserName FROM CmDYDY_test) t ' from CmDYDY_test
set @str = stuff(@str,1,10,'') + ' ORDER BY UNITCODE'
----查看SQL语句
print @str
----执行SQL
--exec(@str)

[playwarcraft]

--不知道是這個意思不?
create table cmDYDY(UnitCode varchar(30),ADDRESS varchar(20),userName varchar(20))
insert into cmDYDY
select 'M01A02010003', '11391662' ,'user1'
union all
select 'M01A02010004', '11391634' ,'user2'
union all
select 'M01A02010005', '11391711' ,'user3'

create table M01A0201000306(retime datetime,data int)
insert into M01A0201000306 select '2006-07-30',100
insert into M01A0201000306 select '2006-07-31',200
insert into M01A0201000306 select '2006-06-30',150

create table M01A0201000406(retime datetime,data int)
insert into M01A0201000406 select '2006-05-30',100
insert into M01A0201000406 select '2006-05-23',300
insert into M01A0201000406 select '2006-04-30',150

create table M01A0201000506(retime datetime,data int)
insert into M01A0201000506 select '2006-07-20',200
insert into M01A0201000506 select '2006-07-29',100
insert into M01A0201000506 select '2006-06-23',330

/*use tmp_cm來顯示數據*/
create table tmp_cm(unitcode varchar(30),address varchar(20),username varchar(30),maxdata int)

/**/
declare @a varchar(30),@u varchar(20),@n varchar(20),@table varchar(30),@sql varchar(1000)
set @table=''
set @sql=''
declare c1 cursor scroll for
SELECT UNITCODE,ADDRESS, UserName FROM CmDYDY
open c1
fetch first from c1 into @u,@a,@n
while @@fetch_status=0
begin
set @table=@u+'06'
set @sql='insert into tmp_cm select *,[data]=(select max(data) from '+@table+') from CmDYDY where UnitCode='''+@u+''''
exec (@sql)
fetch next from c1 into @u,@a,@n
end
close c1
deallocate c1

--check the result
select * from tmp_cm

drop table tmp_cm
drop table cmDYDY
drop table M01A0201000306
drop table M01A0201000406
drop table M01A0201000506

/**/
unitcode address username maxdata
--------------------------------------------------------------------
M01A02010003 11391662 user1 200
M01A02010004 11391634 user2 300
M01A02010005 11391711 user3 330

[昵称被占用了]

declare @sql varchar(8000)
set @sql=''
SELECT @sql=@sql+'select '''+UnitCode+''' as UnitCode,max(data) as MaxData from '+UnitCode+'06 union all '
FROM CmDYDY t
ORDER BY UNITCODE

set @sql=left(@sql,len(@sql)-10)
exec ('SELECT c.*,t.MaxData
FROM CmDYDY c,('+@sql+') as t
where c.UNITCODE=t.UNITCODE
ORDER BY c.UNITCODE')


,
(SELECT MAX(Data) AS Data
FROM ~~~此处根据UNITCODE动态生成表名~~) AS MaxData

[ss__328]

xujexy的调用出这个错误
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。

怎么回事呢

[mylkzy]

将declare @sql varchar(8000)改为declare @sql Nvarchar(4000)试试

[xujexy]

因为@UnitCode 为null，所以@sql也为null

修正：
CREATE PROCEDURE midCmDYDY AS


declare @sql varchar(8000)
declare @UnitCode as varchar(50)
set @sql=''
--declare @tmp table(retime datetime,data int)
--select * into from
create table #tmp(retime datetime,data int)
SELECT @sql='insert into #tmp select '''+isnull(@UnitCode,'')+''' as UnitCode,max(data) as MaxData from '+isnull(@UnitCode,'')+'06 '


exec sp_executesql @sql

SELECT c.*,t.MaxData
FROM CmDYDY c,#tmp as t
where c.UNITCODE=t.UNITCODE
ORDER BY c.UNITCODE

GO

[hellowork]

请楼上的朋友更正一下:
sp_executesql的前二个参数必须为nvarchar类型,所以在声明@sql时请指定其类型为nvarchar,否则还会出现"过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型"这样的语法错误.

[xujexy]

hellowork(一两清风) ( ) 信誉：100
谢谢，你说得对

[ss__328]

谢谢Hellowork和Haiwer，你们的方法是可行的，但是有个问题，因为我CmDYDY 表中列数比较多（大概有50个终端），@sql 8000个字符不够长怎么办，老出错

[lxzm1001]

@sql 8000个字符不够长怎么办，老出错

这好像要定义多个变量了

[ss__328]

playwarcraft 的方法可以解决我的问题了~~~,谢谢,测试通过.大概也只能使用游标了吧

[xujexy]

用sp和临时表（我这个人不喜欢用游标，觉得慢）

CREATE PROCEDURE midCmDYDY AS


declare @sql varchar(8000)
declare @UnitCode as varchar(50)
set @sql=''
declare @tmp table(retime datetime,data int)


SELECT @sql='insert into @tmp select '''+@UnitCode+''' as UnitCode,max(data) as MaxData from '+@UnitCode+'06 '
exec sp_executesql @sql

SELECT c.*,t.MaxData
FROM CmDYDY c,@tmp as t
where c.UNITCODE=t.UNITCODE
ORDER BY c.UNITCODE

GO