求写一个SQL

[jiujiang88]

原表：

<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,谢谢。

[jiujiang88]

额。。。。好像没形成表格，大家能看懂就行，或者帮我发成表格形式？

[jiujiang88]

另外如果ID没有对应的TYPEA,TYPEB,TYPEC,则为0

[jiujiang88]

有会的高手帮我写下吗？

[老白猫]

给你顶个 顺带看看新论坛我的头像图片是不是变长了

[叶子]

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)
*/

[jiujiang88]

木有变长。。。。。。。。。。

[jiujiang88]

叶子，能帮我改下么？因为我这实际项目中的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)
*/

[jiujiang88]

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

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)
*/

[叶子]

你这个表貌似没有主键？

[jiujiang88]

ID就是主键,我只是把我遇到的项目问题抽象成这个了。

[xb12369]