怎样按条件联合查询四个以上表中的数据?

[viphk]

表A,B,C,D 关键字 uid
四个表中的字段是相同的,只是表名称不同,由于估计网站后期数据量可能比较大,我为了便于管理,与减少数据的查询量,分了类放在这四个表中.
我现在想实现联合查询,先读取所有表中的记录,并且按ID排序,

然后又要能实现,分类的查询,比如我要查询所有表中KTPAY字段='uuu',的所有记录,怎么写SQL语句,
要查询B表中KTPAY字段='uuu'的记录,又怎么写语句呢?

[--小F--]

select * from A where KTPAY='uuu'
union all
select * from B where KTPAY='uuu'
union all
select * from C where KTPAY='uuu'
union all
select * from D where KTPAY='uuu' 
order by id

[ringobob]

select * from A where 条件
union all
select * from B where 条件
union all
select * from C where 条件
union all
select * from D where 条件
order by uid

[-晴天]

先分别查询,再联合,这样效率高些,排序子句只要最后一个查询语句有就行了:

select * from A where KTPAY='uuu'
union all
select * from B where KTPAY='uuu'
union all
select * from C where KTPAY='uuu'
union all
select * from D where KTPAY='uuu' order by id

[gogodiy]

一般用UNIOL ALL来连接了。

[百年树人]

select *  --这里列举除了tbname外的其他字段
from 
(select 'a' as tbname,* from a 
 union all
 select 'b',* from b
 union all
 select 'c',* from c
 union all
 select 'd',* from d
) t
where ktpay='uuu' and tbname='b'
order by id

[viphk]

嗯,我试一下,学习了!!

[viphk]

多个表的记录总数又怎么查询呢?

[mabailin]

[Quote=引用 2 楼 qianjin036a 的回复:]

先分别查询,再联合,这样效率高些,排序子句只要最后一个查询语句有就行了:
SQL code
select * from A where KTPAY='uuu'
union all
select * from B where KTPAY='uuu'
union all
select * from C where KTPAY='uuu'
union all
select * from D whe……
[/Quote]

支持，查询应该尽早将返回条数减少

[haosa]

select * from A where KTPAY='uuu'
union all
select * from B where KTPAY='uuu'
union all
select * from C where KTPAY='uuu'
union all
select * from D where KTPAY='uuu' 
order by id

[viphk]

多个表的记录总数又怎么查询呢?

[oO寒枫Oo]

select (select count(1) from A)+(select count(1) from B)+(select count(1) from C)+(select count(1) from D) as Sum_num

[So_CooL]

select * from a inner join b on a.KTPAY='uuu' = b.KTPAY='uuu'
inner join c on a.KTPAY='uuu' = c.KTPAY='uuu'
inner join d on a.KTPAY='uuu' = d.KTPAY='uuu'

[So_CooL]

select * from a inner join b on a.KTPAY = b.KTPAY
inner join c on a.KTPAY = c.KTPAY
inner join d on a.KTPAY=d.KTPAY
where  a.KTPAY = 'uuu'