请用SQL语句实现:从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意:TestDB中有很多科目,都有1-12月份的发生额。
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
数据库名:JcyAudit,数据集:Select * from TestDB
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
数据库名:JcyAudit,数据集:Select * from TestDB
select AccID,Occmonth,DebitOccur from TestDB where addid=101
),c2 as(
select * from testdb a where accid<>101 and exists(select 1 from c1 where Occmonth=a.Occmonth and DebitOccur<a.DebitOccur)
)select accid from c2 group by accid having count(*)=12
use JcyAuditdeclare @TestDB table (AccID int,Occmonth int,DebitOccur int)
insert into @TestDB
select 101,1,13 union all
select 101,2,15 union all
select 101,3,17 union all
select 101,4,12 union all
select 101,5,14 union all
select 101,6,15 union all
select 101,7,18 union all
select 101,8,23 union all
select 101,9,12 union all
select 101,10,12 union all
select 101,11,11 union all
select 101,12,13 union all
select 102,1,13 union all
select 102,2,15 union all
select 102,3,25 union all
select 102,4,12 union all
select 102,5,16 union all
select 102,6,21 union all
select 102,7,21 union all
select 102,8,21 union all
select 102,9,21 union all
select 102,10,21 union all
select 102,11,21 union all
select 102,12,21 union all
select 103,1,25 union all
select 103,2,28 union all
select 103,3,29 union all
select 103,4,30 union all
select 103,5,39 union all
select 103,6,26 union all
select 103,7,27 union all
select 103,8,29 union all
select 103,9,30 union all
select 103,10,31 union all
select 103,11,34 union all
select 103,12,35 union all
select 104,1,65 union all
select 104,2,67 union all
select 104,3,34 union all
select 104,4,45 union all
select 104,5,78 union all
select 104,6,89 union all
select 104,7,66 union all
select 104,8,46 union all
select 104,9,45 union all
select 104,10,34 union all
select 104,11,89 union all
select 104,12,45select AccID from @TestDB
where AccID<>101 group by AccID
having min(DebitOccur)>=(select max(DebitOccur) from @TestDB where AccID=101)/*
AccID
-----------
103
104
*/
select accid from(
select a.accid from testDB a inner join (
select AccID,Occmonth,DebitOccur from TestDB where addid=101
)b on a.occmonth=b.occmonth and a.debitoccur>b.debitoccur where a.accid<>101
)t group by accid having count(*)=12
select AccID from TestDB where AccID<>101 group by AccID
having min(DebitOccur)>(select max(DebitOccur) from TestDB where AccID=101)--如果科目类型是varchar的话
select AccID from TestDB where AccID<>'101' group by AccID
having min(DebitOccur)>(select max(DebitOccur) from TestDB where AccID='101')
if (not object_id('tempdb..#temp') is null)
drop table #temp
Select * into #temp from TestDB a
where not exists(select 1 from TestDB b where b.Occmonth = a.Occmonth
and b.DebitOccur >= a.DebitOccur and b.AccID = '101') select AccID,count(*) from #temp
group by AccID
having count(*) = 12
晴天和abcjun188的应该是对的,我就不写了。
Select * into #temp from TestDB a 这句话的结构没了解什么什么含义,没接触过! 麻烦解释一下了 谢谢!!!