表A:
peono peoname cardno opertype handletime
00296584 张三 100328 申请用户卡 2007-08-22 15:37:35.607
00296584 张三 000566 换卡 2007-08-23 09:05:29.187
00083144 李四 052314 申请用户卡 2007-08-22 15:27:20.000
00083144 李四 052314 换卡 2007-08-23 10:11:26.530
00296013 王五 021108 申请用户卡 2007-08-22 15:50:10.547
00296013 王五 021108 换卡 2007-08-23 10:56:23.390
00053973 张飞 119922 申请用户卡 2007-08-23 08:52:07.340
00053973 张飞 105603 换卡 2007-08-23 09:04:20.263
......如何买语句查询如下结果,申请用户卡与换卡卡号不一致的peono:-------------
peono00296584
00053973
peono peoname cardno opertype handletime
00296584 张三 100328 申请用户卡 2007-08-22 15:37:35.607
00296584 张三 000566 换卡 2007-08-23 09:05:29.187
00083144 李四 052314 申请用户卡 2007-08-22 15:27:20.000
00083144 李四 052314 换卡 2007-08-23 10:11:26.530
00296013 王五 021108 申请用户卡 2007-08-22 15:50:10.547
00296013 王五 021108 换卡 2007-08-23 10:56:23.390
00053973 张飞 119922 申请用户卡 2007-08-23 08:52:07.340
00053973 张飞 105603 换卡 2007-08-23 09:04:20.263
......如何买语句查询如下结果,申请用户卡与换卡卡号不一致的peono:-------------
peono00296584
00053973
distinct m.peono,n.peoname
from
表A m,表A n
where
m.peono=n.peono
and
m.opertype='申请用户卡'
and
n.opertype='换卡'
and
m.cardno<>n.cardno
distinct m.peono
from
表A m,表A n
where
m.peono=n.peono
and
m.opertype='申请用户卡'
and
n.opertype='换卡'
and
m.cardno<>n.cardno
(
select peono,cardno from a group by peono,cardno having count(*) = 1
) t
(
select peono,cardno ,count(*) from a group by peono,cardno having count(*) >1
) t
更正一下
-------------------
select distinct peono from 表名 a where exists
(select 1 from 表名 where a.peono=peono and cast(a.cardno as int)>cast(cardno as int)and cast(a.cardno as int)<cast(cardno as int))
再次更正一下 这次一定对
-------------------
select distinct peono from 表名 a where exists
(select 1 from 表名 where a.peono=peono and (cast(a.cardno as int)>cast(cardno as int) or cast(a.cardno as int)<cast(cardno as int)))
(
select peono,cardno ,count(*) from a group by peono,cardno having count(*) >1
) t
---------------------------
鹤 兄
是你自己的写错了吧???
再看看题目要求~~~~
的是正确的!
Select peono From 表A As T Where opertype='申请用户卡' And Exists
(Select 1 From 表A Where peono=T.peono And peoname=T.peoname And cardno<>T.cardno)