sql语句问题

Question

try:Select [car_sa].[enout].cardcode,[car_sa].[enout].cardtype,[car_sa].[card].carnumber,[car_sa].[enout].entertime,
[car_sa].[card].pass,[car_sa].[enout].outtime,[car_sa].[enout].pass,[car_sa].[enout].cartype,[car_sa].[enout].money,
[car_sa].[enout].opcode,[car_sa].[enout].id_no,[car_sa].[enout].iComputer 
from [car_sa].[card] 
inner join [car_sa].[enout] on [car_sa].[card].cardcode=[car_sa].[enout].cardcode
 where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 And [car_sa].[enout].outtime>='2003-09-08 00:00:00' 
and [car_sa].[enout].outtime<='2003-09-08 23:59:59' 
union all (select [car_sa].[enout_bak].cardcode,[car_sa].[enout_bak].cardtype,[car_sa].[card].carnumber,
[car_sa].[enout_bak].entertime,[car_sa].[card].pass,[car_sa].[enout_bak].outtime,[car_sa].[enout_bak].pass,
[car_sa].[enout_bak].cartype,[car_sa].[enout_bak].money,[car_sa].[enout_bak].opcode,
[car_sa].[enout_bak].id_no,[car_sa].[enout_bak].icomputer from [car_sa].[card] 
inner join [car_sa].[enout_bak] on [car_sa].[card].cardcode=[car_sa].[enout_bak].cardcode 
where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 
and [car_sa].[enout_bak].outtime>='2003-09-08 00:00:00' and [car_sa].[enout_bak].outtime<='2003-09-08 23:59:59' 
)

Answer 1

去掉第二个select的括号
Select [car_sa].[enout].cardcode,[car_sa].[enout].cardtype,[car_sa].[card].carnumber,[car_sa].[enout].entertime,[car_sa].[card].pass,[car_sa].[enout].outtime,[car_sa].[enout].pass,[car_sa].[enout].cartype,[car_sa].[enout].money,[car_sa].[enout].opcode,[car_sa].[enout].id_no,[car_sa].[enout].iComputer from [car_sa].[card] inner join [car_sa].[enout] on [car_sa].[card].cardcode=[car_sa].[enout].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 And [car_sa].[enout].outtime>='2003-09-08 00:00:00' and [car_sa].[enout].outtime<='2003-09-08 23:59:59' union all select [car_sa].[enout_bak].cardcode,[car_sa].[enout_bak].cardtype,[car_sa].[card].carnumber,[car_sa].[enout_bak].entertime,[car_sa].[card].pass,[car_sa].[enout_bak].outtime,[car_sa].[enout_bak].pass,[car_sa].[enout_bak].cartype,[car_sa].[enout_bak].money,[car_sa].[enout_bak].opcode,[car_sa].[enout_bak].id_no,[car_sa].[enout_bak].icomputer from [car_sa].[card] inner join [car_sa].[enout_bak] on [car_sa].[card].cardcode=[car_sa].[enout_bak].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 and [car_sa].[enout_bak].outtime>='2003-09-08 00:00:00' and [car_sa].[enout_bak].outtime<='2003-09-08 23:59:59' Order by [car_sa].[card].cardcode Asc

Answer 2

Select B.cardcode,B.cardtype,A.carnumber,B.entertime,A.pass,B.outtime,B.pass,B.cartype,B.money,B.opcode,B.id_no,B.iComputer 
from [car_sa].[card] A inner join [car_sa].[enout] B 
on A.cardcode=B.cardcode 
where A.eoflag=0 and A.cardtype=4 And B.outtime>='2003-09-08 00:00:00' and B.outtime<='2003-09-08 23:59:59' 
union all 
select B.cardcode,B.cardtype,A.carnumber,B.entertime,A.pass,B.outtime,B.pass,B.cartype,B.money,B.opcode,B.id_no,B.icomputer 
from [car_sa].[card] A inner join [car_sa].[enout_bak] B 
on A.cardcode=B.cardcode 
where A.eoflag=0 and A.cardtype=4 and B.outtime>='2003-09-08 00:00:00' and B.outtime<='2003-09-08 23:59:59' 
Order by A.cardcode Asc

Answer 3

Select [car_sa].[enout].cardcode,[car_sa].[enout].cardtype,[car_sa].[card].carnumber,[car_sa].[enout].entertime,[car_sa].[card].pass,[car_sa].[enout].outtime,[car_sa].[enout].pass,[car_sa].[enout].cartype,[car_sa].[enout].money,[car_sa].[enout].opcode,[car_sa].[enout].id_no,[car_sa].[enout].iComputer from [car_sa].[card] inner join [car_sa].[enout] on [car_sa].[card].cardcode=[car_sa].[enout].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 And [car_sa].[enout].outtime>='2003-09-08 00:00:00' and [car_sa].[enout].outtime<='2003-09-08 23:59:59' union all select [car_sa].[enout_bak].cardcode,[car_sa].[enout_bak].cardtype,[car_sa].[card].carnumber,[car_sa].[enout_bak].entertime,[car_sa].[card].pass,[car_sa].[enout_bak].outtime,[car_sa].[enout_bak].pass,[car_sa].[enout_bak].cartype,[car_sa].[enout_bak].money,[car_sa].[enout_bak].opcode,[car_sa].[enout_bak].id_no,[car_sa].[enout_bak].icomputer from [car_sa].[card] inner join [car_sa].[enout_bak] on [car_sa].[card].cardcode=[car_sa].[enout_bak].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 and [car_sa].[enout_bak].outtime>='2003-09-08 00:00:00' and [car_sa].[enout_bak].outtime<='2003-09-08 23:59:59' Order by [car_sa].[card].cardcode Asc或：Select [car_sa].[enout].cardcode,[car_sa].[enout].cardtype,[car_sa].[card].carnumber,[car_sa].[enout].entertime,[car_sa].[card].pass,[car_sa].[enout].outtime,[car_sa].[enout].pass,[car_sa].[enout].cartype,[car_sa].[enout].money,[car_sa].[enout].opcode,[car_sa].[enout].id_no,[car_sa].[enout].iComputer from [car_sa].[card] inner join [car_sa].[enout] on [car_sa].[card].cardcode=[car_sa].[enout].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 And [car_sa].[enout].outtime>='2003-09-08 00:00:00' and [car_sa].[enout].outtime<='2003-09-08 23:59:59' union all select * from ( select top 999999 [car_sa].[enout_bak].cardcode,[car_sa].[enout_bak].cardtype,[car_sa].[card].carnumber,[car_sa].[enout_bak].entertime,[car_sa].[card].pass,[car_sa].[enout_bak].outtime,[car_sa].[enout_bak].pass,[car_sa].[enout_bak].cartype,[car_sa].[enout_bak].money,[car_sa].[enout_bak].opcode,[car_sa].[enout_bak].id_no,[car_sa].[enout_bak].icomputer from [car_sa].[card] inner join [car_sa].[enout_bak] on [car_sa].[card].cardcode=[car_sa].[enout_bak].cardcode where [car_sa].[card].eoflag=0 and [car_sa].[card].cardtype=4 and [car_sa].[enout_bak].outtime>='2003-09-08 00:00:00' and [car_sa].[enout_bak].outtime<='2003-09-08 23:59:59' Order by [car_sa].[card].cardcode Asc) tem

Answer 4

TRY:
Select [car_sa].[enout].cardcode,[car_sa].[enout].cardtype,[car_sa].[card].carnumber,[car_sa].[enout].entertime,[car_sa].[card].pass,[car_sa].[enout].outtime,[car_sa].[enout].pass,[car_sa].[enout].cartype,[car_sa].[enout].[money],[car_sa].[enout].opcode,[car_sa].[enout].id_no,[car_sa].[enout].iComputer 
from [car_sa].[card] inner join [car_sa].[enout] 
on [car_sa].[card].cardcode=[car_sa].[enout].cardcode 
where [car_sa].[card].eoflag=0 
and [car_sa].[card].cardtype=4 
And [car_sa].[enout].outtime>='2003-09-08 00:00:00' 
and [car_sa].[enout].outtime<='2003-09-08 23:59:59' 
union all 
select [car_sa].[enout_bak].cardcode,[car_sa].[enout_bak].cardtype,[car_sa].[card].carnumber,[car_sa].[enout_bak].entertime,[car_sa].[card].pass,[car_sa].[enout_bak].outtime,[car_sa].[enout_bak].pass,[car_sa].[enout_bak].cartype,[car_sa].[enout_bak].[money],[car_sa].[enout_bak].opcode,[car_sa].[enout_bak].id_no,[car_sa].[enout_bak].icomputer 
from [car_sa].[card] inner join [car_sa].[enout_bak] 
on [car_sa].[card].cardcode=[car_sa].[enout_bak].cardcode 
where [car_sa].[card].eoflag=0 
and [car_sa].[card].cardtype=4 
and [car_sa].[enout_bak].outtime>='2003-09-08 00:00:00' 
and [car_sa].[enout_bak].outtime<='2003-09-08 23:59:59' 
Order by [car_sa].[card].cardcode Asc

Answer 5

将[car_sa].[card].cardcode 用[car_sa].[enout_bak].cardcode替换。你查询出来的字段并没有[car_sa].[card].cardcode 所以报错。

Answer 6

都不行呀,去掉括号又出现如下错误:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Answer 7

呵呵，我的理解错误啊。和字段名称无关啊。就是那句话错误啊。
去掉括号的话，因为是union all 运算，所以排序过不去。