求一个连接查询的写法?
创建实验环境表:
create table a(
code int,
cname varchar(10)
)
create table b(
zID int identity,
code int,
iDate datetime,
days float
)
insert into a values(1, 'aaa ')
insert into a values(2, 'bbb ')
insert into a values(3, 'ccc ')
insert into b values(1, '2007-4-12 ',0.5)
insert into b values(2, '2007-4-25 ',0.5)
insert into b values(3, '2007-6-12 ',4)
insert into b values(1, '2007-5-13 ',25)
insert into b values(2, '2007-3-12 ',14)
insert into b values(3, '2007-7-12 ',14)
insert into b values(1, '2007-2-20 ',7)
insert into b values(2, '2007-4-12 ',6)
insert into b values(3, '2007-6-12 ',4)
insert into b values(3, '2007-5-12 ',8)
我现在想得到的一个查询结果集的样式是
code, cname,1月份天数,2月天数,...一直到12月， 天数合计
我这样写只能得到汇总的总和，怎么改一下呢？
select a.code,m.days from a left join
(
select code,days=sum(days) from b group by code --这里先把B表的资料汇总起来,然后
--实现和a表的查询...
)m on a.code=m.code
[解决办法]
是不是要這樣的結果？
Select
a.code,
a.cname,
SUM(Case Month(iDate) When 1 Then 1 Else 0 End) As [1月份天数],
SUM(Case Month(iDate) When 2 Then 1 Else 0 End) As [2月份天数],
SUM(Case Month(iDate) When 3 Then 1 Else 0 End) As [3月份天数],
SUM(Case Month(iDate) When 4 Then 1 Else 0 End) As [4月份天数],
SUM(Case Month(iDate) When 5 Then 1 Else 0 End) As [5月份天数],
SUM(Case Month(iDate) When 6 Then 1 Else 0 End) As [6月份天数],
SUM(Case Month(iDate) When 7 Then 1 Else 0 End) As [7月份天数],
SUM(Case Month(iDate) When 8 Then 1 Else 0 End) As [8月份天数],
SUM(Case Month(iDate) When 9 Then 1 Else 0 End) As [9月份天数],
SUM(Case Month(iDate) When 10 Then 1 Else 0 End) As [10月份天数],
SUM(Case Month(iDate) When 11 Then 1 Else 0 End) As [11月份天数],
SUM(Case Month(iDate) When 12 Then 1 Else 0 End) As [12月份天数],
Count(*) As [天数合计]
From
a
Inner Join
b
On
A.code = B.code
Group By
a.code,
a.cname
[解决办法]
create table a(
code int,
cname varchar(10)
)
create table b(
zID int identity,
code int,
iDate datetime,
days float
)
insert into a values(1, 'aaa ')
insert into a values(2, 'bbb ')
insert into a values(3, 'ccc ')
insert into b values(1, '2007-4-12 ',0.5)
insert into b values(2, '2007-4-25 ',0.5)
insert into b values(3, '2007-6-12 ',4)
insert into b values(1, '2007-5-13 ',25)
insert into b values(2, '2007-3-12 ',14)
insert into b values(3, '2007-7-12 ',14)
insert into b values(1, '2007-2-20 ',7)
insert into b values(2, '2007-4-12 ',6)
insert into b values(3, '2007-6-12 ',4)
insert into b values(3, '2007-5-12 ',8)
GO
Select
a.code,
a.cname,
SUM(Case Month(iDate) When 1 Then 1 Else 0 End) As [1月份天数],
SUM(Case Month(iDate) When 2 Then 1 Else 0 End) As [2月份天数],
SUM(Case Month(iDate) When 3 Then 1 Else 0 End) As [3月份天数],
SUM(Case Month(iDate) When 4 Then 1 Else 0 End) As [4月份天数],
SUM(Case Month(iDate) When 5 Then 1 Else 0 End) As [5月份天数],
SUM(Case Month(iDate) When 6 Then 1 Else 0 End) As [6月份天数],
SUM(Case Month(iDate) When 7 Then 1 Else 0 End) As [7月份天数],
SUM(Case Month(iDate) When 8 Then 1 Else 0 End) As [8月份天数],
SUM(Case Month(iDate) When 9 Then 1 Else 0 End) As [9月份天数],
SUM(Case Month(iDate) When 10 Then 1 Else 0 End) As [10月份天数],
SUM(Case Month(iDate) When 11 Then 1 Else 0 End) As [11月份天数],
SUM(Case Month(iDate) When 12 Then 1 Else 0 End) As [12月份天数],
Count(*) As [天数合计]
From
a
Inner Join
b
On
A.code = B.code
Group By
a.code,
a.cname
GO
Drop Table a, b
--Result
/*
codecname1月份天数2月份天数3月份天数4月份天数5月份天数6月份天数7月份天数...天数合计
1aaa0101100...3
2bbb0012000...3
3ccc0000121...4
*/
[解决办法]
鱼兄，写得不错，但要是统计从第一天到第一百天的话
不是要写100句 "SUM(Case Month(iDate) When 12 Then 1 Else 0 End) As [12月份天数] "
[解决办法]
"要是统计从第一天到第一百天的话 "
什麼意思？
你不是按月統計嗎？一年只有12個月啊。
[解决办法]
select distinct c.code , c.cname ,
'1月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=1 and c.code = d.code),
'2月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=2 and c.code = d.code),
'3月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=3 and c.code = d.code),
'4月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=4 and c.code = d.code),
'5月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=5 and c.code = d.code),
'6月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=6 and c.code = d.code),
'7月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=7 and c.code = d.code),
'8月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=8 and c.code = d.code),
'9月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=9 and c.code = d.code),
'10月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=10 and c.code = d.code),
'11月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=11 and c.code = d.code),
'12月份天数 '=(select sum(days) from b as d where datename(mm,d.iDate)=12 and c.code = d.code)
from a as c left join b as e on c.code = e.code
[解决办法]
晕。。怎么变形了。。
