--创建表T_ProductInitialDataTablesCREATE TABLE T_ProductInitialDataTables
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');--现在查询的结果
select * from T_ProductInitialDataTables
SerialNo productEnName TableName FIELENAME FIELEVALUE41 ADMS20 AssessmentMemory PointCode 网点号
42 ADMS20 AssessmentMemory SerialNo 流水号
43 ADMS20 AssessmentMemory Memory 备注
44 ADMS20 AssessmentMemory PointCode 网点号
45 ADMS20 AssessmentMemory SerialNo 流水号
46 ADMS20 AssessmentMemory Memory 备注
--如何得到这样的结果????
productEnName Memory SerialNo PointCodeADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号---------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
有没有虚拟表的做法???????
现在就用这种方法!!
类似以这样的,但是不完整!!!(谁能帮我补充完整???)
-------------------------------------------------------------------------------------------------------------create proc P_ProductTableInitData_Query @TableName VARCHAR(256), --接收传过来的表名
@ProductEnName VARCHAR(max), --产品文字段名称as
BEGIN
----------------------------------------------------
--这里先把表定死
@TableName='T_SystemSubmit_Jump';
@ProductEnName='ADMS20';
---------------------------------------------
declare @SqlText NVARCHAR(MAX)
declare @ForA int
declare @FprB int
DECLARE @unmber INT --用来保存表中字段的数量
DECLARE @DataVolume INT
DECLARE @Databulk INT
DECLARE @information VARCHAR(MAX) --保存具体的值
--判断全局临时表是否存在【存在将其删除】
IF object_id('tempdb.dbo.##ProvisionalListOfBasicData') <>0
drop table ##ProvisionalListOfBasicData
--查询表结构
DECLARE @ColumnCount INT
SET @SqlText = N'SELECT * INTO ##ProvisionalListOfBasicData FROM '+@TableName+' WHERE 1<>1'
exec Sp_ExecuteSql @SqlText,N'@TableName VARCHAR(256)',@TableName
SELECT *FROM ##ProvisionalListOfBasicData
--通过传过来的表名来查询表中字段的数量DECLARE @unmber INT
SET @unmber=(SELECT max(colid)
FROM sys.syscolumns
WHERE (sys.syscolumns.id = OBJECT_ID('AssessmentMemory')));
--打印查询到表的字段的数量--得到表中的数据量
SET @DataVolume=(SELECT max(SerialNumber) AS 'BUMBER' FROM (
SELECT ROW_NUMBER() OVER(ORDER BY SerialNO) AS 'SerialNumber',
FieleValue FROM T_ProductInitialDataTables WHERE ProductEnName=@ProductEnName and TableName=@TableName
) AS T1);
--实际表中的数据条数=表中的数据量/字段数【@Databulk=@DataVolume/@unmber】
--循环向临时表中添加数据
WHERE @ForA<=(@DataVolume/@unmber)
BEGIN DECLARE @SQLTEXT NVARCHAR(MAX)set @information=@information+@SQLTEXT
SET @SQLTEXT = '
WHERE @ForB<=@unmber
BEGIN SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY SerialNO) AS SerialNumber,
FieleValue FROM T_ProductInitialDataTables WHERE ProductEnName='''+@ProductEnName+''' and TableName='''+@TableName+'''
) AS T1
WHERE T1.SerialNumber=@ForB SET @unmber=@unmber+1
END
'
INSERT INTO ##ProvisionalListOfBasicData(@String) VALUES (@information)
SET @ForA=@ForA+1
ENDEND
-------------------------------------------------------------------------------------------------------------
不能投机取巧的用表中查出来的值!
这里面的数据【表名,产品英文名称,表中的字段名全部是动态的,字段名称有多个,但是多个字段的名称是用一个字符串传进来的】谁能帮助?????
有个这样的例子。create proc get_all(@field varchar(8000),@where varchar(8000))
as
begin
declare @sql nvarchar(4000)
declare @str nvarchar(4000)
declare @cnt int
set @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
exec sp_executesql @str,N'@cnt int output',@cnt output
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
from (select substring(@field,number,charindex(',',@field + ',',number) - number) as FieldName
from master..spt_values
where [type] = 'p' and number between 1 and len(@field)
and substring(','+@field,number,1) = ','
)t
select @sql = @sql + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
end
goexec dbo.get_all 'Memory,SerialNo',''drop proc get_all但是这里的,(SerialNo - 1)什么意思。我不懂,不敢冒然的使用,上面虚拟临时表的方法虽然不完整,效率低,但本人觉得可行
如果有更好的方法更好!!!!!!!!
多谢高人指点!!!!!
( [SerialNo] int NOT NULL IDENTITY (1, 1),
[ProductEnName] VARCHAR(128) NOT NULL,
[TableName] varchar(256) NOT NULL,
[FieldName] varchar(256) NOT NULL,
[FieleValue] varchar(max) NULL,
constraint PK_T_ProductInitialDataTables primary key ([SerialNo]))insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','PointCode','网点号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','SerialNo','流水号');
insert into T_ProductInitialDataTables ([ProductEnName],[TableName],[FieldName],[FieleValue]) values
('ADMS20','AssessmentMemory','Memory','备注');--现在查询的结果
select * from T_ProductInitialDataTables
SerialNo productEnName TableName FIELENAME FIELEVALUE41 ADMS20 AssessmentMemory PointCode 网点号
42 ADMS20 AssessmentMemory SerialNo 流水号
43 ADMS20 AssessmentMemory Memory 备注
44 ADMS20 AssessmentMemory PointCode 网点号
45 ADMS20 AssessmentMemory SerialNo 流水号
46 ADMS20 AssessmentMemory Memory 备注
--如何得到这样的结果????
productEnName Memory SerialNo PointCodeADMS20 备注 流水号 网点号
ADMS20 备注 流水号 网点号---------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
有没有虚拟表的做法???????
现在就用这种方法!!
类似以这样的,但是不完整!!!(谁能帮我补充完整???)
-------------------------------------------------------------------------------------------------------------create proc P_ProductTableInitData_Query @TableName VARCHAR(256), --接收传过来的表名
@ProductEnName VARCHAR(max), --产品文字段名称as
BEGIN
----------------------------------------------------
--这里先把表定死
@TableName='T_SystemSubmit_Jump';
@ProductEnName='ADMS20';
---------------------------------------------
declare @SqlText NVARCHAR(MAX)
declare @ForA int
declare @FprB int
DECLARE @unmber INT --用来保存表中字段的数量
DECLARE @DataVolume INT
DECLARE @Databulk INT
DECLARE @information VARCHAR(MAX) --保存具体的值
--判断全局临时表是否存在【存在将其删除】
IF object_id('tempdb.dbo.##ProvisionalListOfBasicData') <>0
drop table ##ProvisionalListOfBasicData
--查询表结构
DECLARE @ColumnCount INT
SET @SqlText = N'SELECT * INTO ##ProvisionalListOfBasicData FROM '+@TableName+' WHERE 1<>1'
exec Sp_ExecuteSql @SqlText,N'@TableName VARCHAR(256)',@TableName
SELECT *FROM ##ProvisionalListOfBasicData
--通过传过来的表名来查询表中字段的数量DECLARE @unmber INT
SET @unmber=(SELECT max(colid)
FROM sys.syscolumns
WHERE (sys.syscolumns.id = OBJECT_ID('AssessmentMemory')));
--打印查询到表的字段的数量--得到表中的数据量
SET @DataVolume=(SELECT max(SerialNumber) AS 'BUMBER' FROM (
SELECT ROW_NUMBER() OVER(ORDER BY SerialNO) AS 'SerialNumber',
FieleValue FROM T_ProductInitialDataTables WHERE ProductEnName=@ProductEnName and TableName=@TableName
) AS T1);
--实际表中的数据条数=表中的数据量/字段数【@Databulk=@DataVolume/@unmber】
--循环向临时表中添加数据
WHERE @ForA<=(@DataVolume/@unmber)
BEGIN DECLARE @SQLTEXT NVARCHAR(MAX)set @information=@information+@SQLTEXT
SET @SQLTEXT = '
WHERE @ForB<=@unmber
BEGIN SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY SerialNO) AS SerialNumber,
FieleValue FROM T_ProductInitialDataTables WHERE ProductEnName='''+@ProductEnName+''' and TableName='''+@TableName+'''
) AS T1
WHERE T1.SerialNumber=@ForB SET @unmber=@unmber+1
END
'
INSERT INTO ##ProvisionalListOfBasicData(@String) VALUES (@information)
SET @ForA=@ForA+1
ENDEND
-------------------------------------------------------------------------------------------------------------
不能投机取巧的用表中查出来的值!
这里面的数据【表名,产品英文名称,表中的字段名全部是动态的,字段名称有多个,但是多个字段的名称是用一个字符串传进来的】谁能帮助?????
有个这样的例子。create proc get_all(@field varchar(8000),@where varchar(8000))
as
begin
declare @sql nvarchar(4000)
declare @str nvarchar(4000)
declare @cnt int
set @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
exec sp_executesql @str,N'@cnt int output',@cnt output
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
from (select substring(@field,number,charindex(',',@field + ',',number) - number) as FieldName
from master..spt_values
where [type] = 'p' and number between 1 and len(@field)
and substring(','+@field,number,1) = ','
)t
select @sql = @sql + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
end
goexec dbo.get_all 'Memory,SerialNo',''drop proc get_all但是这里的,(SerialNo - 1)什么意思。我不懂,不敢冒然的使用,上面虚拟临时表的方法虽然不完整,效率低,但本人觉得可行
如果有更好的方法更好!!!!!!!!
多谢高人指点!!!!!
解决方案 »
- 两条语句完全相同,为什么执行的总时间不一样?
- 一个会员表,5万条记录,SQL语句读取的时候超时了,求解决办法!
- 问个SQL数据库中关于字段默认值的问题~~~~~
- 如何把表的结构信息导出.
- 关于循环语句中使用SQL查询的效率问题
- 存储过程的输入参数能不能通过函数赋值
- 配置发布服务器问题,在线等待!!
- 请教高手,通过ccproxy 代理上网,如何用查询分析器连接internet 上SQL Server?up有分
- java用ODBC连接SQL2008问题。
- SQL 求救啊,怎么上一季度的采购金额啊
- 行列转换问题!请教高人??
- 安装AdventureWorks2008R2_SR1 出现"Full text search not running"
as
begin
declare @sql nvarchar(4000)
declare @str nvarchar(4000)
declare @cnt int
set @str = N'select @cnt = count(distinct FieldName) from T_ProductInitialDataTables ' + @where + ' group by productEnName,TableName'
exec sp_executesql @str,N'@cnt int output',@cnt output
set @sql = 'select productEnName'
select @sql = @sql + ',max(case FieldName when ''' + FieldName + ''' then FIELEVALUE else '''' end)[' + FieldName + ']'
from (select substring(@field,number,charindex(',',@field + ',',number) - number) as FieldName
from master..spt_values
where [type] = 'p' and number between 1 and len(@field)
and substring(','+@field,number,1) = ','
)t
select @sql = @sql + ' from T_ProductInitialDataTables ' + @where + ' group by productEnName,(SerialNo - 1)/' + ltrim(@cnt)
exec(@sql)
end
goexec dbo.get_all 'Memory,SerialNo',''drop proc get_all
是把你的productEnName行@CNT等份
按照把行分为 1到@cnt,123123123这样子的。你可以把(SerialNo - 1)/ ' + ltrim(@cnt) 查出来看看是什么。
(SerialNo - 1)是随着(@cnt)的值来变化的,
能把这个规律找出来,这个方法就很使用了??
谁有找到这个规律的办法????我给一些数据@cnt=1 剪掉的数=任意数
@cnt=2 剪掉的数= -1,-3,-5..........................
@cnt=3 剪掉的数= -2, -5,-8.........................
@cnt=4 剪掉的数= -1,-5,-9.....................
@cnt=5 剪掉的数= -3,-8,,-13
@cnt=6 剪掉的数= -2,...............................
@cnt=7 剪掉的数= -3,..................
@cnt=8 剪掉的数= -5,..................
@cnt=9 剪掉的数= -5,..................
@cnt=10 剪掉的数= -5,..................
@cnt=11 剪掉的数= -9,..................
@cnt=12 剪掉的数= -11,..................
. .
. .
. .
. .
. .