单个查询中的多个表结果

Question

我想用单一查询从多个表中获取数据。例子：

表-A：

|  LOT  |  ITEM  |  QTY  |
--------------------------
|  001  |  PAPER |  20   |
|  002  |  INK   |  10   |
|  003  |  PEN   |  25   |
--------------------------

表-B：

 |  LOT  |  ITEM  |  QTY  |
 --------------------------
 |  001  |  PAPER |  20   |
 |  002  |  INK   |  10   |
 |  003  |  PEN   |  25   |
 --------------------------

表-C：

|  LOT  |  ITEM  |  QTY  |
--------------------------
|  001  |  PAPER |  20   |
|  002  |  INK   |  10   |
|  003  |  PEN   |  25   |
--------------------------

当我从所有表中选择LOT 001时，我希望结果是：

| TABLE NAME | LOT | ITEM | QTY |
---------------------------------
|  TABLE-A   | 001 | PAPER|  20 |
|  TABLE-B   | 001 | PAPER|  20 |
|  TABLE-C   | 001 | PAPER|  20 |
---------------------------------

Thx求助

Answer 1

试着做这样的事情：

SELECT T.* FROM(
  SELECT 'TABLE-A' AS TABLE_NAME, LOT, ITEM, QTY FROM TABLE_A
  UNION ALL
  SELECT 'TABLE-B' AS TABLE_NAME, LOT, ITEM, QTY FROM TABLE_B
  UNION ALL
  SELECT 'TABLE-C' AS TABLE_NAME, LOT, ITEM, QTY FROM TABLE_C
) AS T 
WHERE T.LOT = '001';

Answer 2

尝试如下：

SELECT 'TABLE-A' as 'TABLE-A', LOT, ITEM, QTY FROM `table_a` where LOT = '001'
UNION ALL
SELECT 'TABLE-B' as 'TABLE-B', LOT, ITEM, QTY FROM `table_b` where LOT = '001'
UNION ALL
SELECT 'TABLE-C' as 'TABLE-C', LOT, ITEM, QTY FROM `table_c` where LOT = '001';

Answer 3

我被发现了查询

SELECT  'table_a' as 'TABLE NAME', LOT, ITEM, QTY FROM `table_a` where LOT = '001'
UNION ALL
SELECT  'table_b' as 'TABLE NAME', LOT, ITEM, QTY FROM `table_b` where LOT = '001'
UNION ALL
SELECT  'table_c' as 'TABLE NAME', LOT, ITEM, QTY FROM `table_c` where LOT = '001'