有点难度的sql语句！
一个表table三个字段,一个名字字段   name,一个接的定单数   num     一个时间字段:data
写条SQL语句,查找8月份定单数比7月份多的名字。
各位大虾这题目该杂整？


------解决方案--------------------
--try

select * from
(
select name,
[7m]=sum(case when convert(char(7),data,120)= '2007-07 ' then 1 else 0 end),
[8m]=sum(case when convert(char(7),data,120)= '2007-08 ' then 1 else 0 end)
from T
group by name
)tmp where [8m]> [7m]
------解决方案--------------------
--获得7月份数据

SELECT name,sum(num) AS num
INTO #TmpJul
FROM table
Group By name
WHERE date> = '2006-7-1 ' AND date <= '2006-7-31 '

--获得8月份数据

SELECT name,sum(num) AS num
INTO #TmpAug
FROM table
Group By name
WHERE date> = '2006-8-1 ' AND date <= '2006-8-30 '


--获得应得数据

SELECT name
FROM #TmpJul t1
INNER JOIN #TmpAug t2
ON t1.name=t2.name
WHERE t1.num <t2.num

DROP TABLE #TmpJul

DROP TABLE #TmpAug

------解决方案--------------------
一法
select b.name,sum(b.num)-sum(a.num) from #table a,#table b where a.name=b.name
and datediff(mm,a.date, '2007-01-01 ')=0 and datediff(mm,b.date, '2007-02-01 ')=0
group by b.name,convert(varchar(7),a.date,120) having sum(a.num) <sum(b.num)
二法--注释的都有打开用
select name--,sum(case when datediff(mm,a.date, '2007-01-01 ')=0 then num else 0 end) [1月],
--sum(case when datediff(mm,a.date, '2007-02-01 ')=0 then num else 0 end) [2月],
--sum(case when datediff(mm,a.date, '2007-02-01 ')=0 then num else 0 end)-
--sum(case when datediff(mm,a.date, '2007-01-01 ')=0 then num else 0 end) 差值
from #table a group by name having
sum(case when datediff(mm,a.date, '2007-02-01 ')=0 then num else 0 end)>
sum(case when datediff(mm,a.date, '2007-01-01 ')=0 then num else 0 end)
--order by 差值
---测试数据
create table #table([name] varchar(10),num int,[date] datetime)
insert #table
select 'a ',2, '2007-01-02 '
union select 'b ',3, '2007-01-03 '
union select 'a ',30, '2007-01-03 '
union select 'a ',3, '2007-02-04 '
union select 'b ',10, '2007-02-05 '
------解决方案--------------------
select name from [table] a group by name having
sum(case when datediff(mm,a.date, '2007-02-01 ')=0 then num else 0 end)>
sum(case when datediff(mm,a.date, '2007-01-01 ')=0 then num else 0 end)

------解决方案--------------------
select name
from table
where datepart(mm,date)=8 and num> (select num from table where datepart(mm,date)=7)
------解决方案--------------------
更正：
select name
from table
where datepart(mm,date)=8 and num> (select max(num) from table where datepart(mm,date)=7)
------解决方案--------------------
一个表table三个字段,一个名字字段 name,一个接的定单数 num 一个时间字段:data
写条SQL语句,查找8月份定单数比7月份多的名字。
各位大虾这题目该杂整？

select name ,t8.date date8,t8.num num8,t7.date date7,t7.num num7 from
(
select name,left(convert(varchar(10),date,120),7) date , sum(num) num from tb where MONTH(date) = 8 group by name,left(convert(varchar(10),date,120),7)