mysql - 在 mySQL 中创建更好的查询所需的指导

Question

我有一个包含数据的表格，如下所示

我创建了这个查询来返回结果

SELECT id,customer_name,job_number, 
IF(designer_name='des1',total_hrs, "") as des1,
IF(designer_name='des2',total_hrs, "") as des2,
IF(designer_name='des3',total_hrs, "") as des3,
IF(designer_name='des4',total_hrs, "") as des4 

FROM practice.test;

如果有 50 位设计师，这意味着我将不得不多次编写 If 语句，这不太好。

我想知道是否有更好的方法得到结果？

表格代码:

--
-- Database: `practice`
--

-- --------------------------------------------------------

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `designer_id` int(11) NOT NULL,
  `designer_name` varchar(45) NOT NULL,
  `customer_name` varchar(45) NOT NULL,
  `job_number` varchar(45) NOT NULL,
  `total_hrs` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `designer_id`, `designer_name`, `customer_name`, `job_number`, `total_hrs`) VALUES
(1, 1, 'des1', 'aa', 'j101', '11'),
(2, 1, 'des1', 'bb', 'j102', '8'),
(3, 2, 'des2', 'cc', 'j103', '2'),
(4, 2, 'des2', 'dd', 'j104', '2'),
(5, 2, 'des2', 'ee', 'j105', '5'),
(6, 3, 'des3', 'ff', 'j106', '13'),
(7, 3, 'des3', 'gg', 'j107', '32'),
(8, 3, 'des3', 'ii', 'j108', '4'),
(9, 3, 'des3', 'kk', 'j109', '3'),
(10, 3, 'des3', 'll', 'j110', '4'),
(11, 4, 'des4', 'mm', 'j111', '1'),
(12, 4, 'des4', 'nn', 'j112', '6');

Answer 1

您要实现的目标是什么？如果是总小时数，请尝试使用

SELECT designer_name, sum(total_hrs) FROM test GROUP BY designer_id