id time state name
==================================================================
1 2009-9-23 11:39:03 1 One
2 2009-9-23 9:39:03 2 Two
3 2009-9-23 12:39:03 3 One
10 2009-9-23 14:06:59 1 One
11 2009-9-23 14:06:59 2 Three 数据里面是没有four,five,six,seven的,但也要统计.因为以后可能会有.
我统计的只能得到One,two,three的.
然后数据排序
请问怎么得到结果是:Name total
================
One 3
two 1
three 1
four 0
five 0
six 0
seven 0
==================================================================
1 2009-9-23 11:39:03 1 One
2 2009-9-23 9:39:03 2 Two
3 2009-9-23 12:39:03 3 One
10 2009-9-23 14:06:59 1 One
11 2009-9-23 14:06:59 2 Three 数据里面是没有four,five,six,seven的,但也要统计.因为以后可能会有.
我统计的只能得到One,two,three的.
然后数据排序
请问怎么得到结果是:Name total
================
One 3
two 1
three 1
four 0
five 0
six 0
seven 0
然后连接查询
select b.name,total=count(*)
from tb a
right join
(select one as name union two union three union four union five union six union seven) b
on a.name=b.name
from tb a
right join
(select 'one' as name union 'two' union 'three' union 'four' union 'five' union 'six' union 'seven') b
on a.name=b.name
GROUP BY B.NAME
from tb a
right join
(select 'one' as name union 'two' union 'three' union 'four' union 'five' union 'six' union 'seven') b
on a.name=b.name
GROUP BY B.NAME
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 14:40:00
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[time] datetime,[state] int,[name] varchar(5))
insert [tb]
select 1,'2009-9-23 11:39:03',1,'One' union all
select 2,'2009-9-23 9:39:03',2,'Two' union all
select 3,'2009-9-23 12:39:03',3,'One' union all
select 10,'2009-9-23 14:06:59',1,'One' union all
select 11,'2009-9-23 14:06:59',2,'Three'
--------------开始查询--------------------------select name,count(1) as total from [tb] group by name
union all
select name='four',total=0
union all
select name='five',total=0
union all
select name='six',total=0
union all
select name='seven',total=0
----------------结果----------------------------
/* name total
----- -----------
One 3
Three 1
Two 1
four 0
five 0
six 0
seven 0(7 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](id int, time datetime,state int, name varchar(10))
insert [tb]
select
1, '2009-9-23 11:39:03' ,1, 'One' union all select
2, '2009-9-23 9:39:03' ,2 , 'Two' union all select
3, '2009-9-23 12:39:03' ,3 , 'One' union all select
10, '2009-9-23 14:06:59' ,1 , 'One' union all select
11, '2009-9-23 14:06:59', 2 , 'Three'
select b.name,total=sum(case when a.name is null then 0 else 1 end )
from tb a
right join
(select 'one' as name union
select 'two' union
select 'three' union
select 'four' union
select 'five' union
select 'six' union
select 'seven') b
on a.name=b.name
group by b.name
name total
----- -----------
five 0
four 0
one 3
seven 0
six 0
three 1
two 1(所影响的行数为 7 行)
(select name,0 from (select 'One' name union select 'Two' union select 'Three' union select 'Four' union select 'Five' union select 'Six' union select 'Seven' ) a
union all
select name,1 from tb) b
group by name
order by name
-->Author:happy_stone【不能飛的石頭】
-->Date :2009-10-16 14:39:35
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([id] int,[time] Datetime,[state] int,[name] nvarchar(5))
Insert #t
select 1,'2009-9-23 11:39:03',1,N'One' union all
select 2,'2009-9-23 9:39:03',2,N'Two' union all
select 3,'2009-9-23 12:39:03',3,N'One' union all
select 10,'2009-9-23 14:06:59',1,N'One' union all
select 11,'2009-9-23 14:06:59',2,N'Three'
Go
Select a.[name],sum(case when id is not null then 1 else 0 end ) from (select 'one' [name]
union all select 'Two'
union all select 'Three'
union all select 'four'
union all select 'five'
union all select 'six'
union all select 'seven')a
left join #t b on a.[name]=b.[name]
group by a.[name]
/*
name
----- -----------
five 0
four 0
one 3
seven 0
six 0
Three 1
Two 1
*/
insert @tb
select 1,'2009-9-23 11:39:03',1,'One' union all
select 2,'2009-9-23 9:39:03',2,'Two' union all
select 3,'2009-9-23 12:39:03',3,'One' union all
select 10,'2009-9-23 14:06:59',1,'One' union all
select 11,'2009-9-23 14:06:59',2,'Three'
select name,sum([count]) [count] from
(select name,0 [count] from (select 'One' name union select 'Two' union select 'Three' union select 'Four' union select 'Five' union select 'Six' union select 'Seven' ) a
union all
select name,1 from @tb) b
group by name
--order by name/*name count
----- -----------
Five 0
Four 0
One 3
Seven 0
Six 0
Three 1
Two 1(所影响的行数为 7 行)
*/
insert into tb values(1 , '2009-9-23 11:39:03', 1 , 'One')
insert into tb values(2 , '2009-9-23 9:39:03 ', 2 , 'Two')
insert into tb values(3 , '2009-9-23 12:39:03', 3 , 'One')
insert into tb values(10, '2009-9-23 14:06:59', 1 , 'One')
insert into tb values(11, '2009-9-23 14:06:59', 2 , 'Three')
goselect m.name ,isnull(n.total,0) total from
(
select 'One' name union all
select 'Two' name union all
select 'Three' name union all
select 'four' name union all
select 'five' name union all
select 'six' name union all
select 'seven' name
) m left join
(
select name , count(1) total from tb group by name
) n
on m.name = n.name
drop table tb /*
name total
----- -----------
One 3
Two 1
Three 1
four 0
five 0
six 0
seven 0(所影响的行数为 7 行)
*/