想要得到的结果,得到车牌号所对应 最大日期卡的状态,且卡的状态必须是 在 1,2,3中
原始数据
select t.vechile_code,t.card_code,c.state,t.status,t.craete_date from t_veh_carduse t ,t_acc_card c where t.card_code=c.card_code
1 a1 130002 4 2 2012-11-5 10:05:54
2 a1 130001 4 2 2012-11-5 10:04:52
3 a1 130001 4 2 2012-11-5 10:06:24
4 a1 130001 4 2 2012-11-5 10:06:45
5 a1 130003 1 1 2012-11-5 10:07:12
1 查询语句
select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.card_code = c.card_code
group by card.card_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
2 查询语句
select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.vechile_code = c. vechile_code
group by card.card_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
错误 :单行子查询返回多个行
一个车牌号 对应了很多卡号记录
3 查询语句
select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.card_code = c. card_code
group by card.vechile_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
结果:
1 4
2 4
3 1
此种结果也是错误的
4 select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.vechile_code = c.vechile_code
group by card.vechile_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
结果 :4
这种结果也是错误
以下两种写法是对的
select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.card_code = c.card_code and t.state in(1,2,3)
group by card.card_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
结果 1
select t.state
from T_Veh_Carduse c, t_acc_card t
where c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.card_code = c.card_code
group by card.card_code)
and c.craete_date = (select max(card.craete_date)
from T_Veh_Carduse card
where card.vechile_code = c.vechile_code
group by card.vechile_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
结果 1
数据源
1 a1 130002 4 2 2012-11-5 10:05:54
2 a1 130001 1 2 2012-11-5 10:04:52
3 a1 130001 1 2 2012-11-5 10:06:24
4 a1 130001 1 2 2012-11-5 10:06:45
5 a1 130003 1 1 2012-11-5 10:07:12
6 a2 130001 1 1 2012-11-5 10:44:31
1 查询结果为
4 130002
1 130003
2 结果一样
3 结果也是错误的
为什么:因为每个卡 对应 每个车 都有最大记录 所以返回多个值
数据
2 a1 130001 1 2 2012-11-5 10:04:52
6 a2 130001 1 1 2012-11-5 10:05:54
1 a1 130002 4 2 2012-11-5 10:05:54
3 a1 130001 1 2 2012-11-5 10:06:24
4 a1 130001 1 2 2012-11-5 10:06:45
5 a1 130003 1 1 2012-11-5 10:07:12
以下两种结果也是错误的
1
select t.state,t.card_code,c.vechile_code,c.craete_date
from T_Veh_Carduse c, t_acc_card t
where c.craete_date in (select max(card.craete_date)
from T_Veh_Carduse card
group by card.vechile_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
1 1 130003 a1 2012-11-5 10:07:12
2 4 130002 a1 2012-11-5 10:05:54
错误原因分析:
因为
6 a2 130001 1 1 2012-11-5 10:05:54
1 a1 130002 4 2 2012-11-5 10:05:54
最大时间一样 所以 a1除了有一个最大的时间 还有一个时间和a2匹配
2
select t.state,t.card_code,c.vechile_code,c.craete_date
from T_Veh_Carduse c, t_acc_card t
where c.craete_date in (select max(card.craete_date)
from T_Veh_Carduse card
group by card.card_code)
and c.vechile_code = 'a1'
and c.card_code = t.card_code
1 1 130001 a1 2012-11-5 10:06:45
2 4 130002 a1 2012-11-5 10:05:54
3 1 130003 a1 2012-11-5 10:07:12
这种结果错的更加离谱 会查处很多组结果