查询语句！立即给分，谢谢了！

2013-12-19

求一个查询语句！！--在线等，立即给分，多谢了！！表yn，表yns，通过mainid关联，1对多关系。表yn：字段： mainiddesc

求一个查询语句！！--在线等，立即给分，多谢了！！
表yn，表yns，通过mainid关联，1对多关系。

表yn：

字段： mainid     desc
内容：    1         aaaaa
        2         bbbbb
        3         cccc

表yns：

字段： mainid     stage  person
内容：    1        阶段A    张三
        1        阶段B     李四
         1                   李四
         1         fga       李四
        2        阶段A     张三
         2        阶段B     李四
         2        阶段C     王五

希望能在查询中过滤掉除了（阶段A,阶段B,阶段C）的数据，以下面的形式列出数据：

     mainid    desc   stage   person  stage     person  stage   person
      1       aaaaa   阶段A    张三      阶段B       李四
       2       bbbbb   阶段A    张三      阶段B       李四    阶段C   王五
[解决办法]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-22 18:04:04
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--Dec 28 2012 20:23:12 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[yn]
if object_id('[yn]') is not null drop table [yn]
go 
create table [yn]([mainid] int,[desc] varchar(5))
insert [yn]
select 1,'aaaaa' union all
select 2,'bbbbb' union all
select 3,'cccc'
--> 测试数据：[yns]
if object_id('[yns]') is not null drop table [yns]
go 
create table [yns]([mainid] int,[stage] varchar(5),[person] varchar(4))
insert [yns]
select 1,'阶段A','张三' union all
select 1,'阶段B','李四' union all
select 1,'李四',null union all
select 1,'fga','李四' union all
select 2,'阶段A','张三' union all
select 2,'阶段B','李四' union all
select 2,'阶段C','王五'
--------------开始查询--------------------------

--select * from [yns]

select a.mainid,a.[DESC],'阶段A' stage,person=MAX(CASE WHEN stage='阶段A' THEN person ELSE NULL END ),
'阶段B' stage,person=MAX(CASE WHEN stage='阶段B' THEN person ELSE NULL END ),
'阶段C' stage,person=MAX(CASE WHEN stage='阶段C' THEN person ELSE NULL END )
from [yn] a INNER JOIN [yns] b ON a.mainid=b.mainid
WHERE stage  IN ('阶段A','阶段B','阶段C')
GROUP BY a.mainid,a.[DESC]
----------------结果----------------------------
/* 
mainid      DESC  stage person stage person stage person
----------- ----- ----- ------ ----- ------ ----- ------
1           aaaaa 阶段A   张三     阶段B   李四     阶段C   NULL 
 
2           bbbbb 阶段A   张三     阶段B   李四     阶段C   王五
Warning: Null value is eliminated by an aggregate or other SET operation.
*/

[解决办法]
动态

SELECT a.mainid,a.[desc],b.[stage],b.[person] INTO #t 
from [yn] a INNER JOIN [yns] b ON a.mainid=b.mainid
WHERE stage  IN ('阶段A','阶段B','阶段C')

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename('stage')+'=max(case when [stage]='+quotename([stage],'''')+' then [stage] else null end)'
+','+quotename('person')+'=max(case when [stage]='+quotename([stage],'''')+' then [person] else null end)'
from #t group by [stage]
exec('select mainid,[DESC]'+@s+' from #t group by  mainid,[DESC]')

[解决办法]

create table #yn(mainid int,[desc]  varchar(10))
insert into #yn
select 1,'aaaaa'
union all select 2,'bbbbb'
union all select 3,'cccc'

create table #yns(mainid int,stage  varchar(10),person  varchar(10))
insert into #yns
select 1,'阶段A','张三'
union all select 1,'阶段B','李四'
union all select 1,'李四',''
union all select 1,'fga','李四'
union all select 2,'阶段A','张三'
union all select 2,'阶段B','李四'
union all select 2,'阶段C','王五'

declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',[stage]=max(case stage when '''+rtrim(stage)+''' then stage end)
,[person]=max(case stage when '''+rtrim(stage)+''' then person end)'
from #yns where stage in('阶段A','阶段B','阶段C') group by stage
exec('select a.mainid,a.[desc]'+@sql+'from  #yn a inner join #yns b on a.mainid=b.mainid group by a.mainid,a.[desc]' )

/*
mainiddescstagepersonstagepersonstageperson
1aaaaa阶段A张三阶段B李四NULLNULL
2bbbbb阶段A张三阶段B李四阶段C王五
*/

[解决办法]
是这样不：

if object_id('[yn]') is not null drop table [yn]
go 
create table [yn]([mainid] int,[desc] varchar(5))
insert [yn]
select 1,'aaaaa' union all
select 2,'bbbbb' union all
select 3,'cccc'



if object_id('[yns]') is not null drop table [yns]
go 
create table [yns]([mainid] int,[stage] varchar(5),[person] varchar(4))
insert [yns]
select 1,'阶段A','张三' union all
select 1,'阶段B','李四' union all
select 1,'李四',null union all
select 1,'fga','李四' union all
select 2,'阶段A','张三' union all
select 2,'阶段B','李四' union all
select 2,'阶段C','王五'

  
declare @sql nvarchar(3000);  
  
set @sql = '';  
  
select @sql =   
   @sql + ',min(case when stage=''' + stage + ''' then stage else null end) as stage'+  
          ',min(case when stage=''' + stage + ''' then person else null end) as person' 
          
from yn
inner join yns
        on yn.mainid = yns.mainid  
where stage in ('阶段A','阶段B','阶段C')
group by   stage
  
  
select @sql = 
   'select yns.mainid,yn.[desc]'+@sql +  
   ' from yn inner join yns on yn.mainid = yns.mainid group by yns.mainid,yn.[desc]'  
  
--select @sql         
exec(@sql)         
/*
mainid      desc  stage person stage person stage person
----------- ----- ----- ------ ----- ------ ----- ------
1           aaaaa 阶段A   张三     阶段B   李四     NULL  NULL
2           bbbbb 阶段A   张三     阶段B   李四     阶段C   王五
*/

[解决办法]
这个是上面动态生成的语句，便于理解哈：




select yns.mainid,yn.[desc], 
 
       min(case when stage='阶段A' then stage else null end) as stage,
       min(case when stage='阶段A' then person else null end) as person,
       min(case when stage='阶段B' then stage else null end) as stage,
       min(case when stage='阶段B' then person else null end) as person,
       min(case when stage='阶段C' then stage else null end) as stage,
       min(case when stage='阶段C' then person else null end) as person 

from yn 
inner join yns 
        on yn.mainid = yns.mainid 
group by yns.mainid,yn.[desc]

[解决办法]

现在才发现这些“立即给分”的都是忽悠，2个月了
[解决办法]
现在才发现这些“立即给分”的都是忽悠，2个月了




[解决办法]

--> 测试数据：[yn]
if object_id('[yn]') is not null drop table [yn]
go 
create table [yn]([mainid] int,[desc] varchar(5))
insert [yn]
select 1,'aaaaa' union all
select 2,'bbbbb' union all
select 3,'cccc'
--> 测试数据：[yns]
if object_id('[yns]') is not null drop table [yns]
go 
create table [yns]([mainid] int,[stage] varchar(5),[person] varchar(4))
insert [yns]
select 1,'阶段A','张三' union all
select 1,'阶段B','李四' union all
select 1,null,'李四' union all
select 1,'fga','李四' union all
select 2,'阶段A','张三' union all
select 2,'阶段B','李四' union all 
 
select 2,'阶段C','王五'


SELECT a.mainid,a.[desc],
MAX(CASE WHEN stage='阶段A' THEN stage ELSE NULL END ) AS stageA,
MAX(CASE WHEN stage='阶段B' THEN stage ELSE NULL END ) AS stageB,
MAX(CASE WHEN stage='阶段C' THEN stage ELSE NULL END ) AS stageC,

MAX(CASE WHEN person='张三' THEN stage ELSE NULL END ) AS person1,
MAX(CASE WHEN person='李四' THEN person ELSE NULL END ) AS person2,
MAX(CASE WHEN person='王五' THEN person ELSE NULL END ) AS person3

FROM yn a JOIN yns b ON a.mainid=b.mainid
GROUP BY a.mainid,a.[desc]

/*
mainid      desc  stageA stageB stageC person1 person2 person3
----------- ----- ------ ------ ------ ------- ------- -------
1           aaaaa 阶段A    阶段B    NULL   阶段A     李四      NULL
2           bbbbb 阶段A    阶段B    阶段C    阶段A     李四      王五
*/

[解决办法]
这个是上面动态生成的语句，便于理解哈：
SQL code

?
1
2
3
4
5
6
7
8
9
10
11
12
select yns.mainid,yn.[desc],
       min(case when stage='阶段A' then stage else null end) as stage,
       min(case when stage='阶段A' then person else null end) as person,
       min(case when stage='阶段B' then stage else null end) as stage,
       min(case when stage='阶段B' then person else null end) as person,
       min(case when stage='阶段C' then stage else null end) as stage,
       min(case when stage='阶段C' then person else null end) as person 
 
from yn 
inner join yns 
        on yn.mainid = yns.mainid 
group by yns.mainid,yn.[desc]


前辈，这里使用min(),max()是不是一个功能呢，表达什么意思啊，不是很明白，就是为了在一行显示嘛？
[解决办法]
with cet as
(
select  yn .[desc], yns .stage,yns .person from yn left join  yns on  yn .mainid =yns.mainid 

)
select [desc],max(case when stage ='阶段A' then '阶段A' end) ,max(case when stage ='阶段A' then person end) ,max(case when stage ='阶段B' then '阶段B' end) ,max(case when stage ='阶段B' then person end),max(case when stage ='阶段C' then '阶段C' end) ,max(case when stage ='阶段C' then person end)    from cet where stage is not null and person  is not null  group by [desc] 

[解决办法]
引用:这个是上面动态生成的语句，便于理解哈：
SQL code

?
1
2
3
4
5
6
7
8
9
10
11
12
select yns.mainid,yn.[desc],
       min(case when stage='阶段A' then stage else null end) as stage,
       min(case when stage='阶段A' then person else null end) as person,
       min(case when stage='阶段B' then stage else null end) as stage,
       min(case when stage='阶段B' then person else null end) as person,
       min(case when stage='阶段C' then stage else null end) as stage,
       min(case when stage='阶段C' then person else null end) as person 
 
from yn 
inner join yns 
        on yn.mainid = yns.mainid 
group by yns.mainid,yn.[desc]


前辈，这里使用min(),max()是不是一个功能呢，表达什么意思啊，不是很明白，就是为了在一行显示嘛？

同样的问题，不知道这个MAX（）和MIN（）的作用，有的用这个有的又用那个。望高手给解释下，谢谢！
[解决办法]

if object_id('[yn]') is not null drop table [yn]
go 
create table [yn]([mainid] int,[desc] varchar(5)) 
 
insert [yn]
select 1,'aaaaa' union all
select 2,'bbbbb' union all
select 3,'cccc'

if object_id('[yns]') is not null drop table [yns]
go 
create table [yns]([mainid] int,[stage] varchar(5),[person] varchar(4))
insert [yns]
select 1,'阶段A','张三' union all
select 1,'阶段B','李四' union all
select 1,'李四',null union all
select 1,'fga','李四' union all
select 2,'阶段A','张三' union all
select 2,'阶段B','李四' union all
select 2,'阶段C','王五'

select a.mainid,a.[desc]
,b.stage,b.person
,c.stage,c.person
,d.stage,d.person
from yn a
left join yns b on a.mainid=b.mainid and b.stage='阶段A'
left join yns c on a.mainid=c.mainid and c.stage='阶段B'
left join yns d on a.mainid=d.mainid and d.stage='阶段C'
where b.stage<>'' or c.stage <> '' or d.stage <> ''

热点排行