优化一个SQL代码
建表语句如下
CREATE TABLE [dbo].[D_typeParaMapping](
[Id] [int] IDENTITY(1,1) NOT NULL,
[productTypeId] [smallint] NOT NULL,
[P1Name] [varchar](50) NULL,
[p2Name] [varchar](50) NULL,
[p3Name] [varchar](50) NULL,
[p4Name] [varchar](50) NULL,
[p5Name] [varchar](50) NULL,
[p6Name] [varchar](50) NULL,
[p7Name] [varchar](50) NULL,
[p8Name] [varchar](50) NULL,
[p9Name] [varchar](50) NULL,
[p10Name] [varchar](50) NULL,
[p11Name] [varchar](50) NULL,
[p12Name] [varchar](50) NULL,
[p13Name] [varchar](50) NULL,
[p14Name] [varchar](50) NULL,
[p15Name] [varchar](50) NULL,
[p16Name] [varchar](50) NULL,
[p17Name] [varchar](50) NULL,
[p18Name] [varchar](50) NULL,
[p19Name] [varchar](50) NULL,
[p20Name] [varchar](50) NULL,
[p21Name] [varchar](50) NULL,
[p22Name] [varchar](50) NULL,
[p23Name] [varchar](50) NULL,
[p24Name] [varchar](50) NULL,
[p25Name] [varchar](50) NULL,
[p26Name] [varchar](50) NULL,
[p27Name] [varchar](50) NULL,
[p28Name] [varchar](50) NULL,
[p29Name] [varchar](50) NULL,
[p30Name] [varchar](50) NULL,
[p31Name] [varchar](50) NULL,
[p32Name] [varchar](50) NULL,
[p33Name] [varchar](50) NULL,
[p34Name] [varchar](50) NULL,
[p35Name] [varchar](50) NULL,
[p36Name] [varchar](50) NULL,
[p37Name] [varchar](50) NULL,
[p38Name] [varchar](50) NULL,
[p39Name] [varchar](50) NULL,
[p40Name] [varchar](50) NULL,
[P41Name] [varchar](50) NULL,
[p42Name] [varchar](50) NULL,
[p43Name] [varchar](50) NULL,
[p44Name] [varchar](50) NULL,
[p45Name] [varchar](50) NULL,
[p46Name] [varchar](50) NULL,
[p47Name] [varchar](50) NULL,
[P48Name] [varchar](50) NULL,
[p49Name] [varchar](50) NULL,
[p50Name] [varchar](50) NULL,
[p51Name] [varchar](50) NULL,
[p52Name] [varchar](50) NULL,
[p53Name] [varchar](50) NULL,
[p54Name] [varchar](50) NULL,
[p55Name] [varchar](50) NULL,
[p56Name] [varchar](50) NULL,
[p57Name] [varchar](50) NULL,
[p58Name] [varchar](50) NULL,
[p59Name] [varchar](50) NULL,
[p60Name] [varchar](50) NULL,
[p61Name] [varchar](50) NULL,
[p62Name] [varchar](50) NULL,
[p63Name] [varchar](50) NULL,
[p64Name] [varchar](50) NULL,
[p65Name] [varchar](50) NULL,
[p66Name] [varchar](50) NULL,
[p67Name] [varchar](50) NULL,
[p68Name] [varchar](50) NULL,
[p69Name] [varchar](50) NULL,
[p70Name] [varchar](50) NULL,
[f1Name] [varchar](50) NULL,
[f2Name] [varchar](50) NULL,
[f3Name] [varchar](50) NULL,
[f4Name] [varchar](50) NULL,
[f5Name] [varchar](50) NULL,
[p71Name] [varchar](50) NULL,
[p72Name] [varchar](50) NULL,
[p73Name] [varchar](50) NULL,
[p74Name] [varchar](50) NULL,
[p75Name] [varchar](50) NULL,
[p76Name] [varchar](50) NULL,
[p77Name] [varchar](50) NULL,
[p78Name] [varchar](50) NULL,
[p79Name] [varchar](50) NULL,
[p80Name] [varchar](50) NULL,
[p81Name] [varchar](50) NULL,
[p82Name] [varchar](50) NULL,
[p83Name] [varchar](50) NULL,
[p84Name] [varchar](50) NULL,
[p85Name] [varchar](50) NULL,
[p86Name] [varchar](50) NULL,
[p87Name] [varchar](50) NULL,
[p88Name] [varchar](50) NULL,
[p89Name] [varchar](50) NULL,
[p90Name] [varchar](50) NULL,
[p91Name] [varchar](50) NULL,
[p92Name] [varchar](50) NULL,
[p93Name] [varchar](50) NULL,
[p94Name] [varchar](50) NULL,
[p95Name] [varchar](50) NULL,
[p96Name] [varchar](50) NULL,
[p97Name] [varchar](50) NULL,
[p98Name] [varchar](50) NULL,
[p99Name] [varchar](50) NULL,
[p100Name] [varchar](50) NULL,
[p101Name] [varchar](50) NULL,
[p102Name] [varchar](50) NULL,
[p103Name] [varchar](50) NULL,
[p104Name] [varchar](50) NULL,
[p105Name] [varchar](50) NULL,
[p106Name] [varchar](50) NULL,
[p107Name] [varchar](50) NULL,
[p108Name] [varchar](50) NULL,
[p109Name] [varchar](50) NULL,
[p110Name] [varchar](50) NULL,
[p111Name] [varchar](50) NULL,
[p112Name] [varchar](50) NULL,
[p113Name] [varchar](50) NULL,
[p114Name] [varchar](50) NULL,
[p115Name] [varchar](50) NULL,
[p116Name] [varchar](50) NULL,
[p117Name] [varchar](50) NULL,
[p118Name] [varchar](50) NULL,
[p119Name] [varchar](50) NULL,
[p120Name] [varchar](50) NULL,
[p121Name] [varchar](50) NULL,
[p122Name] [varchar](50) NULL,
[p123Name] [varchar](50) NULL,
[p124Name] [varchar](50) NULL,
[p125Name] [varchar](50) NULL,
[p126Name] [varchar](50) NULL,
[p127Name] [varchar](50) NULL,
[p128Name] [varchar](50) NULL,
[p129Name] [varchar](50) NULL,
[p130Name] [varchar](50) NULL,
[p131Name] [varchar](50) NULL,
[p132Name] [varchar](50) NULL,
[p133Name] [varchar](50) NULL,
[p134Name] [varchar](50) NULL,
[p135Name] [varchar](50) NULL,
[p136Name] [varchar](50) NULL,
[p137Name] [varchar](50) NULL,
[p138Name] [varchar](50) NULL,
[p139Name] [varchar](50) NULL,
[p140Name] [varchar](50) NULL,
[p141Name] [varchar](50) NULL,
[p142Name] [varchar](50) NULL,
[p143Name] [varchar](50) NULL,
[p144Name] [varchar](50) NULL,
[p145Name] [varchar](50) NULL,
[p146Name] [varchar](50) NULL,
[p147Name] [varchar](50) NULL,
[p148Name] [varchar](50) NULL,
[p149Name] [varchar](50) NULL,
[p150Name] [varchar](50) NULL,
[p151Name] [varchar](50) NULL,
[p152Name] [varchar](50) NULL,
[p153Name] [varchar](50) NULL,
[p154Name] [varchar](50) NULL,
[p155Name] [varchar](50) NULL,
[p156Name] [varchar](50) NULL,
[p157Name] [varchar](50) NULL,
[p158Name] [varchar](50) NULL,
[p159Name] [varchar](50) NULL,
[p160Name] [varchar](50) NULL,
[p161Name] [varchar](50) NULL,
[p162Name] [varchar](50) NULL,
[p163Name] [varchar](50) NULL,
[p164Name] [varchar](50) NULL,
[p165Name] [varchar](50) NULL,
[p166Name] [varchar](50) NULL,
[p167Name] [varchar](50) NULL,
[p168Name] [varchar](50) NULL,
[p169Name] [varchar](50) NULL,
[p170Name] [varchar](50) NULL,
[p171Name] [varchar](50) NULL,
[p172Name] [varchar](50) NULL,
[p173Name] [varchar](50) NULL,
[p174Name] [varchar](50) NULL,
[p175Name] [varchar](50) NULL,
[p176Name] [varchar](50) NULL,
[p177Name] [varchar](50) NULL,
[p178Name] [varchar](50) NULL,
[p179Name] [varchar](50) NULL,
[p180Name] [varchar](50) NULL,
[p181Name] [varchar](50) NULL,
[p182Name] [varchar](50) NULL,
[p183Name] [varchar](50) NULL,
[p184Name] [varchar](50) NULL,
[p185Name] [varchar](50) NULL,
[p186Name] [varchar](50) NULL,
[p187Name] [varchar](50) NULL,
[p188Name] [varchar](50) NULL,
[p189Name] [varchar](50) NULL,
[p190Name] [varchar](50) NULL,
[p191Name] [varchar](50) NULL,
[p192Name] [varchar](50) NULL,
[p193Name] [varchar](50) NULL,
[p194Name] [varchar](50) NULL,
[p195Name] [varchar](50) NULL,
[p196Name] [varchar](50) NULL,
[p197Name] [varchar](50) NULL,
[p198Name] [varchar](50) NULL,
[p199Name] [varchar](50) NULL,
[p200Name] [varchar](50) NULL,
CONSTRAINT [PK_D_typeParaMapping] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
我要查出该表的一条数据 ，根据 id来查找
比如 id=1 只有 p1Name 和p2name 是有值的 我要的结果是
列名 结果
P1Name 大小
p2Name 长度
p1-p2Name有值的才查询出来没值的不显示。
目前我的代码
- SQL code
declare @id int set @id=1 declare @row table(name varchar(100),val varchar(100)) select name,row_number() over(order by getdate()) as r into #c from syscolumns where id =OBJECT_ID('[D_typeParaMapping]') and name!='id' and name!='productTypeId' declare @count int declare @i int set @i=1 set @count=(select count(1) from #c) while @i>0 begin declare @name nvarchar(100) declare @sql nvarchar(500) declare @val nvarchar(100) select @name=name from #c where r=@i set @sql='select @val='+@name+' from D_typeParaMapping where id=@id ' exec sp_executesql @sql,N'@id int,@val nvarchar(100) output',@id,@val output set @i=@i+1 if @i=@count set @i=0 if @val is not null and @val!='' insert @row select @name as 列名,@val 值 end select * from @row drop table #c
查出结果
列名 值
p60Name大小
p61Name长度
[解决办法]
限定某列也是可以的
- SQL code
declare @sql varchar(max)select @sql = isnull(@sql + ' union all ' , '' ) + ' select '+quotename(Name) +' as value ,'''+quotename(Name)+''' as name from t where s1=1'from syscolumns where ID = object_id('t')PRINT @sqlexec('select * from ('+@sql+') t1 where t1.value is not null')
[解决办法]
- SQL code
declare @sql varchar(8000)declare @sql2 varchar(8000)declare @sql3 varchar(8000)set @SQL=''set @SQL2=''set @SQL3=''select @SQL=@SQL+'isnull(convert(varchar(8000),['+name+']),''A'')+' from syscolumns where ID=501576825 and colorder<=80 order by colorderselect @SQL2=@SQL2+'isnull(convert(varchar(8000),['+name+']),''A'')+' from syscolumns where ID=501576825 and colorder>80 and colorder<=140 order by colorderselect @SQL3=@SQL3+'isnull(convert(varchar(8000),['+name+']),''A'')+' from syscolumns where ID=501576825 and colorder>140 order by colorderset @SQL3=left(@SQL3,len(@SQL3)-1)exec('select * from(select name,colorder,substring(new,colorder,1) new from(select name,colorder,'+@SQL+''+@SQL2+''+@SQL3+' NEW from(select * from(select top 100 percent name,colorder from syscolumns where ID=501576825 order by colorder) A,(select * from D_typeParaMapping) B) A) A) A where new<>''A''')