Use MySQL to query in order except the first record - Databases - SitePoint Forums

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

沉稳的麻辣香锅 · ALL、DISTINCT、DISTINCTR ...· 1 周前 ·

有腹肌的李子 · Solved: Notebook to ...· 6 天前 ·

胡子拉碴的刺猬 · SQL Syntax in Query ...· 5 天前 ·

至今单身的橙子 · Select objects by ...· 5 天前 ·

逆袭的饭卡 · @ttk/vue-ui - npm· 3 天前 ·

神勇威武的豆浆 · C++ library support ...· 2 周前 ·

曾深爱过的野马 · vue星级评分组件源码_一行DataV-Vue· 7 月前 ·

聪明的椅子 · 坂本八幡宫 | 旅游景点 | VISIT ...· 1 年前 ·

含蓄的保温杯 · 上海市市场监督管理局· 1 年前 ·

英勇无比的金鱼 · 广汽集团与百度签署战略合作协议 - 广汽集团· 1 年前 ·

Hello there.

I need to extract data from a MySQL DB table in CSV format.

My stored procedure below that works correctly in exporting the csv file, but the data sorting is wrong, because instead of sorting as in the query it inserts the column headers at the end of all the data instead of inserting the headers on the first row .

I also tried using the syntax

CASE WHEN count_access = "count_access" THEN 0 ELSE CONVERT( count_access , UNSIGNED) END DESC

without solving the problem.

sProc

CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = MAKEDATE( YEAR ( now()), 1 );
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY );
SET @`outfull` := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-' , DATE_FORMAT(end_date,'%d_%m_%Y'), '_' , 'export.csv'); 
FLUSH HOSTS;										
SET @`qry` := CONCAT('SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	INTO OUTFILE \'', @`outfull`, '\' 
	FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC;');    
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
              you’ve managed somehow to confuse the forum’s syntax hilighter, so congratulations on that…
'SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC
(That way the query cant get confused about what you’re trying to ORDER, and what table it’s pulling from for which bit.)
PS: You’re not limiting your query to start date and end date. So… uhm… yeah. about that file name…
              Thanks for reply.
I have edit the sProc without success… it inserts the column headers at the end of all the data instead of inserting the headers on the first row .
CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = MAKEDATE( YEAR ( now()), 1 );
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY );
SET @`outfull` := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-' , DATE_FORMAT(end_date,'%d_%m_%Y'), '_' , 'export.csv'); 
FLUSH HOSTS;										
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC
) q; ');    
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
the column headers are at the top of the CSV file
the column count_access sorting is random and not from largest to smallest
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC)
) q; ');
              Thanks for reply… I tried using CAST without success
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY CAST(`count_access` AS UNSIGNED) DESC)
) q; ');
-- ----------------------------
-- Table structure for `dotable`
-- ----------------------------
DROP TABLE IF EXISTS `dotable`;
CREATE TABLE `dotable`  (
  `Start_date` date DEFAULT NULL,
  `End_date` date DEFAULT NULL,
  `users` varchar(50) DEFAULT NULL,
  `count_access` int(11) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `users`(`users`) USING BTREE
) ENGINE = MyISAM;
-- ----------------------------
-- Records of `dotable`
-- ----------------------------
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'NMADDALE', 2255, 1);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'ANTAS', 1873, 2);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'MORGONGIOR', 1554, 3);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'DIGA', 33, 4);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'SUGARDU', 544, 5);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'CARDEDU', 1332, 6);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'SBARTOLO', 761, 7);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'POGGIOPINI', 1168, 8);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BITTI', 1211, 9);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BOLOTANA', 1192, 10);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'ARBUS', 1032, 11);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'MATTARGHE', 1225, 12);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'INGEGNERIA', 2066, 13);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'CANALFON', 810, 14);
INSERT INTO `dotable` VALUES ('2024-01-01', '2024-06-09', 'BAREGA', 617, 16);
              They are tests, but the ordering continues to be wrong…
SET @`qry` := CONCAT('SELECT * 	
INTO OUTFILE \'', @`outfull`, '\' 
FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (
    SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	(SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	FROM `dotable` 
	ORDER BY `count_access` DESC)
) q; ');
This may be off topic, but if the frequency of converting is low, it may be an option to split the task using an ordinary SQL query and then convert the raw SQL to ,csv using Javascript, PHP or whatever??
Like this pseudocode:
const query = 'SELECT * FROM your_table';
connection.query(query, (err, results, fields) => {
  if (err) throw err;
  // Convert the result set to a CSV string
  let csvString = '';
  const headers = fields.map(field => field.name).join(',') + '\n';
  csvString += headers;
  results.forEach(row => {
    const rowValues = fields.map(field => {
      const value = row[field.name];
      // Escape special characters if needed
      return typeof value === 'string' ? `"${value.replace(/"/g, '""')}"` : value;
    }).join(',');
    csvString += `${rowValues}\n`;
  console.log(csvString);
  connection.end();
              Ok, I got it
because the first value is not an int but the string “count_access” mySQL is not able to order it.

If you change the code to
SELECT
	1 AS rr, 
    'Start_date',
	'End_date',
	'users',
	3  as count_access
    UNION ALL
	SELECT
	2 AS rr,
    `Start_date`,
	`End_date`,
	`users`,
	count_access
	FROM dotable
	ORDER BY rr, count_access DESC
it works.
So this is a solution (maybe not the best but it works)
    SELECT 'Start_date',
	'End_date',
	'users',
	CASE WHEN count_access = -1 THEN 'count_access' ELSE count_access END 
    (SELECT
	1 AS rr, 
    'Start_date',
	'End_date',
	'users',
	-1  as count_access
    UNION ALL
	SELECT
	2 AS rr,
    `Start_date`,
	`End_date`,
	`users`,
	count_access
	FROM dotable
	ORDER BY rr, count_access DESC) s
              Thanks a lot all for this help.

Really appreciated
SET @`qry` := CONCAT('SELECT * INTO OUTFILE \'', @`outfull`, '\' FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
FROM (( SELECT ''Start_date'', ''End_date'', ''users'', ''count_access'' ) UNION
( SELECT `Start_date`, `End_date`, `users`, IFNULL( `count_access`, 0 ) count_access FROM `dotable` )
ORDER BY
		WHEN `count_access` = "count_access" THEN
		999999 ELSE CAST( `count_access` AS SIGNED ) 
	END DESC) q;');    
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
Start_date	End_date	users	count_access
01/01/2024	09/06/2024	NMADDALE	2255
01/01/2024	09/06/2024	INGEGNERIA	2066
01/01/2024	09/06/2024	ANTAS	    1873
01/01/2024	09/06/2024	MORGONGIOR	1554
01/01/2024	09/06/2024	CARDEDU 	1332
01/01/2024	09/06/2024	MATTARGHE	1225
01/01/2024	09/06/2024	BITTI	    1211
01/01/2024	09/06/2024	BOLOTANA	1192
01/01/2024	09/06/2024	POGGIOPINI	1168
01/01/2024	09/06/2024	ARBUS	    1032
01/01/2024	09/06/2024	CANALFON	 810
01/01/2024	09/06/2024	SBARTOLO	 761
01/01/2024	09/06/2024	BAREGA	     617
01/01/2024	09/06/2024	SUGARDU	     544
01/01/2024	09/06/2024	DIGA	      33
Hello there.
I need to extract data from a MySQL DB table in CSV format.
My stored procedure below that works correctly in exporting the csv file, but the data sorting is wrong, because instead of sorting as in the query it inserts the column headers at the end of all the data instead of inserting the headers on the first row .
I also tried using the syntax
CASE WHEN count_access = "count_access" THEN 0 ELSE CONVERT(count_access, UNSIGNED) END DESC
without solving the problem.
sProc
CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = MAKEDATE( YEAR ( now()), 1 );
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY );
SET @`outfull` := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-' , DATE_FORMAT(end_date,'%d_%m_%Y'), '_' , 'export.csv'); 
FLUSH HOSTS;										
SET @`qry` := CONCAT('SELECT
	\'Start_date\',
	\'End_date\',
	\'users\',
	\'count_access\' UNION ALL
	SELECT
	`Start_date`,
	`End_date`,
	`users`,
	IFNULL(`count_access`,0) 
	INTO OUTFILE \'', @`outfull`, '\' 
	FIELDS TERMINATED BY \'\;\' LINES TERMINATED BY \'\r\n\'
	FROM `dotable` 
	ORDER BY CONVERT(`count_access`, UNSIGNED) DESC;');    
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
To ensure that the CSV file is correctly formatted with headers on the first row and the data sorted appropriately, we need to adjust the stored procedure slightly. The main issue with the original procedure is that the UNION ALL operation places the header row as part of the overall result set, which can cause issues when combined with the ORDER BY clause.
Instead, we’ll generate the CSV file in two steps:
Write the headers into the file.
Append the sorted data to the same file.
Here’s the revised version of your stored procedure:
CREATE DEFINER=`root`@`%` PROCEDURE `sProc`()
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = MAKEDATE(YEAR(NOW()), 1);
SET end_date = DATE_SUB(CURRENT_DATE, INTERVAL 8 DAY);
SET @outfull := CONCAT('C:/inetpub/ftproot/csv/', DATE_FORMAT(start_date,'%d_%m_%Y'), '-', DATE_FORMAT(end_date,'%d_%m_%Y'), '_', 'export.csv');
-- Write the header
SET @header_query := CONCAT('SELECT
    \'Start_date\' AS `Start_date`,
    \'End_date\' AS `End_date`,
    \'users\' AS `users`,
    \'count_access\' AS `count_access`
INTO OUTFILE \'', @outfull, '\'
FIELDS TERMINATED BY \';\' LINES TERMINATED BY \'\r\n\';');
PREPARE stmt FROM @header_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Append the data
SET @data_query := CONCAT('SELECT
    `Start_date`,
    `End_date`,
    `users`,
    IFNULL(`count_access`, 0)
INTO OUTFILE \'', @outfull, '\' 
FIELDS TERMINATED BY \';\' LINES TERMINATED BY \'\r\n\'
FROM `dotable`
ORDER BY CONVERT(`count_access`, UNSIGNED) DESC;');
PREPARE stmt FROM @data_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Explanation

Header Query: The first query writes only the header row into the CSV file.
Data Query: The second query appends the sorted data to the same CSV file. Note the use of INTO OUTFILE for both queries, but the file path remains the same, ensuring the data is appended after the header.
This approach ensures the headers appear first, followed by the correctly sorted data rows.