mysql - 可以使用 SELECT 查询，但不能使用它来创建新表

Question

我有一个又长又慢的查询 -

SELECT
0 as 'Check',
DATE_FORMAT(`listlineitems`.`dateEntered`,"%Y-%m-%d") as 'Date Entered',
`listlineitems`.`itemId` as 'parentTableIdx',
`listlineitems`.`parentProjectId` as 'parentProjectIdx',
`listlineitems` .`idx` as 'ID',
if(`listlineitems`.`active`=1, 'Active', 'Inactive') as 'Active/Inactive',
CONCAT(`listUsers`.`FirstName`,' ',`listUsers`.`LastName`) as 'Employee',
CASE `listlineitems`.`type` WHEN 1 THEN 'Time Entry' WHEN 2 THEN 'Expense Entry' END as 'Type',
`listcustomers`.`name` as 'Customer',
`listlocations`.`name` as 'Location',
`listareas`.`name` as 'Area',
`listassets`.`name` as 'Asset',
`listprojects`.`name` as 'Project',
`listprojects`.`number`'Project #',
`listprojects`.`autoassign`'autoassign',
`listactivities`.`name` as 'Activity',
(CASE `listlineitems`.`type` WHEN 1 THEN `listlineitems`.`qty` WHEN 2 THEN `listlineitems`.`qty` END) as 'Quantity',
`listlineitems`.`taxable` as 'Taxable',
`listlineitems`.`totalAmount`-`listlineitems`.`taxAmount` as 'Pre-Tax Amount',
`listlineitems`.`taxAmount` as 'Tax Amount',
`listlineitems`.`totalAmount` as 'Total Amount',
`listCustomers`.`idx` as 'parentCustomerIdx',
`listLocations`.`idx` as 'parentLocationIdx',
`listAreas`.`idx` as 'parentAreaIdx',
`listAssets`.`idx` as 'parentAssetIdx',
CONCAT(`listcustomers`.`name`,"/",`listlocations`.`name`,"/",`listareas`.`name`,"/",`listassets`.`name`,"/",`listprojects`.`name`) as 'Path',
if(`listlineitems`.`customerViewable` = 1, 'Yes', 'No') as 'Cust. Viewable',
(CASE WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI` - `listexpenseentry`.`TaxCostToPSI` ELSE `listlineitems`.`totalAmount`-`listlineitems`.`taxAmount` END) as 'preTaxCostPSI',
(CASE WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TaxCostToPSI` ELSE `listlineitems`.`taxAmount` END) as 'taxCostPSI',
(CASE WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI` ELSE `listlineitems`.`totalAmount` END) as 'totalCostPSI',
view_solinx2.lastAltered as 'lastalteredSO',
view_polinx2.lastAlteredPO as 'lastalteredPO',
view_invlinx2.lastAlteredInv as 'lastalteredInv',
view_solinx2.lastAlteredAfterConfirmation as 'lastAlteredAfterConfirmation',
view_solinx2.roleIdSO as 'roleIdSO',
view_polinx2.roleIdPO as 'roleIdPO',
view_polinx2.userIdPO as 'userIdPO',
view_polinx2.lastAlteredafterConfirmation  as 'lastAlteredAfterConfirmationPO',
view_invlinx2.roleIdInv as 'roleIdInv',
view_invlinx2.userIdInv as 'userIdInv',
view_invlinx2.lastAlteredafterConfirmation as 'lastAlteredAfterConfirmationInv',
view_solinx2.roleId as 'roleId',
view_solinx2.userId as 'userId',
view_solinx2.soId as 'SOId',
view_solinx2.autoassignSO as 'autoassignSO',
if(view_solinx2.notNeeded = 1, "Not Needed", view_solinx2.number) as 'SOname',
view_solinx2.dateEntered as 'SoDate',
view_solinx2.totalSOAmount as 'SoTotal',

view_invlinx2.invId as 'InvId',
if(view_solinx2.notNeeded = 1,  "------", view_invlinx2.`number`) as 'InvName',
view_invlinx2.dateEntered as 'InvDate',
view_invlinx2.amount as 'InvTotal',

view_polinx2.poId as 'POId',
if(view_solinx2.notNeeded = 1,  "------",  view_polinx2.`number`) as 'POName',
view_polinx2.dateEntered as 'PODate',
view_polinx2.amount as 'POTotal',

(select 
listsalesorders.number
from listsalesorders
where  listsalesorders.idx  = autoassign) as 'test',

`listlineitems`.*

FROM `listlineitems` 
LEFT JOIN `listUsers` ON `listlineitems`.`individualId` = `listUsers`.`idx`
LEFT JOIN `listprojects` ON `listlineitems`.`parentProjectId` = `listprojects`.`idx`
LEFT JOIN `listassets` ON `listlineitems`.`parentAssetId` = `listassets`.`idx`
LEFT JOIN `listareas` ON `listlineitems`.`parentAreaId` = `listareas`.`idx`
LEFT JOIN `listlocations` ON `listlineitems`.`parentLocationId` = `listlocations`.`idx`
LEFT JOIN `listcustomers` ON `listlineitems`.`parentCustomerId` = `listcustomers`.`idx`
LEFT JOIN `listactivities` ON `listactivities`.`idx` = `listlineitems`.`activityCode`
LEFT JOIN `listexpenseentry` ON (`listexpenseentry`.`idx` = `listlineitems`.`itemId` AND `listlineitems`.`type` = 2)
LEFT JOIN view_solinx2 ON view_solinx2.idx = listlineitems.idx
LEFT JOIN view_polinx2 ON view_polinx2.idx = listlineitems.idx
LEFT JOIN view_invlinx2 ON view_invlinx2.idx = listlineitems.idx

这运行得很好(除了速度)。我想制作一个基本上是这些以前记录的数据仓库的表，这样我就可以更快地获取它们并根据需要更新仓库。

但是，现在将 CREATE TABLE records_warehouse 放在我的查询前面会告诉我 错误代码:1060:重复的列名称“类型”。我只看到一个名为 type 的列，而且当我单独运行 SELECT 语句时也没有问题。为什么会发生这种情况，我该如何解决？

使用 MySQL 5.6。

Answer 1

您有 2 个同名的列:

SELECT 
CASE `listlineitems`.`type` WHEN 1 THEN 'Time Entry' WHEN 2 THEN 'Expense Entry' END as 'Type',
-- listlineitems have the column named 'type' hidden under *
`listlineitems`.*

您应该明确列出列并在需要时添加别名。