请问这样的查询如何实现？

[peng1014]

表中字段如下：
ID EmployeeName ProductName makeNum makeTime
1 李明 18寸卡迪亚 2 2004-12-06
2 刘强 10寸罗迪克 1 2004-12-12
3 刘晓晖 18寸卡迪亚 1 2004-12-10
4 李明 5寸罗曼史 2 2004-12-12
15 李明 12寸复古 1 2004-12-13
16 刘晓晖 8寸玫瑰 2 2004-11-13
17 张晓 7寸梦幻水晶 1 2004-11-24
24 刘强 15寸复古 2 2004-12-25
32 王富 7寸玫瑰 2 2004-12-26
要求作如下统计：(按日期2004-12-10至2004-12-30）
姓名 12-18寸 7-10寸 5寸
李明 3 0 2
刘强 2 1 0
王富 0 1 0
请问这样的查询如何实现？先行谢过

[lemon_lyk]

declare @t table(
EmployeeName varchar(10),
ProductName char(2),
makeNum int
)
insert into @t
select EmployeeName,substring(ProductName,1,2),makeNum
from 表名
where ASCII(substring(ProductName,2,1))<=57--9的ASCII码是57
insert into @t
select EmployeeName,substring(ProductName,1,1),makeNum
from 表名
where ASCII(substring(ProductName,2,1))>57
Select EmployeeName as 姓名,[12-18寸]=sum(case when (cast(ProductName as int)>=12 and cast(ProductName as int)<=18) then 1*makenum else 0 end),
[7-10寸]=sum(case when (cast(ProductName as int)>=7 and cast(ProductName as int)<=10) then 1*makenum else 0 end),
[5寸]=sum(case when cast(ProductName as int)<=5 then 1 else 0 end)
from @t
group by EmployeeName

[lemon_lyk]

declare @t table(
EmployeeName varchar(10),
ProductName char(2),
makeNum int
)
insert into @t
select EmployeeName,substring(ProductName,1,2),makeNum
from 表名
where ASCII(substring(ProductName,2,1))<=57--9的ASCII码是57
insert into @t
select EmployeeName,substring(ProductName,1,1),makeNum
from 表名
where ASCII(substring(ProductName,2,1))>57
Select EmployeeName as 姓名,[12-18寸]=sum(case when (cast(ProductName as int)>=12 and cast(ProductName as int)<=18) then 1*makenum else 0 end),
[7-10寸]=sum(case when (cast(ProductName as int)>=7 and cast(ProductName as int)<=10) then 1*makenum else 0 end),
[5寸]=sum(case when cast(ProductName as int)<=5 then 1 else 0 end)
from @t
group by EmployeeName

[wzh1215]

select EmployeeName,'12-18寸'=sum(case when left(ProductName,charindex('寸',ProductName)-1)>='12' and left(ProductName,charindex('寸',ProductName)-1)<='18' then 1 else 0 end)
,'7-10寸'=sum(case when left(ProductName,charindex('寸',ProductName)-1)>='7' and left(ProductName,charindex('寸',ProductName)-1)<='10' then 1 else 0 end)
,'5寸'=sum(case when left(ProductName,charindex('寸',ProductName)-1)='5' then 1 else 0 end)
from 表 where makeTime between '2004-12-10' and '2004-12-30'
group by EmployeeName

[jinjazz]

把productname和尺寸分开，用case when 就可以了

[daijingjie2002]

首先你的数据结构有问题，如果ProductName全都8,10,7等还有可能实现

[peng1014]

一定要把productname和尺寸分开吗，别人以前设计的表，改结构麻烦大了

[peng1014]

可不可以用like 方法实现呀，请高手指点

[xluzhong]

建议把ProductName的数字改为：如 5寸罗曼史--〉05寸罗曼史
之后,给你个思想，自己处理ProductName中的数字

Select EmployeeName as 姓名,[12-18寸]=sum(case when (cast(left(ProductName,2) as int)>=12 and cast(left(ProductName,2) as int)>=<=18) then 1*makenum else 0 end),
[7-10寸]=sum(case when (cast(left(ProductName,2) as int)>=>=7 and cast(left(ProductName,2) as int)>=<=10) then 1*makenum else 0 end),
[5寸]=sum(case when cast(left(ProductName,2) as int)>==5 then 1 else 0 end)
from table
group by EmployeeName

[peng1014]

谢谢楼上各位热心的大哥大姐