create table test(id varchar(10),name varchar(10),tons int , quantity int,d1 datetime,d2 datetime,
d3 datetime)
insert into test select 1,'a',1,2,'2007-10-01','2007-10-01','2007-10-01'select name,
sum(case when d1='2007-10-01' then tons else 0 end) as d1tons,
sum(case when d1='2007-10-01' then quantity else 0 end) as d1quantity,
sum(case when d1='2007-10-01' then tons else 0 end) as d2tons,
sum(case when d1='2007-10-01' then quantity else 0 end) as d2quantity,
sum(case when d1='2007-10-01' then tons else 0 end) as d3tons,
sum(case when d1='2007-10-01' then quantity else 0 end) as d3quantity
from test
group by name
drop table test
解决方案 »
- 求高手给个SQL查询代码,谢谢
- 删除重复记录
- 【100分】为什么这段调用数据库代码在网页端未能获取并显示任何内容?
- "外键中引用列的数目与被引用列的数目不等"是怎么回事?
- 求百分比/横向排列
- 我在建存诸过程中,declare @aa numeric,set @sql='select @aa=a from b' exec (@sql),竟出错了说@aa 没有定义呀, 注:@sql 一定要是字符串
- SQL存储过程错误问题
- 一个表有A,B,C,D 四个字段(都位数值类型) 其中D字段的值 =A字段的值 + B字段的值 - C字段的值 ;在插入/修改记录时我应怎么处理D字段的值
- 关于嵌套存储过程的问题!高手快来!
- ACCESS如何访问并显示SQL server中的image类型的字段
- 求助一个关于时间的sql语句
- SQL Server 2005 书
name,
sum(case when date1='2007-10-1' then Tons end) as Date1Tons,
sum(case when date1='2007-10-1' then quantity end) as Date1Quantity,
sum(case when date2='2007-10-1' then Tons end) as Date2Tons,
sum(case when date2='2007-10-1' then quantity end) as Date2Quantity,
sum(case when date3='2007-10-1' then Tons end) as Date3Tons,
sum(case when date3='2007-10-1' then quantity end) as Date3Quantity
from
Test
group by
name
declare @Test table(id int,name varchar(10),Tons int,quantity int,date1 varchar(10),date2 varchar(10),date3 varchar(10))
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01')
select
name,
sum(case when date1='2007-10-01' then Tons end) as Date1Tons,
sum(case when date1='2007-10-01' then quantity end) as Date1Quantity,
sum(case when date2='2007-10-01' then Tons end) as Date2Tons,
sum(case when date2='2007-10-01' then quantity end) as Date2Quantity,
sum(case when date3='2007-10-01' then Tons end) as Date3Tons,
sum(case when date3='2007-10-01' then quantity end) as Date3Quantity
from
@Test
group by
name/*
name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
---------- ----------- ------------- ----------- ------------- ----------- -------------
a 3 1 2 1 3 1
b 4 2 1 1 1 1
c 1 1 NULL NULL NULL NULL
*/
select A.name as name, Date1Tons, Date1Quantity, Date2Tons, Date2Quantity, Date3Tons, Date3Quantity
from (select name, Date1Tons=sum(Tons), Date1Quantity=sum(quantity) from Test where datediff(day,date1,'2007-10-1')=0) A
full join (select name, Date2Tons=sum(Tons), Date2Quantity=sum(quantity) from Test where datediff(day,date2,'2007-10-1')=0) B on A.name=B.name
full join (select name, Date3Tons=sum(Tons), Date3Quantity=sum(quantity) from Test where datediff(day,date3,'2007-10-1')=0) C on A.name=C.name
or B.name=C.name
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01') select name=isnull(A.name,isnull(B.name,C.name)), Date1Tons, Date1Quantity, Date2Tons, Date2Quantity, Date3Tons, Date3Quantity
from (select name, Date1Tons=sum(Tons), Date1Quantity=sum(quantity) from @Test where datediff(day,date1,'2007-10-1')=0 group by name) A
full join (select name, Date2Tons=sum(Tons), Date2Quantity=sum(quantity) from @Test where datediff(day,date2,'2007-10-1')=0 group by name) B on A.name=B.name
full join (select name, Date3Tons=sum(Tons), Date3Quantity=sum(quantity) from @Test where datediff(day,date3,'2007-10-1')=0 group by name) C on A.name=C.name
or B.name=C.name/*
name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
---------- ----------- ------------- ----------- ------------- ----------- -------------
a 3 1 2 1 3 1
b 4 2 1 1 1 1
c 1 1 NULL NULL NULL NULL(3 row(s) affected)
*/
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01') -- use function coalesce to instead isnull
select coalesce(A.name,B.name,C.name), Date1Tons, Date1Quantity, Date2Tons, Date2Quantity, Date3Tons, Date3Quantity
from (select name, Date1Tons=sum(Tons), Date1Quantity=sum(quantity) from @Test where datediff(day,date1,'2007-10-1')=0 group by name) A
full join (select name, Date2Tons=sum(Tons), Date2Quantity=sum(quantity) from @Test where datediff(day,date2,'2007-10-1')=0 group by name) B on A.name=B.name
full join (select name, Date3Tons=sum(Tons), Date3Quantity=sum(quantity) from @Test where datediff(day,date3,'2007-10-1')=0 group by name) C on A.name=C.name
or B.name=C.name/*
name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
---------- ----------- ------------- ----------- ------------- ----------- -------------
a 3 1 2 1 3 1
b 4 2 1 1 1 1
c 1 1 NULL NULL NULL NULL(3 row(s) affected)
*/
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01') -- use function coalesce to instead isnull
select coalesce(A.name,B.name,C.name), Date1Tons, Date1Quantity, Date2Tons, Date2Quantity, Date3Tons, Date3Quantity
from (select name, Date1Tons=sum(Tons), Date1Quantity=sum(quantity) from @Test where datediff(day,date1,'2007-10-1')=0 group by name) A
full join (select name, Date2Tons=sum(Tons), Date2Quantity=sum(quantity) from @Test where datediff(day,date2,'2007-10-1')=0 group by name) B on A.name=B.name
full join (select name, Date3Tons=sum(Tons), Date3Quantity=sum(quantity) from @Test where datediff(day,date3,'2007-10-1')=0 group by name) C on A.name=C.name
or B.name=C.name/*
name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
---------- ----------- ------------- ----------- ------------- ----------- -------------
a 3 1 2 1 3 1
b 4 2 1 1 1 1
c 1 1 NULL NULL NULL NULL(3 row(s) affected)
*/
有一张这样的表Test如下:
id name Tons quantity date1 date2 date3
1 a 2 1 2007-09-01 2007-10-01 2007-07-01
2 a 3 1 2007-10-01 2007-07-01 2007-10-01
3 b 4 1 2007-08-01 2007-07-01 2007-11-01
4 b 1 1 2007-10-01 2007-10-01 2007-10-01
5 b 3 1 2007-10-01 2007-01-01 2007-11-01
6 c 1 1 2007-9-01 2007-01-01 2007-11-01 我想生成一个新表条件是,分别分组统计提取满足 date1=2007-10-1 ,date2=2007-10-1, date3=2007-10-1 的记录表组成一张新表 name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
a 3 1 2 1 3 1
b 4 2 1 1 1 1最后一问,完了结贴
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01')
select
name,
sum(case when date1='2007-10-01' then Tons end) as Date1Tons,
sum(case when date1='2007-10-01' then quantity end) as Date1Quantity,
sum(case when date2='2007-10-01' then Tons end) as Date2Tons,
sum(case when date2='2007-10-01' then quantity end) as Date2Quantity,
sum(case when date3='2007-10-01' then Tons end) as Date3Tons,
sum(case when date3='2007-10-01' then quantity end) as Date3Quantity
from
@Testgroup by
namehaving sum(case when date1='2007-10-01' then Tons end) >0
and sum(case when date1='2007-10-01' then quantity end)>0
and sum(case when date2='2007-10-01' then Tons end)>0
and sum(case when date2='2007-10-01' then quantity end)>0
and sum(case when date3='2007-10-01' then Tons end)>0
and sum(case when date3='2007-10-01' then quantity end)>0
有一张这样的表Test如下:
id name Tons quantity date1 date2 date3
1 a 2 1 2007-09-01 2007-10-01 2007-07-01
2 a 3 1 2007-10-01 2007-07-01 2007-10-01
3 b 4 1 2007-08-01 2007-07-01 2007-11-01
4 b 1 1 2007-10-01 2007-10-01 2007-10-01
5 b 3 1 2007-10-01 2007-01-01 2007-11-01
6 c 1 1 2007-9-01 2007-01-01 2007-11-01 我想生成一个新表条件是,分别分组统计提取满足 date1=2007-10-1 ,date2=2007-10-1, date3=2007-10-1 的记录表组成一张新表 name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
a 3 1 2 1 3 1
b 4 2 1 1 1 1 最后一问,完了结贴
-------------------declare @Test table(id int,name varchar(10),Tons int,quantity int,date1 varchar(10),date2 varchar(10),date3 varchar(10))
insert into @Test values(1,'a',2,1,'2007-09-01','2007-10-01','2007-07-01')
insert into @Test values(2,'a',3,1,'2007-10-01','2007-07-01','2007-10-01')
insert into @Test values(3,'b',4,1,'2007-08-01','2007-07-01','2007-11-01')
insert into @Test values(4,'b',1,1,'2007-10-01','2007-10-01','2007-10-01')
insert into @Test values(5,'b',3,1,'2007-10-01','2007-01-01','2007-11-01')
insert into @Test values(6,'c',1,1,'2007-10-01','2007-01-01','2007-11-01') -- full join 改为 join
select coalesce(A.name,B.name,C.name), Date1Tons, Date1Quantity, Date2Tons, Date2Quantity, Date3Tons, Date3Quantity
from (select name, Date1Tons=sum(Tons), Date1Quantity=sum(quantity) from @Test where datediff(day,date1,'2007-10-1')=0 group by name) A
join (select name, Date2Tons=sum(Tons), Date2Quantity=sum(quantity) from @Test where datediff(day,date2,'2007-10-1')=0 group by name) B on A.name=B.name
join (select name, Date3Tons=sum(Tons), Date3Quantity=sum(quantity) from @Test where datediff(day,date3,'2007-10-1')=0 group by name) C on A.name=C.name/*
name Date1Tons Date1Quantity Date2Tons Date2Quantity Date3Tons Date3Quantity
---------- ----------- ------------- ----------- ------------- ----------- -------------
a 3 1 2 1 3 1
b 4 2 1 1 1 1(2 row(s) affected)
*/