读书人
栏目▼

求写一个SQL解决思路

发布时间： 2012-12-17 09:31:40 作者： rapoo

求写一个SQL
原表：


<table border="1">
<tr><td>Id</td><td>type</td><td>days</td><tr/>
<tr><td>1</td><td>TYPEA1</td><td>3</td><tr/>
<tr><td>1</td><td>TYPEA2</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEA3</td><td>5</td><tr/>
<tr><td>1</td><td>TYPEB1</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEB2</td><td>3</td><tr/>
<tr><td>1</td><td>TYPEB3</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEC1</td><td>5</td><tr/>
<tr><td>1</td><td>TYPEC2</td><td>4</td><tr/>
<tr><td>1</td><td>TYPEC3</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEA1</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEA2</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEA3</td><td>5</td><tr/>
<tr><td>2</td><td>TYPEB1</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEB2</td><td>3</td><tr/>
<tr><td>2</td><td>TYPEB3</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEC1</td><td>5</td><tr/>
<tr><td>2</td><td>TYPEC2</td><td>4</td><tr/>
<tr><td>2</td><td>TYPEC3</td><td>3</td><tr/>
</table>

要求输出结果:

<table border="1">
<tr><td>Id</td><td>TYPEA</td><td>TYPEB</td><td>TYPEC</td><tr/>
<tr><td>1</td><td>12</td><td>11</td><td>12</td><tr/>
<tr><td>2</td><td>12</td><td>11</td><td>12</td><tr/>
</table>

求SQL,谢谢。
[最优解释]

declare @t table (Id int,type varchar(6),days int)
insert into @t
select 1,'TYPEA1',3 union all
select 1,'TYPEA2',4 union all
select 1,'TYPEA3',5 union all
select 1,'TYPEB1',4 union all
select 1,'TYPEB2',3 union all
select 1,'TYPEB3',4 union all
select 1,'TYPEC1',5 union all
select 1,'TYPEC2',4 union all
select 1,'TYPEC3',3 union all
select 2,'TYPEA1',3 union all
select 2,'TYPEA2',4 union all
select 2,'TYPEA3',5 union all
select 2,'TYPEB1',4 union all
select 2,'TYPEB2',3 union all
select 2,'TYPEB3',4 union all
select 2,'TYPEC1',5 union all
select 2,'TYPEC2',4 union all
select 2,'TYPEC3',3

;with maco as
(
select 
ID,left(type,5) as type,sum(days) as days 
from @t group by ID,left(type,5)
)

select 
ID ,
sum(case when type='TYPEA' then days else 0 end) as TYPEA, 
sum(case when type='TYPEB' then days else 0 end) as TYPEB,  
 
sum(case when type='TYPEC' then days else 0 end) as TYPEC
from maco group by ID

/*
ID          TYPEA       TYPEB       TYPEC
----------- ----------- ----------- -----------
1           12          11          12
2           12          11          12

(2 row(s) affected)
*/


[其他解释]
额。。。。好像没形成表格，大家能看懂就行，或者帮我发成表格形式？
[其他解释]
另外如果ID没有对应的TYPEA,TYPEB,TYPEC,则为0
[其他解释]
有会的高手帮我写下吗？
[其他解释]

给你顶个 顺带看看新论坛我的头像图片是不是变长了
[其他解释]
木有变长。。。。。。。。。。
[其他解释]
叶子，能帮我改下么？因为我这实际项目中的value不是TYPEA1,TYPEA2,TYPEA3这种有规律的,比如是固定的3,4种value会把days加在一起。

declare @t table (Id int,type varchar(6),days int)
insert into @t
select 1,'avalue1(TYPEA1)',3 union all
select 1,'aavalue2(TYPEA2)',4 union all
select 1,'aaavalue3(TYPEA3)',5 union all
select 1,'bvalue1(TYPEB1)',4 union all
select 1,'bbvalue2(TYPEB2)',3 union all
select 1,'bbbvalue3(TYPEB3)',4 union all
select 1,'cvalue1(TYPEC1)',5 union all
select 1,'ccvalue2(TYPEC2)',4 union all
select 1,'cccvalue3(TYPEC3)',3 union all
select 2,'avalue1(TYPEA1)',3 union all
select 2,'aavalue2(TYPEA2)',4 union all
select 2,'aaavalue3(TYPEA3)',5 union all
select 2,'bvalue1(TYPEB1)',4 union all
select 2,'bbvalue2(TYPEB2)',3 union all
select 2,'bbbvalue3(TYPEB3)',4 union all
select 2,'cvalue1(TYPEC1)',5 union all
select 2,'ccvalue2(TYPEC2)',4 union all
select 2,'cccvalue3(TYPEC3)',3

谢谢
[其他解释]

declare @t table (Id int,type varchar(10),days int)
insert into @t
select 1,'avalue1',3 union all
select 1,'aavalue2',4 union all
select 1,'aaavalue3',5 union all
select 1,'bvalue1',4 union all
select 1,'bbvalue2',3 union all
select 1,'bbbvalue3',4 union all
select 1,'cvalue1',5 union all
select 1,'ccvalue2',4 union all
select 1,'cccvalue3',3 union all
select 2,'avalue1',3 union all
select 2,'aavalue2',4 union all
select 2,'aaavalue3',5 union all
select 2,'bvalue1',4 union all
select 2,'bbvalue2',3 union all
select 2,'bbbvalue3',4 union all
select 2,'cvalue1',5 union all
select 2,'ccvalue2',4 union all 
 
select 2,'cccvalue3',3

;with maco as
(
select 
ID,left(type,1) as type,sum(days) as days 
from @t group by ID,left(type,1)
)

select 
ID ,
sum(case when type='a' then days else 0 end) as TYPEA, 
sum(case when type='b' then days else 0 end) as TYPEB, 
sum(case when type='c' then days else 0 end) as TYPEC
from maco group by ID

/*
ID          TYPEA       TYPEB       TYPEC
----------- ----------- ----------- -----------
1           12          11          12
2           12          11          12

(2 row(s) affected)
*/

[其他解释]
额。。。。。。又被弄成有规律的了，我这样实现的，叶子帮我看看还可以优化一下不，我怕数据量大了，就要弄分表，读写分离之类的。

declare @t table (Id int,type varchar(10),days int)
insert into @t
select 1,'avalue1',3 union all
select 1,'aavalue2',4 union all
select 1,'aaavalue3',5 union all
select 1,'bvalue1',4 union all
select 1,'bbvalue2',3 union all
select 1,'bbbvalue3',4 union all
select 1,'cvalue1',5 union all
select 1,'ccvalue2',4 union all
select 1,'cccvalue3',3 union all
select 2,'avalue1',3 union all
select 2,'aavalue2',4 union all
select 2,'aaavalue3',5 union all
select 2,'bvalue1',4 union all
select 2,'bbvalue2',3 union all
select 2,'bbbvalue3',4 union all
select 2,'cvalue1',5 union all
select 2,'ccvalue2',4 union all
select 2,'cccvalue3',3
 
select 
    ID ,
    sum(case type 
when 'avalue1' then days 
when 'aavalue2' then days 
when 'aaavalue3' then days 
else 0 end) as TYPEA, 
    sum(case type 
when 'bvalue1' then days 
when 'bbvalue2' then days 
when 'bbbvalue3' then days 
else 0 end) as TYPEB, 
    sum(case type 
when 'cvalue1' then days 
when 'ccvalue2' then days 
when 'cccvalue3' then days 
else 0 end) as TYPEC
from @t group by ID
 
/*
ID          TYPEA       TYPEB       TYPEC
----------- ----------- ----------- -----------
1           12          11          12
2           12          11          12 
 
 
(2 row(s) affected)
*/

[其他解释]
你这个表貌似没有主键？
[其他解释]
ID就是主键,我只是把我遇到的项目问题抽象成这个了。
[其他解释]
null

读书人网 >SQL Server

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net