有点难度的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)
) t8,
(
select name,left(convert(varchar(10),date,120),7) date , sum(num) num from tb where MONTH(date) = 7 group by name,left(convert(varchar(10),date,120),7)
) t7
where t8.name = t7.name and left(t8.date,4) = left(t7.date,4) and t8.num > t7.num
