php MySQL Select，多个表中同名的列，按同名的另一列排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3341693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Select, column with same name from multiple tables, order by another column with same name
提问by ina
I'm trying to select rows with a certain column name and order them by another column name.
我正在尝试选择具有特定列名的行并按另一个列名对其进行排序。
Here's my problem, into N simplified tables:
这是我的问题，变成 N 个简化表：
table 1: id, username, datetime, comment
table 1: id, username, datetime, comment
table 2: id, username, datetime, vote
table 2: id, username, datetime, vote
.
.
.
.
.
.
table N: id, username, datetime, bought
table N: id, username, datetime, bought
I want to be able to select all rows where username='Some Name', ordered by datetime -- across all N tables.
我希望能够username='Some Name'在所有 N 个表中选择按日期时间排序的所有行。
--
——
Incidentally, does anyone know of a good book or resource on learning and practicing advanced SQL? (Tried SQLZoo, but they don't have enough advance examples.. and then there are the too-advanced ones that I'm still stumped on)
顺便说一句，有人知道关于学习和练习高级 SQL 的好书或资源吗？（尝试过 SQLZoo，但他们没有足够的高级示例......然后还有一些我仍然难以理解的太高级的示例）
采纳答案by AvatarKava
If you're looking to sort across ALL the tables at the same time (a newer record from table 3 shows up before something from table 2, etc)
如果您希望同时对所有表进行排序（表 3 中较新的记录显示在表 2 中的某些内容之前，等等）
The easiest way to do this across multiple tables would be a UNION:
跨多个表执行此操作的最简单方法是 UNION：
(SELECT id, username, datetime FROM table 1 WHERE username = 'Some Name')
UNION
(SELECT id, username, datetime FROM table 2 WHERE username = 'Some Name')
UNION
(SELECT id, somethingelse AS username, date as datetime FROM table 3 WHERE somethingelse = 'Some Name')
....
ORDER BY datetime DESC
Note that when you use a union like this, all of your select statements should return the same number of columns and be named the same (you can use aliases for this as I showed above in table 3)
请注意，当您使用这样的联合时，您的所有 select 语句都应返回相同数量的列并命名相同（您可以为此使用别名，如我在上面的表 3 中所示）
回答by Janus T?ndering
Alias your tables in the query, ie.
在查询中别名您的表，即。
SELECT t1.id, t2.username, ... FROM table1 as t1
回答by fabrik
You can select same column names from different tables like:
您可以从不同的表中选择相同的列名，例如：
SELECT table1.username as t1user, table2.username as t2user...
回答by Liutas
You can use UNIONS
你可以使用联合
Select must be somthing like this:
选择必须是这样的：
(SELECT comment, date FROM table1 WHERE username='Some Name')
UNION
(SELECT vote, date FROM table2 WHERE username='Some Name')
...
UNION
(SELECT bought, date FROM tableN WHERE username='Some Name')
ORDER BY date LIMIT 100;
回答by radhason power
display_error($this->entity);
$result = get_leave_status($this->entity);
start_table(TABLESTYLE, "width=60%");
$th = array(_("#"),
_("Login Date"),
_("Login Time"),
_("Reason"),
_("Status"),
_(" "),
);
table_header($th);
$sl=1;
while($sl<13){
display_error($sl);
$str = ($sl < 10) ? 0 . $sl : $sl;
$sql="SELECT login_time,login_date,early_leave_reason FROM ".TB_PREF."hcm_attendance_".$str." WHERE emp_code=". db_escape($this->entity);
$sl++;
$result = db_query($sql, "Can't make query in loan_req table.");
$k = 0;
$i = 1;
while ($myrow = db_fetch($result)) {
alt_table_row_color($k);
label_cell($i,'align=center');
label_cell(sql2date($myrow["login_date"]),'align=center');
label_cell($myrow["login_time"],'align=center');
label_cell($myrow["early_leave_reason"], "Width=30%");
if ($myrow["status"] == 1)
label_cell('Approved');
elseif ($myrow["status"] == 2)
label_cell('Rejected');
else {
label_cell(viewer_link('Pending',"payroll/inquiry/leave_document.php?eno=".$this->vendor_no.'&lid='.$myrow['id']));
edit_button_cell("{$this->name}Edit[{$myrow['id']}]", _("EDIT"));
}
end_row();
$i++;
}
}
end_table(1);
here i have used $sl to increment table name like 0_hcm_attendance_01,0_hcm_attendance_02 -------to 0_hcm_attendance_12
here TB_PREF=0;
TB_PREF."hcm_attendance_".$str
