表
id Name addTime
1 A123 2009-1-1
2 AZ456 2009-1-2
3 A123 2009-4-1
4 A123 2009-5-1
5 BF444 2009-2-1
6 BY123 2009-1-1
7 Ct123 2009-4-1
8 Ct123 2009-3-1
........想得到某一时间段(例如2009-1-1到2009-4-1)
Name 种类 数量
A 2 3
B 2 2
C 1 2
请指教!!
id Name addTime
1 A123 2009-1-1
2 AZ456 2009-1-2
3 A123 2009-4-1
4 A123 2009-5-1
5 BF444 2009-2-1
6 BY123 2009-1-1
7 Ct123 2009-4-1
8 Ct123 2009-3-1
........想得到某一时间段(例如2009-1-1到2009-4-1)
Name 种类 数量
A 2 3
B 2 2
C 1 2
请指教!!
SELECT
LEFT(LTRIM(NAME),1)AS NAME,
COUNT(DISTINCT NAME)AS 种类,
COUNT(NAME)AS 数量
FROM
TB
WHERE ADDTIME BETWEEN '2009-1-1' AND '2009-4-4-1' GROUP BY NAME
select
substring(Name,1,1) as Name ,
count(distinct Name) as 种类,
count(id) as 数量
from tablename
where addTime between '2009-1-1' and '2009-4-1'
group by substring(Name,1,1)没有环境不能测试,你试试这样行不?
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,Name varchar(5),addTime datetime)
insert into #T
select 1,'A123','2009-1-1' union all
select 2,'AZ456','2009-1-2' union all
select 3,'A123','2009-4-1' union all
select 4,'A123','2009-5-1' union all
select 5,'BF444','2009-2-1' union all
select 6,'BY123','2009-1-1' union all
select 7,'Ct123','2009-4-1' union all
select 8,'Ct123','2009-3-1'select left(name,1) as name,count(distinct name) as 种类
,count(*) as 数量 from #T
where addTime between '2009-1-1' and '2009-4-1'
group by left(name,1)/*
name 种类 数量
---- ----------- -----------
A 2 3
B 2 2
C 1 2
*/
INSERT TBTEST
SELECT 1, 'A123' , '2009-1-1' UNION ALL
SELECT 2, 'AZ456', '2009-1-2' UNION ALL
SELECT 3, 'A123' , '2009-4-1' UNION ALL
SELECT 4, 'A123' , '2009-5-1' UNION ALL
SELECT 5, 'BF444', '2009-2-1' UNION ALL
SELECT 6, 'BY123', '2009-1-1' UNION ALL
SELECT 7, 'Ct123', '2009-4-1' UNION ALL
SELECT 8, 'Ct123', '2009-3-1' SELECT * FROM TBTEST
SELECT NAME,SUM(种类)种类,SUM(数量)数量 FROM (SELECT
LEFT(LTRIM(NAME),1)AS NAME,
COUNT(DISTINCT NAME)AS 种类,
COUNT(NAME)AS 数量
FROM
TBTEST
WHERE ADDTIME BETWEEN '2009-1-1' AND '2009-4-1' GROUP BY NAME)AS T
GROUP BY NAME
NAME 种类 数量
---- ----------- -----------
A 2 3
B 2 2
C 1 2(所影响的行数为 3 行)
这样就有多个A,B,C!!
希望A,B,C唯一!!
SELECT
LEFT(LTRIM(NAME),1)AS NAME,
COUNT(DISTINCT NAME)AS 种类,
COUNT(NAME)AS 数量
FROM
TB
WHERE ADDTIME BETWEEN '2009-1-1' AND '2009-4-4-1' GROUP BY LEFT(LTRIM(NAME),1)
这样更好!!!
-- Author:happyflystone
-- Version:V1.001
-- Date:2009-05-16 17:18:32
-------------------------------------- Test Data: t1
If object_id('t1') is not null
Drop table t1
Go
Create table t1(id int,Name nvarchar(15),addTime datetime)
Go
Insert into t1
select 1,'A123','2009-1-1' union all
select 2,'AZ456','2009-1-2' union all
select 3,'A123','2009-04-01 12:12:00' union all
select 4,'A123','2009-5-1' union all
select 5,'BF444','2009-2-1' union all
select 6,'BY123','2009-1-1' union all
select 7,'Ct123','2009-4-1' union all
select 8,'Ct123','2009-3-1'
Go
--Start
SELECT
LEFT( NAME ,1) AS [NAME],
COUNT(DISTINCT NAME) AS 种类,
COUNT(NAME) AS 数量
FROM
T1
WHERE ADDTIME BETWEEN '2009-1-1' AND '2009-4-1 23:59:59'
GROUP BY LEFT( NAME ,1)
--Result:
/*
NAME 种类 数量
---- ----------- -----------
A 2 3
B 2 2
C 1 2(所影响的行数为 3 行)
*/
--End
这个name不是固定值,所以给出的代码不行哦!!
substring(Name,1,1) as Name ,
count(distinct Name) as 种类,
count(id) as 数量
from tablename
where addTime between '2009-1-1' and '2009-4-1'
group by substring(Name,1,1)