表A
--------
peono |conmoney |condate |contype |condept0001 |1.00 |2007-07-28 11:45:29.000 |消费 |食堂A
0002 |2.00 |2007-07-28 23:45:29.000 |消费 |食堂A
0001 |1.00 |2007-07-29 13:45:29.000 |消费 |食堂A
0002 |1.50 |2007-07-30 01:45:29.000 |消费 |食堂B
0002 |1.00 |2007-07-30 23:45:29.000 |消费 |食堂A
0001 |2.00 |2007-07-31 10:45:29.000 |消费 |食堂B
0003 |1.50 |2007-07-31 11:45:29.000 |消费 |食堂A
0003 |1.00 |2007-07-31 15:45:29.000 |消费 |食堂A如何通过查询语句得到结果:食堂A
-------------
condate |summoney
2007-07-28 |3.00
2007-07-29 |1.00
2007-07-30 |1.00
2007-07-31 |2.50食堂B
------------
condate |summoney
2007-07-28 |0.00
2007-07-29 |0.00
2007-07-30 |1.50
2007-07-31 |2.00
--------
peono |conmoney |condate |contype |condept0001 |1.00 |2007-07-28 11:45:29.000 |消费 |食堂A
0002 |2.00 |2007-07-28 23:45:29.000 |消费 |食堂A
0001 |1.00 |2007-07-29 13:45:29.000 |消费 |食堂A
0002 |1.50 |2007-07-30 01:45:29.000 |消费 |食堂B
0002 |1.00 |2007-07-30 23:45:29.000 |消费 |食堂A
0001 |2.00 |2007-07-31 10:45:29.000 |消费 |食堂B
0003 |1.50 |2007-07-31 11:45:29.000 |消费 |食堂A
0003 |1.00 |2007-07-31 15:45:29.000 |消费 |食堂A如何通过查询语句得到结果:食堂A
-------------
condate |summoney
2007-07-28 |3.00
2007-07-29 |1.00
2007-07-30 |1.00
2007-07-31 |2.50食堂B
------------
condate |summoney
2007-07-28 |0.00
2007-07-29 |0.00
2007-07-30 |1.50
2007-07-31 |2.00
A.condept,
A.condate,
SUM(B.conmoney) As summoney
From
(
Select T1.*, T2.* From(
Select Distinct condept From A) T1
Cross Join (
Select Distinct Convert(Varchar(10), condate, 120) As condate From A) T2) A
Left Join
A B
On A.condept = B.condept And A.condate = Convert(Varchar(10), condate, 120)
Group By
A.condept,
A.condate
condept,
Convert(varchar(10),Condate,120) as Condate,
Sum(conmoney) as SumMoney
From TableName
Group By Condept,
Convert(varchar(10),Condate,120)
create table table1
(
peono int,
conmoney money,
condate datetime,
contype varchar(10),
condept varchar(10)
)insert into table1 (peono,conmoney,condate,contype,condept) values (1,1,'2007-07-28 11:45:29.000','消费','食堂A')
insert into table1 (peono,conmoney,condate,contype,condept) values (2,2,'2007-07-28 23:45:29.000','消费','食堂A')
insert into table1 (peono,conmoney,condate,contype,condept) values (1,1,'2007-07-29 13:45:29.000','消费','食堂A')
insert into table1 (peono,conmoney,condate,contype,condept) values (2,1.5,'2007-07-30 01:45:29.000','消费','食堂B')
insert into table1 (peono,conmoney,condate,contype,condept) values (2,1,'2007-07-30 23:45:29.000','消费','食堂A')
insert into table1 (peono,conmoney,condate,contype,condept) values (1,2,'2007-07-31 10:45:29.000','消费','食堂B')
insert into table1 (peono,conmoney,condate,contype,condept) values (3,1.5,'2007-07-31 11:45:29.000','消费','食堂A')
insert into table1 (peono,conmoney,condate,contype,condept) values (3,1,'2007-07-31 15:45:29.000','消费','食堂A')
*/select * from table1
select a.condateone, sum(a.conmoney) as summoney from (
select CONVERT(varchar(12) , condate, 111 ) as condateone,conmoney,peono,condept from table1 where condept = '食堂A'
) as a group by a.condateoneselect a.condateone, sum(a.conmoney) as summoney from (
select CONVERT(varchar(12) , condate, 111 ) as condateone,conmoney,peono,condept from table1 where condept = '食堂B'
) as a group by a.condateone不过食堂B中的数据为0的没有显示出来,吃饭去了,没时间改了,自己弄下吧