原始表a001
DAte Indexe Val2006-9-15 01:33:34 1 234.52578
2006-9-15 01:53:34 1 0
2006-9-15 01:33:34 2 1352.86792
2006-9-15 01:33:34 3 .875628456
2006-9-15 01:33:34 4 352.86792
2006-9-15 02:34:35 1 252.67984
2006-9-15 02:34:35 2 1287.86874
2006-9-15 02:34:35 3 .875623576
2006-9-15 02:34:35 4 287.86874
``````````````````````````
IndexeIndexe列的1对应t1列,4对应t2列 3对应t3列
要查找出来昨天的数据传到表a1(需要先把表a1的数据删除掉),并算出来t1列小计,想是放在作业里面运行
假如一个数据在一个小时里面有两条选数值大的哪个,假如都为0,选择前面的哪个格式如下
d1 t1 t2 t3
01 234 352.86 0.875
02 252 287.86 0.875
`````````````````
24 ''' '''''' '''
小计
第一列我设置成DateTime类型数据对吗?
剩下的都是decimal类型,他自己就能处理小数的位置
还有不是只有这3列, Indexe列是1-100多个数字哪,一天大概有3000行原始数据吧,保存3个月是不是应该多建几个表分别放这些数据,就用一个原始表查询起来会很慢吧.想想把没有联系的原始表分开到几个表里面
我想以后能随便的添加别的列.昨天子陌红尘老师帮我写的哪段我都理解啦
DAte Indexe Val2006-9-15 01:33:34 1 234.52578
2006-9-15 01:53:34 1 0
2006-9-15 01:33:34 2 1352.86792
2006-9-15 01:33:34 3 .875628456
2006-9-15 01:33:34 4 352.86792
2006-9-15 02:34:35 1 252.67984
2006-9-15 02:34:35 2 1287.86874
2006-9-15 02:34:35 3 .875623576
2006-9-15 02:34:35 4 287.86874
``````````````````````````
IndexeIndexe列的1对应t1列,4对应t2列 3对应t3列
要查找出来昨天的数据传到表a1(需要先把表a1的数据删除掉),并算出来t1列小计,想是放在作业里面运行
假如一个数据在一个小时里面有两条选数值大的哪个,假如都为0,选择前面的哪个格式如下
d1 t1 t2 t3
01 234 352.86 0.875
02 252 287.86 0.875
`````````````````
24 ''' '''''' '''
小计
第一列我设置成DateTime类型数据对吗?
剩下的都是decimal类型,他自己就能处理小数的位置
还有不是只有这3列, Indexe列是1-100多个数字哪,一天大概有3000行原始数据吧,保存3个月是不是应该多建几个表分别放这些数据,就用一个原始表查询起来会很慢吧.想想把没有联系的原始表分开到几个表里面
我想以后能随便的添加别的列.昨天子陌红尘老师帮我写的哪段我都理解啦
insert @a select '2006-9-15 01:33:34', 1, 234.52578
union all select '2006-9-15 01:53:34', 1, 0
union all select '2006-9-15 01:33:34', 2, 1352.86792
union all select '2006-9-15 01:33:34', 3, .875628456
union all select '2006-9-15 01:33:34', 4, 352.86792
union all select '2006-9-15 02:34:35', 1, 252.67984
union all select '2006-9-15 02:34:35', 2 ,1287.86874
union all select '2006-9-15 02:34:35', 3, .875623576
union all select '2006-9-15 02:34:35', 4, 287.86874create table tim(id int identity(1,1),t1 varchar(20),t2 varchar(20),t3 varchar(20),d1 char(2))
insert tim select top 24 '' as t1,'' as t2,'' as t3,'' as d1 from sysobjects a, sysobjects b
update tim set d1=right('00'+ltrim(id),2)select d1,
t1=isnull((select max(val) from @a a where indexe=1 and convert(char(2),[date],108)=tim.d1),0),
t2=isnull((select max(val) from @a a where indexe=4 and convert(char(2),[date],108)=tim.d1),0),
t3=isnull((select max(val) from @a a where indexe=3 and convert(char(2),[date],108)=tim.d1),0)
into tmp1
from tim
select d1,floor(t1) t1,left(str(t2,20,3),len(str(t2,20,3))-1) t2,left(str(t3,20,4),len(str(t3,20,4))-1) t3 into tmp2 from tmp1select * from tmp2
union all
select '小计' as d1,sum(cast(t1 as decimal(10,0))) t1,sum(cast(t2 as decimal(20,2))) t2,sum(cast(t3 as decimal(20,3))) t3 from tmp2
insert @a select '2006-9-15 01:33:34', 1, 234.52578
union all select '2006-9-15 01:53:34', 1, 0
union all select '2006-9-15 01:33:34', 2, 1352.86792
union all select '2006-9-15 01:33:34', 3, .875628456
union all select '2006-9-15 01:33:34', 4, 352.86792
union all select '2006-9-15 02:34:35', 1, 252.67984
union all select '2006-9-15 02:34:35', 2 ,1287.86874
union all select '2006-9-15 02:34:35', 3, .875623576
union all select '2006-9-15 02:34:35', 4, 287.86874
--以上为基本数据
--------------------------以下为形成24小时的数据
create table tim(id int identity(1,1),t1 varchar(20),t2 varchar(20),t3 varchar(20),d1 char(2))
insert tim select top 24 '' as t1,'' as t2,'' as t3,'' as d1 from sysobjects a, sysobjects b
update tim set d1=right('00'+ltrim(id),2)
---------------------------以下为计算各列的值并存入临时表tmp1方便处理小数位
select d1,
t1=isnull((select max(val) from @a a where indexe=1 and convert(char(2),[date],108)=tim.d1),0),
t2=isnull((select max(val) from @a a where indexe=4 and convert(char(2),[date],108)=tim.d1),0),
t3=isnull((select max(val) from @a a where indexe=3 and convert(char(2),[date],108)=tim.d1),0)
into tmp1
from tim
---------------------------以下继续处理小数位并存入临时表tmp2
select d1,floor(t1) t1,left(str(t2,20,3),len(str(t2,20,3))-1) t2,left(str(t3,20,4),len(str(t3,20,4))-1) t3 into tmp2 from tmp1
---------------------------以下开成合计并合并记录得结果
select * from tmp2
union all
select '小计' as d1,sum(cast(t1 as decimal(10,0))) t1,sum(cast(t2 as decimal(20,2))) t2,sum(cast(t3 as decimal(20,3))) t3 from tmp2
第一列我设置成DateTime类型数据对吗?
----------如果你是要保存"01","24"這樣的數據,用char更好,不要用datetime類型。
还有不是只有这3列, Indexe列是1-100多个数字哪,
------------
如果是有100多個數字,你所產生的列是怎麼對應的??“1对应t1列,4对应t2列 3对应t3列”,最好能有個規律??如果是固定只有100個數字,可以在開始我給你寫的代碼的基礎上將你所要的列加上即可。
如何对应?
Select
Right(100+DatePart(hh,[DateAndTime]),2) As d1,
Max(Case TagIndex When 1 Then Val End) As t1,
Max(Case TagIndex When 4 Then Val End) As t2,
Max(Case TagIndex When 3 Then Val End) As t3
From Fxiaoshiliang
Where DateDiff(dd,[DateAndTime],GetDate())=1
Group By Right(100+DatePart(hh,[DateAndTime]),2) Insert a1 Select '',SUM(t1),0,0 From a1Select * From a1
得到的提示是
服务器: 消息 213,级别 16,状态 4,行 3
插入错误: 列名或所提供值的数目与表定义不匹配。第一列是DateTime类型数据
剩下的都是decimal类型,他自己就能处理小数的位置
还有不是只有这3列, Indexe列是1-100多哪,我想以后能随便的添加别的列.
剩下的都是decimal(20.2)类型,他自己就能处理小数的位置服务器: 消息 213,级别 16,状态 4,行 3
插入错误: 列名或所提供值的数目与表定义不匹配。
drop table [dbo].[biao1]
GOCREATE TABLE [dbo].[biao1] (
[d1] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[t1] [decimal](20, 2) NULL ,
[t2] [decimal](20, 2) NULL ,
[t3] [decimal](20, 2) NULL ,
[liouliang4] [decimal](20, 2) NULL ,
[liouliang5] [decimal](20, 2) NULL ,
[liouliang6] [decimal](20, 2) NULL ,
[liouliang7] [decimal](20, 2) NULL ,
[liouliang8] [decimal](20, 2) NULL
) ON [PRIMARY]
GO
我自己建的表
drop table [dbo].[Fxiaoshiliang]
GOCREATE TABLE [dbo].[Fxiaoshiliang] (
[DateAndTime] [datetime] NULL ,
[Millitm] [smallint] NULL ,
[TagIndex] [smallint] NULL ,
[Val] [float] NULL ,
[Status] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[Marker] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO这个是原始数据表的脚本
(d1,t1,t2,t3)
Select
Right(100+DatePart(hh,[DateAndTime]),2) As d1,
Max(Case TagIndex When 1 Then Val End) As t1,
Max(Case TagIndex When 4 Then Val End) As t2,
Max(Case TagIndex When 3 Then Val End) As t3
From Fxiaoshiliang
Where DateDiff(dd,[DateAndTime],GetDate())=1
Group By Right(100+DatePart(hh,[DateAndTime]),2) Insert a1 (d1,t1,t2,t3) Select '',SUM(t1),0,0 From a1Select * From a1
还有不是只有这3列, Indexe列是1-100多哪,我想以后能随便的添加别的列.---------
只要你的列對應有規律,就可以。
/*引用*/
1.包含两个表------典型行列转换问题例子
--建立测试环境
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
go--删除测试环境
Drop Table tb1,tb2
select
'2006-9-15 01:33:34',1,234.52578
union all
select
'2006-9-15 01:53:34',1,0
union all
select
'2006-9-15 01:33:34',2,1352.86792
union all
select
'2006-9-15 01:33:34',3,0.875628456
union all
select
'2006-9-15 01:33:34',4,352.86792
union all
select
'2006-9-15 02:34:35',1,252.67984
union all
select
'2006-9-15 02:34:35',2,1287.86874
union all
select
'2006-9-15 02:34:35',3,0.875623576
union all
select
'2006-9-15 02:34:35',4,287.86874
DECLARE @Mysql nvarchar(4000)select @Mysql='select datename(yyyy,dt)+datename(mm,dt)+datename(dd,dt)+datename(hh,dt) as d1';select @Mysql=@Mysql+', max(case when NIndex='+rtrim(convert(char(10),NIndex))+' then val else 0 end) as t'+rtrim(convert(char(10),NIndex))
from NatTest
group by NIndexprint @Mysqlselect @Mysql=@Mysql+
' from NatTest '+
'group by datename(yyyy,dt)+datename(mm,dt)+datename(dd,dt)+datename(hh,dt)'exec sp_executesql @Mysql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TagTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TagTable]
GOCREATE TABLE [dbo].[TagTable] (
[TagName] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[TagIndex] [smallint] NULL ,
[TagType] [smallint] NULL ,
[TagDataType] [smallint] NULL
) ON [PRIMARY]
GO
这个也是原始表,这个表的[TagIndex]就是对应原始表格的TagIndex]的列的,本来不想用这个表的,看不还是不能省略,要是太麻烦就不要写啦,写出来我改不了不是给自己添难吗?
游泳的鱼要不我把我的数据库发给你吧,我有很多没用的信箱,我用附件形式发进去,你一附加就可以啦
原始表a001
DAte Indexe Val2006-9-15 01:33:34 1 234.52578
………………………………
------------------------------------------------------------
假如DATE 是“消费时间”,INDEXE是“消费类型”,VAL是“消费金额”,那么我们现在要查询
在某个时间段(查询条件),某个或某些消费类型(查询条件)的最大消费金额(查询的果)
楼住的做法是提前把这写结果都汇总出来,在我们已经有的结果表上进行查询,这样的话,你就不得不把所有消费类型和可能查询的时间段都给汇总出来,我不赞成这种做法,个人认为在原始表上直接查询即可,楼住担心查询效率,原始表很窄,如果分时间段拆分原表,我想查询效率应该不成问题。
在某个时间段(查询条件),某个或某些消费类型(查询条件)的所有数据加上最大消费金额(查询的果),最后就是想要个EXCEL放在哪里就可以了,日报类型的,每天出一次,
Fxiaoshiliang表的TagIndex列下的数字 1.3.5.6相对应的Val列数值.能按照日期把多余的数据删除(就是一个小时只能有一个数据,重复的话选择最大的或者第二选择时间靠前的)放在biao1的哪个列里面都可以,最后再算出来1的小计就好啦