select SaleType,SaleType(SaleType) from test where RequestTime between '2009-1' and '2009-7' group by SaleType
[code=SQL]select saletype,个数=count(1) from test where convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07' group by saletype order by saletypecode]
select RequestTime, A=sum(case when SaleType='a' then 1 else 0 end), B=sum(case when SaleType='b' then 1 else 0 end), C=sum(case when SaleType='c' then 1 else 0 end), D=sum(case when SaleType='d' then 1 else 0 end) FROM TEST WHERE RequestTime '2009-1' BETWEEN AND '2009-7' GROUP BY RequestTime
select saletype,个数=count(1) from test where convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07' group by saletype order by saletype
不行....我要 显示字段为 : 日期 ,A状态个数,B状态个数,C状态个数
select RequestTime=convert(varchar(7),RequestTime,120), A=sum(case when SaleType='a' then 1 else 0 end), B=sum(case when SaleType='b' then 1 else 0 end), C=sum(case when SaleType='c' then 1 else 0 end), D=sum(case when SaleType='d' then 1 else 0 end) FROM TEST WHERE RequestTime '2009-1' BETWEEN AND '2009-7' GROUP BY convert(varchar(7),RequestTime,120),
如果不是1楼的情况 就用 select RequestTime, sum(case SaleType when 'a' then 1 else 0 end) as a, sum(case SaleType when 'b' then 1 else 0 end) as b, sum(case SaleType when 'c' then 1 else 0 end) as c, sum(case SaleType when 'd' then 1 else 0 end) as d FROM TEST WHERE RequestTime '2009-1' BETWEEN AND '2009-7' GROUP BY RequestTime
select RequestTime, sum(case SaleType when 'a' then 1 else 0 end) as a, sum(case SaleType when 'b' then 1 else 0 end) as b, sum(case SaleType when 'c' then 1 else 0 end) as c, sum(case SaleType when 'd' then 1 else 0 end) as d FROM TEST WHERE RequestTime BETWEEN '2009-1' AND '2009-7' GROUP BY RequestTime
SELECT convert(varchar(7),RequestTime,120) 日期, A状态个数=sum(case when SaleType='A' then 1 else 0 end), B状态个数=sum(case when SaleType='B' then 1 else 0 end), C状态个数=sum(case when SaleType='C' then 1 else 0 end), D状态个数=sum(case when SaleType='D' then 1 else 0 end) FROM TEST WHERE RequestTime >='2009-1-1' AND RequestTime <'2009-8-1' GROUP BY convert(varchar(7),RequestTime,120)
select RequestTime, sum(case SaleType when 'a' then 1 else 0 end) as a, sum(case SaleType when 'b' then 1 else 0 end) as b, sum(case SaleType when 'c' then 1 else 0 end) as c, sum(case SaleType when 'd' then 1 else 0 end) as d FROM TEST convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07' GROUP BY RequestTime
select RequestTime, A = sum(case when RequestMethod ='A' then 1 else 0 end), B =sum(case when RequestMethod ='B' then 1 else 0 end), C =sum(case when RequestMethod ='C' then 1 else 0 end), D =sum(case when RequestMethod ='D' then 1 else 0 end) FROM OrderInfo WHERE left(RequestTime,7) BETWEEN '2009-1' AND '2009-7' GROUP BY RequestTime错误 :Unknown column 'A' in 'field list' (0.453 sec)
select RequestTime, sum(case when RequestMethod ='A' then 1 else 0 end), sum(case when RequestMethod ='B' then 1 else 0 end), sum(case when RequestMethod ='C' then 1 else 0 end), sum(case when RequestMethod ='D' then 1 else 0 end) FROM OrderInfo WHERE left(RequestTime,7) BETWEEN '2009-1' AND '2009-7' GROUP BY RequestTime没有数据...
select RequestTime=convert(varchar(7),RequestTime,120), A=sum(case when SaleType='a' then 1 else 0 end), B=sum(case when SaleType='b' then 1 else 0 end), C=sum(case when SaleType='c' then 1 else 0 end), D=sum(case when SaleType='d' then 1 else 0 end) FROM TEST WHERE convert(varchar(7),RequestTime,120) '2009-01' BETWEEN AND '2009-07' GROUP BY convert(varchar(7),RequestTime,120),
select convert(varchar(7),RequestTime,120) as RequestTime, sum(case SaleType when 'a' then 1 else 0 end) as a, sum(case SaleType when 'b' then 1 else 0 end) as b, sum(case SaleType when 'c' then 1 else 0 end) as c, sum(case SaleType when 'd' then 1 else 0 end) as d FROM TEST convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07' GROUP BY convert(varchar(7),RequestTime,120)
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(RequestTime DATETIME,SaleType char(1)) go insert into tb select '2009-2-12','a' union all select '2009-3-12','b' union all select '2009-4-12','c' union all select '2009-1-12','a' union all select '2009-2-12','a' union all select '2009-2-12','a' union all select '2009-4-12','c' union all select '2009-8-12','a' union all select '2009-2-12','d' go select RequestTime=convert(varchar(7),RequestTime,120), A=sum(case when SaleType='a' then 1 else 0 end), B=sum(case when SaleType='b' then 1 else 0 end), C=sum(case when SaleType='c' then 1 else 0 end), D=sum(case when SaleType='d' then 1 else 0 end) FROM tb WHERE RequestTime BETWEEN '2009-1-01' AND '2009-7-30' GROUP BY convert(varchar(7),RequestTime,120) /*------------ RequestTime A B C D ----------- ----------- ----------- ----------- ----------- 2009-01 1 0 0 0 2009-02 3 0 0 1 2009-03 0 1 0 0 2009-04 0 0 2 0(4 行受影响) -------*/
where RequestTime between '2009-1' and '2009-7'
group by SaleType
where convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07'
group by saletype
order by saletypecode]
A=sum(case when SaleType='a' then 1 else 0 end),
B=sum(case when SaleType='b' then 1 else 0 end),
C=sum(case when SaleType='c' then 1 else 0 end),
D=sum(case when SaleType='d' then 1 else 0 end)
FROM TEST
WHERE RequestTime '2009-1' BETWEEN AND '2009-7'
GROUP BY RequestTime
where convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07'
group by saletype
order by saletype
不行....我要 显示字段为 : 日期 ,A状态个数,B状态个数,C状态个数
A=sum(case when SaleType='a' then 1 else 0 end),
B=sum(case when SaleType='b' then 1 else 0 end),
C=sum(case when SaleType='c' then 1 else 0 end),
D=sum(case when SaleType='d' then 1 else 0 end)
FROM TEST
WHERE RequestTime '2009-1' BETWEEN AND '2009-7'
GROUP BY convert(varchar(7),RequestTime,120),
select RequestTime,
sum(case SaleType when 'a' then 1 else 0 end) as a,
sum(case SaleType when 'b' then 1 else 0 end) as b,
sum(case SaleType when 'c' then 1 else 0 end) as c,
sum(case SaleType when 'd' then 1 else 0 end) as d
FROM TEST
WHERE RequestTime '2009-1' BETWEEN AND '2009-7'
GROUP BY RequestTime
sum(case SaleType when 'a' then 1 else 0 end) as a,
sum(case SaleType when 'b' then 1 else 0 end) as b,
sum(case SaleType when 'c' then 1 else 0 end) as c,
sum(case SaleType when 'd' then 1 else 0 end) as d
FROM TEST
WHERE RequestTime BETWEEN '2009-1' AND '2009-7'
GROUP BY RequestTime
A状态个数=sum(case when SaleType='A' then 1 else 0 end),
B状态个数=sum(case when SaleType='B' then 1 else 0 end),
C状态个数=sum(case when SaleType='C' then 1 else 0 end),
D状态个数=sum(case when SaleType='D' then 1 else 0 end)
FROM TEST
WHERE RequestTime >='2009-1-1' AND RequestTime <'2009-8-1'
GROUP BY convert(varchar(7),RequestTime,120)
sum(case SaleType when 'a' then 1 else 0 end) as a,
sum(case SaleType when 'b' then 1 else 0 end) as b,
sum(case SaleType when 'c' then 1 else 0 end) as c,
sum(case SaleType when 'd' then 1 else 0 end) as d
FROM TEST
convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07'
GROUP BY RequestTime
A = sum(case when RequestMethod ='A' then 1 else 0 end),
B =sum(case when RequestMethod ='B' then 1 else 0 end),
C =sum(case when RequestMethod ='C' then 1 else 0 end),
D =sum(case when RequestMethod ='D' then 1 else 0 end)
FROM OrderInfo
WHERE left(RequestTime,7) BETWEEN '2009-1' AND '2009-7'
GROUP BY RequestTime错误 :Unknown column 'A' in 'field list'
(0.453 sec)
select RequestTime,
sum(case when RequestMethod ='A' then 1 else 0 end),
sum(case when RequestMethod ='B' then 1 else 0 end),
sum(case when RequestMethod ='C' then 1 else 0 end),
sum(case when RequestMethod ='D' then 1 else 0 end)
FROM OrderInfo
WHERE left(RequestTime,7) BETWEEN '2009-1' AND '2009-7'
GROUP BY RequestTime没有数据...
select RequestTime=convert(varchar(7),RequestTime,120),
A=sum(case when SaleType='a' then 1 else 0 end),
B=sum(case when SaleType='b' then 1 else 0 end),
C=sum(case when SaleType='c' then 1 else 0 end),
D=sum(case when SaleType='d' then 1 else 0 end)
FROM TEST
WHERE convert(varchar(7),RequestTime,120) '2009-01' BETWEEN AND '2009-07'
GROUP BY convert(varchar(7),RequestTime,120),
sum(case SaleType when 'a' then 1 else 0 end) as a,
sum(case SaleType when 'b' then 1 else 0 end) as b,
sum(case SaleType when 'c' then 1 else 0 end) as c,
sum(case SaleType when 'd' then 1 else 0 end) as d
FROM TEST
convert(varchar(7),RequestTime,120) between '2009-01' and '2009-07'
GROUP BY convert(varchar(7),RequestTime,120)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(RequestTime DATETIME,SaleType char(1))
go
insert into tb
select '2009-2-12','a' union all
select '2009-3-12','b' union all
select '2009-4-12','c' union all
select '2009-1-12','a' union all
select '2009-2-12','a' union all
select '2009-2-12','a' union all
select '2009-4-12','c' union all
select '2009-8-12','a' union all
select '2009-2-12','d'
go
select RequestTime=convert(varchar(7),RequestTime,120),
A=sum(case when SaleType='a' then 1 else 0 end),
B=sum(case when SaleType='b' then 1 else 0 end),
C=sum(case when SaleType='c' then 1 else 0 end),
D=sum(case when SaleType='d' then 1 else 0 end)
FROM tb
WHERE RequestTime BETWEEN '2009-1-01' AND '2009-7-30'
GROUP BY convert(varchar(7),RequestTime,120)
/*------------
RequestTime A B C D
----------- ----------- ----------- ----------- -----------
2009-01 1 0 0 0
2009-02 3 0 0 1
2009-03 0 1 0 0
2009-04 0 0 2 0(4 行受影响)
-------*/