如何在 sql 準備語句中選擇特定列？

我創建了一個準備好的語句來輸出文件並在文件名中添加日期。如果我選擇整個數據庫，那部分就可以工作。我想選擇要包含在查詢中的特定列，而不是全部。

我是 SQL 語法的新手，所以請在回答時注意這一點。:)

這是我在要求文件名中的日期之前開始的內容：

   USE dbx;
SELECT
  custtype.custtype,
  custtype.custtypedesc,
  USER.jobtitle,
  USER.branch,
  log.custid,
  log.username,
  log.actualuser,
  log.timestamp,
  log.action,
  log.data INTO outfile '/inetpub/wwwroot/reports/Activity_Log.csv' fields terminated BY ',' optionally enclosed BY '"' lines terminated BY '\n' 
FROM
  log 
  LEFT OUTER JOIN
     USER 
     ON log.custid = USER.custid 
     AND log.username = USER.username 
  LEFT OUTER JOIN
     custtype 
     ON custtype.custtype = USER.custtype 
WHERE
  USER.email NOT LIKE " % @sample.com" 
  AND USER.email NOT LIKE " % @google.com" 
  AND log.username NOT LIKE "Guest % "

您可以看到我正在根據我的要求選擇各個列。效果很好。但是，我不知道如何在下一個查詢中選擇這樣的單個列…

這是添加日期要求後我現在擁有的內容：

SET
  @TS = DATE_FORMAT(NOW(), '_%m_%d_%Y');
SET
  @FOLDER = '/inetpub/wwwroot/reports/';
SET
  @PREFIX = 'Activity_Report_';
SET
  @EXT = '.csv';
USE webcat_336;
SET
  @CMD = concat("
  SELECT
     custtype.CustType, custtype.CustTypeDesc 
  FROM
     log INTO OUTFILE", @FOLDER , @TS, @EXT);
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';
' ESCAPED BY '\"'",
" LINES TERMINATED BY '\r\n';
"

如果我將第二個範例中的“custtype.CustType, custtype.CustTypeDesc”替換為 *，則該文件會正確生成並按預期命名為“Activity_Log_m_d_Y.csv”。

最新更新

我現在可以使用以下程式碼選擇我最初想要的所有列。但是，WHERE 子句打破了它。

USE db_name;
SELECT
  @cmd : = concat( "
  SELECT
     custtype.CustType , custtype.CustTypeDesc , user.JobTitle , user.Branch , log.CustID , log.Username , log.ActualUser , log.Timestamp , log.Action , log.Data into OUTFILE '/inetpub/wwwroot/reports/Activity_Report_",
DATE_FORMAT(now(), '%m_%d_%Y'), ".csv'
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' 
  FROM
     log 
     LEFT OUTER JOIN
        user 
        ON log.CustID = user.CustID 
        AND log.Username = user.Username 
     LEFT OUTER JOIN
        custtype 
        ON custtype.CustType = user.CustType 
  WHERE
     USER.email NOT LIKE " % @sample.com" 
     AND USER.email NOT LIKE " % @google.com" 
     AND log.username NOT LIKE "Guest % " ");
PREPARE stmt 
  FROM
     @cmd;
EXECUTE stmt;

問題

如何僅選擇特定列而不是上面範例中的所有列？

在我了解 mysql 之後 - 你不會改變任何關於

FROM、JOIN 和最有可能的 WHERE

只更改所需的列….如果您的 3 個表格一起工作，即使您只想查看單個列，您也無法輕鬆分解報告的這個“團隊”

SET
  @TS = DATE_FORMAT(NOW(), '_%m_%d_%Y');
SET
  @FOLDER = '/inetpub/wwwroot/reports/';
SET
  @PREFIX = 'Activity_Report_';
SET
  @EXT = '.csv';
USE webcat_336;
SET
  @CMD = concat("
  SELECT
     custtype.CustType, custtype.CustTypeDesc 
   INTO OUTFILE", @FOLDER , @TS, @EXT ," FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'
FROM
  log 
  LEFT OUTER JOIN
     USER 
     ON log.custid = USER.custid 
     AND log.username = USER.username 
  LEFT OUTER JOIN
     custtype 
     ON custtype.custtype = USER.custtype 
WHERE
  USER.email NOT LIKE " % @sample.com" 
  AND USER.email NOT LIKE " % @google.com" 
  AND log.username NOT LIKE "Guest % ");

"

缺少一些引號：

CONCAT("... INTO OUTFILE", @FOLDER , @TS, @EXT ," FIELDS ...

–>

CONCAT("... INTO OUTFILE'", @FOLDER , @TS, @EXT ,"' FIELDS ...

引用自：https://dba.stackexchange.com/questions/252935