我有4个表,default_table,table_a,table_b,table_c,每个表都有两列,
我想从所有表中选择name
和id
,然后对其进行排序,
我尝试"SELECT Name,ID FROM default_table, table_a, table_b, table_c ORDER BY Name"
但这给了我错误,所以有人能帮我解决这个问题吗?我需要sql或php中的代码,如果可以的话,甚至需要javascript
由于order by
是在联合后应用的,您只需执行:
select id, name from default_table
union all select id, name from table_a
union all select id, name from table_b
union all select id, name from table_c
order by name
你的问题是,你正在对所有的表数据进行笛卡尔乘积,所以它无法计算出你指的是哪个id
或name
。即使可以,由于乘法效应,您也会得到比预期更多的行。您只想通过将所有行添加到输出中来连接数据。
进一步解释区别,假设您有两个表:
table1 table2
====== ======
id name id name
-- ---- -- ------
1 pax 1 jill
2 bob 2 debbie
3 joe
查询:
select a.id, a.name, b.id, b.name from table1 a, table2 b
(笛卡尔乘积)会给你:
1 pax 1 jill
1 pax 2 debbie
2 bob 1 jill
2 bob 2 debbie
3 joe 1 jill
3 joe 2 debbie
(行计数是单个行计数的乘积)而:
select id, name from table1
union all
select id, name from table2
(工会)会给你:
1 pax
2 bob
3 joe
1 jill
2 debbie
(行计数是单个行计数的总和)。
我想您需要UNION
(或UNION ALL
):
SELECT *
FROM
( SELECT Name, ID
FROM default_table
UNION ALL
SELECT Name, ID
FROM table_a
UNION ALL
SELECT Name, ID
FROM table_b
UNION ALL
SELECT Name, ID
FROM table_c
) AS un
ORDER BY Name
试试这样的
SELECT
t1.name AS t1_name, t1.id AS t1_id,
t2.name AS t2_name, t2.id AS t2_id,
t3.name AS t3_name, t3.id AS t3_id,
t4.name AS t4_name, t4.id AS t4_id
FROM
default_table AS t1,
table_a AS t2,
table_b AS t3,
table_c AS t4
ORDER BY t1.name