首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

请教这个查询怎样写

2013-12-26 
请问这个查询怎样写？/*已知表T_Test1数据如下:SN号GUID工序检测结果等级维修结果完成时间经手人001A01来料

请问这个查询怎样写？


/*
已知表T_Test1数据如下:

SN号  GUID     工序          检测结果               等级       维修结果       完成时间     经手人
001   A01     来料检测        合格品                  A        NULL          2013-10-01    小红
001   A01     上线检测        合格品                  A        NULL          2013-10-02    小明
002   A01     模组检测        LED灯暗                 FX       NULL          2013-10-03    小军
002   A01     模组维修        LED灯暗                 NULL     更换LED灯     2013-10-04    小强
002   A01     模组检测        漏打螺丝                FX       NULL          2013-10-05    小巧
002   A01     模组维修        漏打螺丝                NULL     重打螺丝      2013-10-06    小强
002   A01     模组检测        1个亮点,1个暗点         B        NULL          2013-10-07    小军 
003   A02     来料检测        合格品                  A        NULL          2013-10-01    小红
003   A02     上线检测        合格品                  A        NULL          2013-10-02    小明
003   A02     模组检测        合格品                  A        NULL          2013-10-03    小军  
----------------------------------------------------------------------------------------------------------------------------------------------------------------
写一个查询如下:
GUID   SN号      来料检测               上线检测           模组检测                                                   模组维修                        完成时间
A01    001,002   小红,合格品,A          小明,合格品,A      小军,LED灯暗,FX,小巧,漏打螺丝,FX,小军,1个亮点,1个暗点,B    小强,更换LED灯,小强,重打螺丝    2013-10-07
A02    003       小红,合格品,A          小明,合格品,A      小军,合格品,A                                                                              2013-10-03   

这个查询如何写？
*/
-------------建立测试环境(MSSQL2008)
if  exists (select * from sysobjects where id = object_id(N'T_Test1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
   drop table T_Test1 
 
end
GO


CREATE TABLE [dbo].[T_Test1](
[SN号] [varchar](50) NULL,
[GUID] [varchar](50) NULL,
[工序] [varchar](50) NULL,
[检测结果] [varchar](50) NULL,
[等级] [varchar](50) NULL,
[维修结果] [varchar](50) NULL,
[完成时间] [varchar](50) NULL,
[经手人] [varchar](50) NULL
) ON [PRIMARY]

GO



insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '001','A01','来料检测','合格品','A','2013-10-01','小红'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '001','A01','上线检测','合格品','A','2013-10-02','小明'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '002','A01','模组检测','LED灯暗','FX','2013-10-03','小军'

insert into T_Test1(SN号,GUID,工序,检测结果,维修结果,完成时间,经手人)
select '002','A01','模组维修','LED灯暗','更换LED灯','2013-10-04','小强'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '002','A01','模组检测','漏打螺丝','FX','2013-10-05','小巧'

insert into T_Test1(SN号,GUID,工序,检测结果,维修结果,完成时间,经手人)
select '002','A01','模组维修','漏打螺丝','重打螺丝','2013-10-06','小强'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '002','A01','模组检测','1个亮点,1个暗点','B','2013-10-07','小军'


insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '003','A02','来料检测','合格品','A','2013-10-01','小红'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '003','A02','上线检测','合格品','A','2013-10-02','小明'

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)
select '003','A02','模组检测','合格品','A','2013-10-03','小军'

GO
select * from T_Test1


[解决办法]
for xml path
[解决办法]
SELECT 
[GUID],
[SN号] = STUFF((SELECT DISTINCT ','+[SN号] FROM T_Test1 B WHERE B.[GUID]=A.[GUID] FOR XML PATH('')),1,1,''),
[来料检测] = STUFF((SELECT ','+经手人+','+检测结果+','+等级 FROM T_Test1 B WHERE B.[GUID]=A.[GUID] AND B.工序 = '来料检测' ORDER BY 完成时间 FOR XML PATH('')),1,1,''),
[上线检测] = STUFF((SELECT ','+经手人+','+检测结果+','+等级 FROM T_Test1 B WHERE B.[GUID]=A.[GUID] AND B.工序 = '上线检测' ORDER BY 完成时间 FOR XML PATH('')),1,1,''),
[模组检测] = STUFF((SELECT ','+经手人+','+检测结果+','+等级 FROM T_Test1 B WHERE B.[GUID]=A.[GUID] AND B.工序 = '模组检测' ORDER BY 完成时间 FOR XML PATH('')),1,1,''),
[模组维修] = STUFF((SELECT ','+经手人+','+维修结果 FROM T_Test1 B WHERE B.[GUID]=A.[GUID] AND B.工序 = '模组维修' ORDER BY 完成时间 FOR XML PATH('')),1,1,''),
[完成时间] = MAX(完成时间)
FROM T_Test1 A
GROUP BY [GUID]
/*
GUIDSN号来料检测上线检测模组检测模组维修完成时间
A01001,002小红,合格品,A小明,合格品,A小军,LED灯暗,FX,小巧,漏打螺丝,FX,小军,1个亮点,1个暗点,B小强,更换LED灯,小强,重打螺丝2013-10-07
A02003小红,合格品,A小明,合格品,A小军,合格品,ANULL2013-10-03
*/

[解决办法]
if  exists (select * from sysobjects where id = object_id(N'T_Test1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
   drop table T_Test1
end
GO


CREATE TABLE [dbo].[T_Test1](
[SN号] [varchar](50) NULL,
[GUID] [varchar](50) NULL,
[工序] [varchar](50) NULL,
[检测结果] [varchar](50) NULL,
[等级] [varchar](50) NULL,
[维修结果] [varchar](50) NULL,
[完成时间] [varchar](50) NULL,
[经手人] [varchar](50) NULL
) ON [PRIMARY]
GO

insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人) select '001','A01','来料检测','合格品','A','2013-10-01','小红'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人) select '001','A01','上线检测','合格品','A','2013-10-02','小明'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人) select '002','A01','模组检测','LED灯暗','FX','2013-10-03','小军'
insert into T_Test1(SN号,GUID,工序,检测结果,维修结果,完成时间,经手人)select '002','A01','模组维修','LED灯暗','更换LED灯','2013-10-04','小强'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)select '002','A01','模组检测','漏打螺丝','FX','2013-10-05','小巧'
insert into T_Test1(SN号,GUID,工序,检测结果,维修结果,完成时间,经手人)select '002','A01','模组维修','漏打螺丝','重打螺丝','2013-10-06','小强'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)select '002','A01','模组检测','1个亮点,1个暗点','B','2013-10-07','小军' 
 
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)select '003','A02','来料检测','合格品','A','2013-10-01','小红'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)select '003','A02','上线检测','合格品','A','2013-10-02','小明'
insert into T_Test1(SN号,GUID,工序,检测结果,等级,完成时间,经手人)select '003','A02','模组检测','合格品','A','2013-10-03','小军'
GO


;with cte as
(
select GUID,SN号,工序,经手人+case when isnull(检测结果,'')='' then '' else ','+检测结果 end
+case when isnull(等级,'')='' then '' else ','+等级 end as result,完成时间
from T_Test1 
)
select a.GUID,
stuff((select ','+SN号 from (select distinct GUID,SN号 from cte) b 
       where b.GUID=a.GUID 
       for xml path('')),1,1,'') 'SN号',
stuff((select ','+result from (select GUID,工序,result from cte where 工序='来料检测') b 
       where b.GUID=a.GUID 
       for xml path('')),1,1,'') '来料检测',
stuff((select ','+result from (select GUID,工序,result from cte where 工序='上线检测') b 
       where b.GUID=a.GUID 
       for xml path('')),1,1,'') '上线检测',
stuff((select ','+result from (select GUID,工序,result from cte where 工序='模组检测') b 
       where b.GUID=a.GUID 
       for xml path('')),1,1,'') '模组检测',
stuff((select ','+result from (select GUID,工序,result from cte where 工序='模组维修') b 
       where b.GUID=a.GUID 
       for xml path('')),1,1,'') '模组维修',
MAX(完成时间) as 完成时间
from cte a
group by  a.GUID
/*
GUIDSN号来料检测上线检测模组检测模组维修完成时间
A01001,002小红,合格品,A小明,合格品,A小军,LED灯暗,FX,小巧,漏打螺丝,FX,小军,1个亮点,1个暗点,B小强,LED灯暗,小强,漏打螺丝2013-10-07
A02003小红,合格品,A小明,合格品,A小军,合格品,ANULL2013-10-03
*/



[解决办法]
呵呵，再修改一下，这次好了：
select distinct guid,

       stuff(
              (
  select ','+t2.sn号
  from t_test1 t2
  where t2.guid = t1.guid
  group by t2.sn号
  for xml path('')
              ),
              1,1,''
            ) as sn,
 
       stuff(
              (
  select isnull(','+t2.经手人,'') +isnull(',' + 检测结果,'')+isnull(','+等级,'')
  from t_test1 t2
  where t2.guid = t1.guid and t2.sn号 = t2.sn号 
        and t2.工序 = '来料检测'
  for xml path('')
              ),
              1,1,''
            ) as 来料检测,
            
       stuff(
              (
  select isnull(','+t2.经手人,'') +isnull(',' + 检测结果,'')+isnull(','+等级,'')
  from t_test1 t2
  where t2.guid = t1.guid and t2.sn号 = t2.sn号 
        and t2.工序 = '上线检测'
  for xml path('')
              ),
              1,1,''
            ) as 上线检测,
                        
       stuff(
              (
  select isnull(','+t2.经手人,'') +isnull(',' + 检测结果,'')+isnull(','+等级,'')
  from t_test1 t2
  where t2.guid = t1.guid and t2.sn号 = t2.sn号 
        and t2.工序 = '模组检测'
  for xml path('')
              ),
              1,1,''
            ) as 模组检测, 
 

       stuff(
              (
  select isnull(','+t2.经手人,'') +isnull(',' + 维修结果,'')
  from t_test1 t2
  where t2.guid = t1.guid and t2.sn号 = t2.sn号 
        and t2.工序 = '模组维修'
  for xml path('')
              ),
              1,1,''
            ) as 模组维修,
        
       max([完成时间]) over(partition by guid) as 完成时间            
                   
from t_test1 t1
/*
guidsn    来料检测         上线检测         模组检测                                                    模组维修                       完成时间
A01    001,002小红,合格品,A小明,合格品,A小军,LED灯暗,FX,小巧,漏打螺丝,FX,小军,1个亮点,1个暗点,B小强,更换LED灯,小强,重打螺丝  2013-10-07
A02    003    小红,合格品,A小明,合格品,A小军,合格品,A                                        NULL                        2013-10-03

*/

[解决办法]

declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
            +'isnull(stuff((select '',''+b.x from t b where b.GUID=a.GUID and b.工序='''+工序+''' for xml path('''')),1,1,''''),'''') '''+工序+''' '
 from
 (select row_number() over(order by case 工序 when '来料检测' then 1
                                             when '上线检测' then 2
                                             when '模组检测' then 3
                                             when '模组维修' then 4 end) 'rn',工序 
  from (select distinct 工序 from T_Test1) t) y order by rn

select @tsql='with t as
(select GUID,SN号,isnull(经手人,'''')+'',''+case when charindex(''检测'',工序,1)>0 then isnull(检测结果,'''')
                                           when charindex(''维修'',工序,1)>0 then isnull(维修结果,'''') end
                                     +'',''+isnull(等级,'''') ''x'',工序,完成时间
 from T_Test1)
  select a.GUID,
         stuff((select distinct '',''+b.SN号 from t b where b.GUID=a.GUID for xml path('''')),1,1,'''') ''SN号'','
       +@tsql+','
       +' max(完成时间) ''完成时间''
 from t a group by a.GUID '
 
exec(@tsql)

/*
GUID  SN号        来料检测                上线检测               模组检测                                                         模组维修                           完成时间 
 
----- ---------- -------------------- -------------------- --------------------------------------------------------- ------------------------------ ---------------
A01   001,002    小红,合格品,A          小明,合格品,A         小军,LED灯暗,FX,小巧,漏打螺丝,FX,小军,1个亮点,1个暗点,B      小强,更换LED灯,,小强,重打螺丝,    2013-10-07
A02   003        小红,合格品,A          小明,合格品,A         小军,合格品,A                                                                             2013-10-03

(2 row(s) affected)
*/

[解决办法]
你尝试建索引看看？1000条和100条虽然都是10秒，估计只是偶然，不是完全绝对，这与数据存储分布有关系。
[解决办法]
引用:
这个查询有没有办法提高速度呀

现在有12W条记录
分页查询10000条记录时用了18秒
分页查询1000条记录时用了10秒
分页查询100条记录也是用了10秒左右 
分页查询10条记录用了5秒

搞不懂得1000条和100条都用了10秒

单纯从SQL写法上应该没法优化,
空间换时间的方法: 建物理表,保存该查询结果,然后设SQL作业定期刷新.
查询时直接访问该统计表即可.

读书人精选
热点排行