请一些帮助来获取mysql中的列数...我不知道如何编写以下问题的查询 .
我正在使用以下查询
SELECT * FROM
(
SELECT cont_details.*,
location.location,
depot_details.depot_name as depot_name,
depot_details.depot_code as depot_code,
block.block_id,
depot_details.depot_city,
depot_tracking.depot_tracking_id as depot_tracking_id
FROM depot_tracking
LEFT JOIN depot_details
ON depot_tracking.depot_details_id = depot_details.depot_details_id
LEFT JOIN cont_details
ON depot_tracking.cont_details_id = cont_details.cont_details_id
LEFT JOIN block
ON depot_tracking.cont_details_id = block.cont_details_id
LEFT JOIN cont_sold
ON depot_tracking.cont_details_id = cont_sold.cont_details_id
LEFT JOIN location
ON depot_details.depot_city = location.port_code
WHERE
depot_tracking.status = "0"
AND depot_tracking.gate_out =""
AND block.block_id IS NULL
AND cont_sold.cont_sold_id IS NULL
ORDER BY
cont_details.cont_details_id ASC,
str_to_date(depot_tracking.gate_in,"%d-%m-%Y") DESC
)
origin GROUP BY origin.cont_details_id
并且此查询的输出显示如下(只是示例coloumn名称将更改)
------------------------------------------
location | size | type | price |
-------------------------------------------
USA | 20 | GP | 230 |
USA | 20 | GP | 330 |
UAE | 40 | HD | 230 |
IND | 40 | RF | 230 |
IND | 30 | FR | 430 |
IND | 40 | FR | 870 |
USA | 30 | HD | 230 |
UAE | 40 | RF | 430 |
USA | 30 | GP | 230 |
UAE | 20 | HD | 445 |
UAE | 20 | RF | 323 |
IND | 30 | FR | 130 |
-------------------------------------------
现在从这张表我需要根据位置,大小和类型coloumns得到不同的位置和计数 . 示例如下所示 .
--------------------------------------------------------------------------------
location | 20GP | 20HD | 20RF | 30FR | 30HD | 30GP | 30GP | 40HD | 40FR | 40RF |
--------------------------------------------------------------------------------
UAE | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 |
USA | 2 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 |
IND | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 1 |
--------------------------------------------------------------------------------
请有人帮我获取上面的表输出以及如何进行查询 . 谢谢 .
2 回答
您可以使用以下方法将数据从表格转换为第二表格 -