首页 文章

如何获取MySQL中的DISTINCT列和COUNT列(MySql PIVOT表)?

提问于
浏览
2

请一些帮助来获取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 回答

  • 2
    SELECT 
    Mtab.location,
    SUM(CASE WHEN ( Mtab.size='20' AND Mtab.type='GP') THEN 1 ELSE 0 END) AS '20GP', 
    SUM(CASE WHEN ( Mtab.size='20' AND Mtab.type='HS') THEN 1 ELSE 0 END) AS '20GP', 
    SUM(CASE WHEN ( Mtab.size='20' AND Mtab.type='RF') THEN 1 ELSE 0 END) AS '20GP', 
    (
      your current query...
      your current query...
      your current query...
    )
    Mtab
    GROUP BY Mtab.location
    
  • 2

    您可以使用以下方法将数据从表格转换为第二表格 -

    SELECT location, 
    SUM(CASE WHEN size=20 AND `type`='GP' THEN COUNT(location) ELSE NULL END) AS '20GP', 
    SUM(CASE WHEN size=20 AND `type`='HD' THEN COUNT(location) ELSE NULL END) AS '20HD', 
    SUM(CASE WHEN size=20 AND `type`='RF' THEN COUNT(location) ELSE NULL END) AS '20RF' 
    FROM mytable 
    GROUP BY location, size, `type`;
    

相关问题