select a.* from a,b
where a.id=b.id and a.date>b.date;
where a.id=b.id and a.date>b.date;
解决方案 »
- 游标内的判断,晕了!求救!代码如下
- 备份别人机子上的数据库问题
- SQL SERVER2000数据库不能启动了,怎么把数据库备份下来,然后恢复回来?谢谢!
- connection.commit 方法执行成功,但数据库中事务未提交。
- 连服务器读数据,服务器内存泄露???高手帮忙
- 如何使用delphi6.0自带的数据库通过sql2000的导入/导出向导,导入sql2000中
- 数据库超难问题
- 请教sql语句.
- update问题
- 这个问题如何解决呀,急!!
- 如何进行特定表的备份?
- 这句错在哪里update (select top 2 * from HPTraceCode where planid=15 and state='2' order by bodyCode DESC) set state='1'
(select A.ID,count(A.Date) as 数量
from A
group by A.ID
) TmpA,
(select B.ID,count(B.Date) as 数量
from B
group by B.ID
) TmpB
where TmpA.id=TmpB.id and TmpA.数量<>TmpB.数量
select tmpA * from
(select A.ID,count(A.Date) as 数量
from A
group by A.ID
) TmpA,
(select B.ID,count(B.Date) as 数量
from B
group by B.ID
) TmpB
where TmpA.id=TmpB.id and TmpA.数量<>TmpB.数量
那数量不同的我是想取A中时间最大的啊!!
(select A.ID,count(A.Date) as 数量,max(A.date) as 最大时间
from A
group by A.ID
) TmpA,
(select B.ID,count(B.Date) as 数量
from B
group by B.ID
) TmpB
where TmpA.id=TmpB.id and TmpA.数量<>TmpB.数量
and (a.date>b.date and a.id=b.id)
ID date
1 2005-1-1
1 2005-2-1
2 2005-2-2
3 2005-2-3
b 表有
ID date
1 2005-1-20
2 2005-2-3
那么 我想取出得结果就是
ID date
1 2005-2-1 (a表与b表钟1 得数量不同 取a中date最大得)
3 2005-2-3 (a表中有b表没有 取;2 在a中和b中都有 数量相同 不取)
这回明白么?? 有人能会写么??
id int ,
date varchar(32)
)insert into a
select 1,'2005-1-1'
union all
select 1, '2005-2-1'
union all
select 2, '2005-2-2'
union all
select 3, '2005-2-3'CREATE TABLE b (
id int ,
date varchar(32)
)
insert into b
select 1,'2005-2-20'
union all
select 2, '2005-2-3'--------------------------select * from
(
select Aid as id,
max(case
when A数量<>B数量 and Bid is not null then A最大日期 --// 取a中date最大得
when Bid is null then Adate --//a表中有b表没有 取A
else null --// 不取
end) as date
--select *
from
(
select * from
(
select id as Aid,
date as Adate,
(select count(1) from a TmpA where TmpA.id=a.id) as A数量,
(select max(date) from a TmpA where TmpA.id=a.id) as A最大日期
from a
) tpA,
(
select id as Bid,
date as Bdate,
(select count(1) from b TmpB where TmpB.id=b.id) as B数量
from b
) tpB
where tpA.Aid=tpB.Bid
union all
select id,date,null,null,null,null,null
from a
where a.id not in(select distinct id from b)
) BigTmp
group by Aid
) lastTmp
where date is not null--结果-------------------------------2005-2-1 3
2005-2-3 1
date as Adate,
(select count(1) from a TmpA where TmpA.id=a.id) as A数量,
(select max(date) from a TmpA where TmpA.id=a.id) as A最大日期
from a
) tpA,
(
select id as Bid,
date as Bdate,
(select count(1) from b TmpB where TmpB.id=b.id) as B数量
from b
) tpB
where tpA.Aid=tpB.Bid
union all --//A有B没有
select id,date,null,null,null,null,null
from a
where a.id not in(select distinct id from b)第二步:实现条件 case
when A数量<>B数量 and Bid is not null then A最大日期 --// 取a中date最大得
when Bid is null then Adate --//a表中有b表没有 取A
else null --// 不取
end) as date------------------------------这个查询的确麻烦哈!!!