求疑难SQL,考验您的SQL能力.求解.

Question

数据结构:
name type1 type2  total  avg
A     t     t1     5      7
A     tt    tt2    1      10
A     ttt   ttt3   2      2
A     tttt  tttt4  3      8
b     bt     bt1   6      3
b     btt    btt2  2      1
b     btt    btt3  1      6sql 后希望 为:name  total    (a)t/t1   (b)tt/tt2  (c)ttt/ttt3   (d)tttt/tttt4
A     11        7       10             2                8
b     9         3        1             6                X

Answer 1

(a)t/t1  (b)tt/tt2  (c)ttt/ttt3  (d)tttt/tttt4 
没看懂

Answer 2

a  b c d 可以是任意的名字, tt 对应原结构.

Answer 3

你需要干两件事，先把type1和type2合并，然后再进行行转列。

Answer 4

对不起,有一点要更改一下,
type1 type2 (type1两种状态 p1,p2.type2有两种状态L1 ,L2),那么行转换后,最多有四种组合.

Answer 5

数据结构:
name type1 type2  total  avg
A     P1     L1     5      7
A     P2     L1     1      10
A     P1    L2      2       2
A     P2    L2      3      8
b     P1    L1      6      3
b     P1    L2      2      1
b     P2    L1      1      6sql 后希望 为:name  total     a(P1/L1)      b(P2/L1)    c(P1/L2)       d (P2/L2)
A     11        7             10                2                8
b     9         3             1                 6                X

Answer 6

后3行的type1是b开头的，有什么规律吗？

Answer 7

改正,12楼,就是说type1 type2,最多有四种组合,有时候可能为1 or 2 or 3 种组合

Answer 8

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[type1] varchar(4),[type2] varchar(5),[total] int,[avg] int)
insert [tb]
select 'A','t','t1',5,7 union all
select 'A','tt','tt2',1,10 union all
select 'A','ttt','ttt3',2,2 union all
select 'A','tttt','tttt4',3,8 union all
select 'b','bt','bt1',6,3 union all
select 'b','btt','btt2',2,1 union all
select 'b','btt','btt3',1,6
 
---查询---
select tid=identity(int,1,1),* into # from tbselect
  name,
  sum(total) as total,
  max(case when px=1 then [avg]  end) as [(a)t/t1],
  max(case when px=2 then [avg]  end) as [(b)tt/tt2],
  max(case when px=3 then [avg]  end) as [(c)ttt/ttt3],
  max(case when px=4 then [avg]  end) as [(d)tttt/tttt40]
from 
  (select *,px=(select count(1)+1 from # where name=t.name and tid<t.tid) from # t) a
group by
  name
---结果---
name total       (a)t/t1     (b)tt/tt2   (c)ttt/ttt3 (d)tttt/tttt40 
---- ----------- ----------- ----------- ----------- -------------- 
A    11          7           10          2           8
b    9           3           1           6           NULL（所影响的行数为 2 行）

Answer 9

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
create table TB(
[NAME] VARCHAR(50)
,TYPE1 VARCHAR(10)
,TYPE2 VARCHAR(20)
,TOTAL INT
,[AVG] INT
) 
INSERT INTO TB
SELECT 'A','P1','L1',      5,      7 UNION ALL
SELECT 'A','P2','L1',      1,      10 UNION ALL 
SELECT 'A','P1','L2',      2,      2 UNION ALL 
SELECT 'A','P2','L2',      3,      8 UNION ALL 
SELECT 'b','P1','L1',      6,      3 UNION ALL 
SELECT 'b','P1','L2',      2,      1 UNION ALL 
SELECT 'b','P2','L1',      1,      6 UNION ALL 
SELECT * FROM TBSELECT [NAME],SUM(TOTAL) 'TOTAL'
,SUM(CASE WHEN TYPE1='P1' AND TYPE2='L1' THEN [AVG] ELSE 0 END) 'A'
,SUM(CASE WHEN TYPE1='P2' AND TYPE2='L1' THEN [AVG] ELSE 0 END) 'B'
,SUM(CASE WHEN TYPE1='P1' AND TYPE2='L2' THEN [AVG] ELSE 0 END) 'C'
,SUM(CASE WHEN TYPE1='P2' AND TYPE2='L2' THEN [AVG] ELSE 0 END) 'D'
FROM TB 
GROUP BY [NAME]
/*
A 11 27 20 10 8
b 9 10 7 0 0
*/

Answer 10

if object_id('[tbtest]') is not null drop table [tbtest]
go 
create table [tbtest](name char(1),type1 varchar(3),type2 varchar(5), total int, [avg] int )
insert tbtest select
'A',    'p1'    ,  'l1'    , 5      ,7  union all select 
'A' ,   'p2'    ,  'l1'    ,1     , 10 union all select 
'A'  ,  'p1'  ,    'l2'   ,2    ,  2 union all select 
'A'   , 'p2'  ,    'l2',  3   ,   8 union all select 
'b'    ,'p1'   ,   'l1'  ,  6  ,    3 union all select 
'b'    ,'p1' ,     'l2'  , 2 ,     1 union all select 
'b'    ,'p2',      'l1' ,  1,      6 select 
name,
total=sum(total),
a=max(case when type1='p1' and type2='l1' then [avg]  else null end ),
b=max(case when type1='p2' and type2='l1' then [avg]  else null end ),
c=max(case when type1='p1' and type2='l2' then [avg]  else null end ),
d=max(case when type1='p2' and type2='l2' then [avg]  else null end)
from tbtest
group by namename total       a           b           c           d
---- ----------- ----------- ----------- ----------- -----------
A    11          7           10          2           8
b    9           3           6           1           NULL
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)

Answer 11

thanks all, 我试上面的SQL,我会回复.

Answer 12

OK.
# josy
# (百年树人)# guguda2008
# (摘星星的烤鸭)# js_szy
# (华夏小卒)感谢!万分!

Answer 13

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-08 12:58:36.700●●●●●
　★★★★★soft_wsx★★★★★
*/
--select GETDATE()
if object_id('tb') is not null drop table tb
  go
create table tb(id int identity(1,1),name nvarchar(10),type1 nvarchar(10), type2 nvarchar(10),  total int,  avg int)
  go
insert into tb(name,type1,type2,total,avg)
          select 'A',    't'   , 't1'    ,  5,      7 
union all select 'A',    'tt'  ,  'tt2'  ,  1,      10 
union all select 'A',    'ttt' , 'ttt3'  ,  2,      2 
union all select 'A',    'tttt',  'tttt4',  3,      8 
union all select 'b',    'bt' ,    'bt1',   6,      3 
union all select 'b',    'btt',    'btt2',  2,      1 
union all select 'b',    'bttt',    'btt3',  1,      6 select SUBSTRING(type1,2,10) from tb where type1 not like 'T%'
declare @sql nvarchar(4000)
SET @sql=N'select [name],sum(total) as total'   --初始化变量必须
select @sql=@sql+N','+
                  QUOTENAME(N'('+char(64+id)+N')'+type1+N'/'+type2)+
                    N'=max(
                            case when case when id>4 then substring(type1,2,10) else type1 end='+quotename(type1,N'''')
                            +N' then avg else 0 end)'
                    from (select distinct id,type1,type2 from tb where name='A')a
set @sql=@sql+N' from tb group by name'  
 --print @sqlexec(@sql)select [name],sum(total) as total,[(A)t/t1]=max(
                            case when case when id>4 then substring(type1,2,10) else type1 end='t' then avg else 0 end),[(B)tt/tt2]=max(
                            case when case when id>4 then substring(type1,2,10) else type1 end='tt' then avg else 0 end),[(C)ttt/ttt3]=max(
                            case when case when id>4 then substring(type1,2,10) else type1 end='ttt' then avg else 0 end),[(D)tttt/tttt4]=max(
                            case when case when id>4 then substring(type1,2,10) else type1 end='tttt' then avg else 0 end) from tb group by name/*
name total (A)t/t1 (B)tt/tt2 (C)ttt/ttt3 (D)tttt/tttt4
A 11 7 10 2 8
b 9 3 1 6 0
*/网速太慢了！希望还没有结果

Answer 14

注意自己的需求，如果A中有多个P1/L1，最终的结果如果是求和就用SUM，如果是求最大就用MAX

Answer 15

对于同一个name不会有重复的p1/l1,那么现在max和sum是同样的功能?

Answer 16

有一个小的问题,就是能否把 'a'  'b' 'c' 'd' 改成对应 Pl/L1 P1/l2 p2/l1 p2/l2

Answer 17

基本是一样的，差别就在空字段上，SUM显示0，MAX显示NULL

Answer 18

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
create table TB(
[NAME] VARCHAR(50)
,TYPE1 VARCHAR(10)
,TYPE2 VARCHAR(20)
,TOTAL INT
,[AVG] INT
) 
INSERT INTO TB
SELECT 'A','P1','L1',      5,      7 UNION ALL
SELECT 'A','P2','L1',      1,      10 UNION ALL 
SELECT 'A','P1','L2',      2,      2 UNION ALL 
SELECT 'A','P2','L2',      3,      8 UNION ALL 
SELECT 'b','P1','L1',      6,      3 UNION ALL 
SELECT 'b','P1','L2',      2,      1 UNION ALL 
SELECT 'b','P2','L1',      1,      6 UNION ALL 
SELECT * FROM TBSELECT [NAME],SUM(TOTAL) 'TOTAL'
,CAST(SUM(CASE WHEN TYPE1='P1' AND TYPE2='L1' THEN [AVG] ELSE 0 END) AS VARCHAR(10)) 'Pl/L1'
,SUM(CASE WHEN TYPE1='P2' AND TYPE2='L1' THEN [AVG] ELSE 0 END) 'P2/L1'
,SUM(CASE WHEN TYPE1='P1' AND TYPE2='L2' THEN [AVG] ELSE 0 END) 'Pl/L2'
,SUM(CASE WHEN TYPE1='P2' AND TYPE2='L2' THEN [AVG] ELSE 0 END) 'Pl/L2'
FROM TB 
GROUP BY [NAME]
/*
NAME TOTAL P1/L1 P2/L1 P1/L2 P2/L2
A 11 7 10 2 8
b 9 3 6 1 0
*/