我有一个表,有字段 A 和 B 两个:
table T
(
A varchar(32),
B datatime
)我想通过这个表来创建一个新的表,实现指定某个某个时间之前的A的不同的值的出现次数的统计,
新的表如下
Table newtable
(
A varchar(32),
B_new datetime, /*这里的B_new就是上面提到的指定的那个时间*/
C numeric(10,0) /不同的A的值出现的次数*/
)我这里是想创建一个临时表来存储结果
table T
(
A varchar(32),
B datatime
)我想通过这个表来创建一个新的表,实现指定某个某个时间之前的A的不同的值的出现次数的统计,
新的表如下
Table newtable
(
A varchar(32),
B_new datetime, /*这里的B_new就是上面提到的指定的那个时间*/
C numeric(10,0) /不同的A的值出现的次数*/
)我这里是想创建一个临时表来存储结果
A,
B,
count(1) as C
INTO
newtable
FROM
T
GROUP BY
A,
B
ELECT A,B,COUNT(*)AS 个数
FROM TB
WHERE B
BETWEEN ..AND ...
GROUP BY A,B
SELECT A,B,COUNT(*)AS 个数
FROM TB
WHERE B
BETWEEN ..AND ...
GROUP BY A,B
A,
B,
c=cast(count(distinct a) as decimal(14,2)
INTO
newtable
FROM
T
GROUP BY
A,
B楼主试下
没那个NEWTABLE???/SELECT A,B,COUNT(*)AS 个数
INTO NEWTABLE
FROM TB
WHERE B
BETWEEN ..AND ...
GROUP BY A,B
那只能这样了
set @t=''
select A,B_new=@t,C=count(c) from T group by A where B<=@t
A,
B,
c=cast(count(distinct a) as decimal(14,2)
INTO
#a
FROM T where b>=开始日期 and b<=结束日期 --或者用b between 开始日期 and b<=结束日期
GROUP BY A,Binsert newtable select * from #a
A,B,count(1) as C
INTO
newtable
FROM
TB
WHERE
时间 between ... and ...
GROUP BY
A,B
A B
巧克力 2009-01-05 00:00:00.000
巧克力 2009-02-05 00:00:00.000
巧克力 2009-01-15 00:00:00.000
杯面 2009-01-25 00:00:00.000
杯面 2009-02-25 00:00:00.000
杯面 2009-02-20 00:00:00.000
苹果 2009-01-11 00:00:00.000
苹果 2009-01-12 00:00:00.000
苹果 2009-01-16 00:00:00.000我想查询 2月前的各种东西的数量,预期效果是
A B C
巧克力 2009年1月 2
杯面 2009年1月 1
苹果 2009年1月 3
FROM TB GROUP BY A,CONVERT(VARCHAR(7),B,120)
select
A,
B=rtrim(DATEPART(YEAR,B))+'年'+rtrim(DATEPART(MONTH,B))+'月'
,c=COUNT(*)
from tb1
where DATEDIFF(MONTH,B,'2009-02-01')>0
group by A,rtrim(DATEPART(YEAR,B))+'年'+rtrim(DATEPART(MONTH,B))+'月'
A,
CONVERT(VARCHAR(7),B,120)AS B,
COUNT(*)NUM
INTO NEWTABLEFROM
TB GROUP BY A,CONVERT(VARCHAR(7),B,120)
要加条件就加个WHERE
--如果是单纯今年年的话
select
A,
B=rtrim(DATEPART(YEAR,B))+'年'+rtrim(DATEPART(MONTH,B))+'月'
,c=COUNT(*)
from tb1
where DATEPART(month,B)<2
group by A,rtrim(DATEPART(YEAR,B))+'年'+rtrim(DATEPART(MONTH,B))+'月'
create table T
(
A varchar(32),
B datetime
)
insert t
select 'AA','2009-08-13'
union all select 'AA','2009-08-13'
union all select 'BB','2009-08-13'
union all select 'CC','2009-08-13'
union all select 'AA','2009-08-13'
union all select 'dd','2009-08-13'
SELECT
A,
B,
c=cast(count(1) as decimal(14,2))
INTO #a
FROM T where b>='2009-08-13' and b<='2009-08-13' --或者用b between 开始日期 and b<=结束日期
GROUP BY A,B
create Table newtable
(
A varchar(32),
B_new datetime, /*这里的B_new就是上面提到的指定的那个时间*/
C numeric(10,0) -- /不同的A的值出现的次数*/
) insert newtable select * from #aselect * from newtable
/*
A B_new C
AA 2009-08-13 00:00:00.000 3
BB 2009-08-13 00:00:00.000 1
CC 2009-08-13 00:00:00.000 1
dd 2009-08-13 00:00:00.000 1
*/