求写一个SQL解决思路

2012-12-17

求写一个SQL原表：table border1trtdId/tdtdtype/tdtddays/tdtr/trtd1/tdtdT

求写一个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

谢谢
[其他解释]

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

求写一个SQL解决思路