第一排序width,第二排序字母这个不太明白
解决方案 »
- SQL SERVER 2000 的数据库怎么导入到2008里?
- sdsdf
- 怎样用select 实现查询出指定范围的数据
- 发现这个语句是错的啊???不能执行
- 数据库名动态变化如何取出该库中某表的数据
- 如何跟踪数据储存到那一个表,并删除表内的数据。
- 最早只能在借书后三天还书:就是系统默认的当前时间+3天, 请问语法怎么用啊
- 报表(rdl)使用List控件,如何设置list的输出数据的数量?
- 关于SqlServer2005索引性能问题
- 向大家问个问题 ,谢谢大家。我能不能将select出来的结果集合, 在插入到存储过程的临时表里面去
- SqlServer 中 如何用Sql语句在一张数据表的指定位置添加字段
- 添加 外键的问题!
--测试数据
create table [table](number int,s1 char(1),s2 char(1),s3 char(1),s4 char(1),s5 char(1),quantity int,width int)
insert [table]
select 1,'a','b','s','o' ,'a' ,100,1200 union all
select 2,'8','c','c','a' ,'5' ,200,1500 union all
select 3,'9','5','9',null,null,150,1200 union all
select 4,'b','s','b','5' ,'1' ,150,1600
go--统计
select width,s1=case
when s1 between '0' and '9' then '[0-9]'
when s1 between 'a' and 'z' then '[a-z]'
else '[other]' end,
quantity=sum(quantity)
from(
select s1,quantity,width from [table]
union all
select s2,quantity,width from [table]
union all
select s3,quantity,width from [table]
union all
select s4,quantity,width from [table]
union all
select s5,quantity,width from [table]
)a group by width,case
when s1 between '0' and '9' then '[0-9]'
when s1 between 'a' and 'z' then '[a-z]'
else '[other]' end
order by width,s1
go--删除测试
drop table [table]/*--结果width s1 quantity
----------- ------- -----------
1200 [0-9] 450
1200 [a-z] 500
1200 [other] 300
1500 [0-9] 400
1500 [a-z] 600
1600 [0-9] 300
1600 [a-z] 450(所影响的行数为 7 行)
--*/
我要的结果是:以b 为例width s1 quantity 1200 1 .....
.... ... .....
1200 b 100
.... ... .....
1200 z .....
.... ... .....
1600 1 .....
.... ... .....
1600 b 300
.... ... ....
create table [table](number int,s1 char(1),s2 char(1),s3 char(1),s4 char(1),s5 char(1),quantity int,width int)
insert [table]
select 1,'a','b','s','o' ,'a' ,100,1200 union all
select 2,'8','c','c','a' ,'5' ,200,1500 union all
select 3,'9','5','9',null,null,150,1200 union all
select 4,'b','s','b','5' ,'1' ,150,1600
go--统计
select width,s1,
quantity=sum(quantity)
from(
select s1,quantity,width from [table]
union all
select s2,quantity,width from [table]
union all
select s3,quantity,width from [table]
union all
select s4,quantity,width from [table]
union all
select s5,quantity,width from [table]
)a group by width,s1
order by width,s1
go--删除测试
drop table [table]/*--结果width s1 quantity
----------- ---- -----------
1200 NULL 300
1200 5 150
1200 9 300
1200 a 200
1200 b 100
1200 o 100
1200 s 100
1500 5 200
1500 8 200
1500 a 200
1500 c 400
1600 1 150
1600 5 150
1600 b 300
1600 s 150(所影响的行数为 15 行)
--*/