求助：一个统计，我写了N久了写不好。

Question

现在有三张表
表 House
--------
HouseID
Name
Address(地址)表 Detail
---------
DetailID
HouseID(和House连接)
PeopleID
Date(日期)表 People
---------
PeopleID(和Detail连接)
Brithday(生日)
希望能查出这样的一张统计表格：
-------------------------------------------------
[month]  <18岁      19-30岁        30岁以上
1         10%       50%            40%
2         20%       40%            40%
总计       15%        45%            40%写了好久，写不出来，求助!! 尽量考虑效率最好..谢谢

Answer 1

对了，漏了个条件，是根据address查

Answer 2

month
是表示那个表的month啊？

Answer 3

似乎只需要根据People 表统计就行了？？
要不楼主贴点数据出来？

Answer 4

日期是在Detail表里，即是Date字段。

Answer 5

表   People 
--------- 
PeopleID(和Detail连接) 
Brithday(生日) ---------------
brithday.生日不是根据这个字段来么？？

Answer 6

数据：例如：表house
------------------
451 绿洲花园A块     沙浦路128弄3号502室
452 绿洲花园B块     沙浦路128弄3号503室
453 美兰湖颐景园    美艾路198弄4号201室
454 美兰湖颐景园A   美艾路198弄4号202室
表 Detail
-------------------------------
1    451     1      2007-10-11
2    452     2      2007-11-11
3    453     3      2007-12-11
4    454     1      2007-12-12表 People
-----------------------------
1      1978-9-14
2      1985-3-21
3      1990-4-22

Answer 7

select datepart(mm,c.birthday) inmonth,
sum(case when datediff(yyyy,c.birthday,getdate()) < 18 then datediff(yyyy,c.birthday,getdate()) else 0 end) as c1,
sum(case when datediff(yyyy,c.birthday,getdate()) between 18 and 30 then datediff(yyyy,c.birthday,getdate()) else 0 end) as c2,
sum(case when datediff(yyyy,c.birthday,getdate()) > 30 then datediff(yyyy,c.birthday,getdate()) else 0 end) as c3
from people c
group by datepart(mm,c.birthday)

Answer 8

select month(brithday)[month] ,
'<18岁'=sum(case when datediff(yy,brithday,getdate())<18 then 1 else 0 end),
'19-30岁'=sum(case when datediff(yy,brithday,getdate())<30 and datediff(yy,brithday,getdate())>18  then 1 else 0 end),
'30岁以上'=sum(case when datediff(yy,brithday,getdate())>30 then 1 else 0 end) 
from people
group by month(brithday)

Answer 9

月份 month 是上一个表 Detail 的Date字段，而且还要根据 House表的address 查。

Answer 10

SELECT [month]=MONTH([People].[Brithday])
       ,[<18岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())<18 THEN 1 ELSE 0 END)
       ,[18-30岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>=18 AND DATEDIFF(YEAR,[People].[Brithday],GETDATE())<=30 THEN 1 ELSE 0 END)
       ,[30岁以上]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>30 THEN 1 ELSE 0 END)
FROM [People]
INNER JOIN [Detail] ON [People].[PeopleID]=[Detail].[PeopleID]
INNER JOIN [House] ON [House].[HouseID]=[Detail].[HouseID]
WHERE [House].[Address] LIKE '%'+@address+'%'

Answer 11

月份   month   是上一个表   Detail   的Date字段，而且还要根据   House表的address   查。则改为SELECT [month]=MONTH([Detail].[Date])
       ,[<18岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())<18 THEN 1 ELSE 0 END)
       ,[18-30岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>=18 AND DATEDIFF(YEAR,[People].[Brithday],GETDATE())<=30 THEN 1 ELSE 0 END)
       ,[30岁以上]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>30 THEN 1 ELSE 0 END)
FROM [People]
INNER JOIN [Detail] ON [People].[PeopleID]=[Detail].[PeopleID]
INNER JOIN [House] ON [House].[HouseID]=[Detail].[HouseID]
WHERE [House].[Address] LIKE '%'+@address+'%'

Answer 12

忘了 最后应该GROUP BY 一下
SELECT [month]=MONTH([Detail].[Date])
       ,[<18岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())<18 THEN 1 ELSE 0 END)
       ,[18-30岁]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>=18 AND DATEDIFF(YEAR,[People].[Brithday],GETDATE())<=30 THEN 1 ELSE 0 END)
       ,[30岁以上]=SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>30 THEN 1 ELSE 0 END)
FROM [People]
INNER JOIN [Detail] ON [People].[PeopleID]=[Detail].[PeopleID]
INNER JOIN [House] ON [House].[HouseID]=[Detail].[HouseID]
WHERE [House].[Address] LIKE '%'+@address+'%'
GROUP BY MONTH([Detail].[Date])

Answer 13

create table Detail(DetailID int,HouseID int,PeopleID int,[Date] datetime)
insert into Detail
select 1,         451,           1,             '2007-10-11' union all
select 2,         452,           2,             '2007-11-11' union all
select 3,         453,           3,             '2007-12-11' union all
select 4,         454,           1,             '2007-12-12'  
create table People(PeopleID int,Brithday datetime)
insert into People
select 1,             '1978-9-14' union all
select 2,             '1985-3-21' union all
select 3,             '1990-4-22'  
 
goselect  datepart(mm,d.Date) [月份],
[<=18岁] =convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate())<=18 then 1 else 0 end)*100.0/count(p.Brithday)))+'%',  
[19-30岁]=convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate()) between 19 and 30  then 1 else 0 end)*100.0/count(p.Brithday)))+'%',  
[>30岁]  =convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate())>30  then 1 else 0 end)*100.0/count(p.Brithday)))+'%' from People p
left join Detail d on p.PeopleID=d.PeopleID
 group by datepart(mm,d.Date)--结果：
/*
月份   <=18岁    19-30岁 >30岁
-------------------------------
10 0% 100% 0%
11 0% 100% 0%
12 50% 50% 0%
*/
drop table Detail,People
 

Answer 14

SELECT [month]=MONTH([Detail].[Date])
       ,[<18岁]=CAST(SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())<18 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
       ,[18-30岁]=CAST(SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>=18 AND DATEDIFF(YEAR,[People].[Brithday],GETDATE())<=30 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
       ,[30岁以上]=CAST(SUM(WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>30 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
FROM [People]
INNER JOIN [Detail] ON [People].[PeopleID]=[Detail].[PeopleID]
INNER JOIN [House] ON [House].[HouseID]=[Detail].[HouseID]
WHERE [House].[Address]=''--加上你的条件
GROUP BY MONTH([Detail].[Date])

Answer 15

[CODE=SQL
/*不好意思 最后一次修改了*/]SELECT [month]=MONTH([Detail].[Date])
       ,[<18岁]=CAST(SUM(CASE WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())<18 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
       ,[18-30岁]=CAST(SUM(CASE WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>=18 AND DATEDIFF(YEAR,[People].[Brithday],GETDATE())<=30 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
       ,[30岁以上]=CAST(SUM(CASE WHEN DATEDIFF(YEAR,[People].[Brithday],GETDATE())>30 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS VARCHAR(5))+'%'
FROM [People]
INNER JOIN [Detail] ON [People].[PeopleID]=[Detail].[PeopleID]
INNER JOIN [House] ON [House].[HouseID]=[Detail].[HouseID]
WHERE [House].[Address]=''--加上你的条件
GROUP BY MONTH([Detail].[Date])
[/CODE]

Answer 16

--try:select month(Date)[month] ,
'<18岁'=sum(case when datediff(yy,brithday,getdate())<18 then 1 else 0 end),
'19-30岁'=sum(case when datediff(yy,brithday,getdate())<30 and datediff(yy,brithday,getdate())>18  then 1 else 0 end),
'30岁以上'=sum(case when datediff(yy,brithday,getdate())>30 then 1 else 0 end) 
from people a left join Detail b on a.PeopleID=b.PeopleID
left join  house c on c.houseid=b.houseid 
where c.address='沙浦路128弄3号502室'
group by month(Date)

Answer 17

------算百分比：
select month(Date)[month] ,
'<18岁'=cast(cast(sum(case when datediff(yy,brithday,getdate())<18 then 1 else 0 end)*100.0/count(1) as numeric(5,2)) as varchar)+'%',
'19-30岁'=cast(cast(sum(case when datediff(yy,brithday,getdate())<30 and datediff(yy,brithday,getdate())>18  then 1 else 0 end)*100.0/count(1) as numeric(5,2)) as varchar)+'%',
'30岁以上'=cast(cast(sum(case when datediff(yy,brithday,getdate())>30 then 1 else 0 end)*100.0/count(1) as numeric(5,2)) as varchar)+'%' 
from people a left join Detail b on a.PeopleID=b.PeopleID
left join  house c on c.houseid=b.houseid 
where c.address='沙浦路128弄3号502室'
group by month(Date)

Answer 18

如果要把总计写进去是不是就要用union all 再写个啊？？

Answer 19

如果总计写到最下面一行的话就要UNION ALL
如果写到最后一列 就不必要了 

Answer 20

SQL codecreate table Detail(DetailID int,HouseID int,PeopleID int,[Date] datetime)
insert into Detail
select 1,         451,           1,             '2007-10-11' union all
select 2,         452,           2,             '2007-11-11' union all
select 3,         453,           3,             '2007-12-11' union all
select 4,         454,           1,             '2007-12-12'  
create table People(PeopleID int,Brithday datetime)
insert into People
select 1,             '1978-9-14' union all
select 2,             '1985-3-21' union all
select 3,             '1990-4-22'  
 
goselect  datepart(mm,d.Date) [月份],
    [<=18岁] =convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate())<=18 then 1 else 0 end)*100.0/count(p.Brithday)))+'%',  
    [19-30岁]=convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate()) between 19 and 30  then 1 else 0 end)*100.0/count(p.Brithday)))+'%',  
    [>30岁]  =convert(varchar,convert(int,sum(case when datediff(yy,p.Brithday,getdate())>30  then 1 else 0 end)*100.0/count(p.Brithday)))+'%' from People p
left join Detail d on p.PeopleID=d.PeopleID
 group by datepart(mm,d.Date)--结果：
/*
月份   <=18岁    19-30岁 >30岁
-------------------------------
10    0%    100%    0%
11    0%    100%    0%
12    50%    50%    0%
*/
drop table Detail,People用连接,在用日期函数就行了呀