Mysql

如何更正此 mysql 連接查詢並將 1 放在同一行中？

  • June 23, 2022

此查詢接近我需要的查詢，但 google_responsive_headlines.responsive_headlines 對 google_text_ads 有很多行。我想將這些結果放在同一行，但不知道如何實現。感謝任何建議！

   SELECT google_responsive_headlines.responsive_headlines,
       google_responsive_headlines.responsive_path_1,
       google_responsive_headlines.responsive_path_2,
       d.responsive_descriptions,
       s.clicks,
       s.cost,
       s.impressions,
       s.ad_id,
       s.status,
       s.final_url,
       s.display_url
   FROM
       irene_db.google_responsive_descriptions d
           INNER JOIN
               (SELECT 
                   SUM(clicks) AS clicks,
                       SUM(cost) AS cost,
                       SUM(impressions) AS impressions,
                       google_text_ads.ad_id,
                       google_text_ads.status,
                       google_text_ads.final_url,
                       google_text_ads.display_url
               FROM
                   google_text_ads
               WHERE
                   google_text_ads.customer_id = 144
                       AND (google_text_ads.date >= '2022-05-21'
                       AND google_text_ads.date <= '2022-06-21')
                       AND google_text_ads.status IN ('ENABLED')
                       AND google_text_ads.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
               GROUP BY ad_id) s ON s.ad_id = d.ad_id
       INNER JOIN
       irene_db.google_responsive_headlines ON s.ad_id = google_responsive_headlines.ad_id
           AND google_responsive_headlines.customer_id = 144
           AND (google_responsive_headlines.date >= '2022-05-21'
           AND google_responsive_headlines.date <= '2022-06-21')
           group by google_responsive_headlines.responsive_headlines
   LIMIT 50;

這是我的結果…

在此處輸入圖像描述

如何讓 responsive_headlines 位於單獨的列中並讓查詢返回 1 行？

編輯：查詢結果在除responsive_headline 之外的所有其他列中都是相同的。google_text_ads 中每 1 行最多可以有 15 個。那麼在我的返回查詢中是否有 responsive_headline1、responsive_headline2、… responsive_headline15。

   responsive_headlines    responsive_path_1   responsive_path_2   responsive_descriptions clicks  cost    impressions ad_id   status  final_url   display_url customer_id date    type
   Retirement Plan Administration, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
   We Help Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
   We Work W/ Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD

您可以先獲取行號，然後有條件地聚合（樞軸）該行號。

連接和分組/分區列不清楚，您可能需要修改。

SELECT
   grh.*
   d.responsive_descriptions,
   s.clicks,
   s.cost,
   s.impressions,
   s.ad_id,
   s.status,
   s.final_url,
   s.display_url
FROM
   irene_db.google_responsive_descriptions d
INNER JOIN (
   SELECT 
       SUM(gta.clicks) AS clicks,
       SUM(gta.cost) AS cost,
       SUM(gta.impressions) AS impressions,
       gta.ad_id,
       gta.status,
       gta.final_url,
       gta.display_url
   FROM
       google_text_ads gta
   WHERE gta.customer_id = 144
     AND gta.date >= '2022-05-21'
     AND gta.date <= '2022-06-21'
     AND gta.status IN ('ENABLED')
     AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
   GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
   SELECT
       grh.adid,
       grh.responsive_path_1,
       grh.responsive_path_2,
       grh.responsive_descriptions,
       MAX(CASE WHEN grh.rn = 1  THEN grh.responsive_headlines END) responsive_headlines1,
       MAX(CASE WHEN grh.rn = 2  THEN grh.responsive_headlines END) responsive_headlines2,
       MAX(CASE WHEN grh.rn = 3  THEN grh.responsive_headlines END) responsive_headlines3,
       MAX(CASE WHEN grh.rn = 4  THEN grh.responsive_headlines END) responsive_headlines4,
       MAX(CASE WHEN grh.rn = 5  THEN grh.responsive_headlines END) responsive_headlines5,
       MAX(CASE WHEN grh.rn = 6  THEN grh.responsive_headlines END) responsive_headlines6,
       MAX(CASE WHEN grh.rn = 7  THEN grh.responsive_headlines END) responsive_headlines7,
       MAX(CASE WHEN grh.rn = 8  THEN grh.responsive_headlines END) responsive_headlines8,
       MAX(CASE WHEN grh.rn = 9  THEN grh.responsive_headlines END) responsive_headlines9,
       MAX(CASE WHEN grh.rn = 10 THEN grh.responsive_headlines END) responsive_headlines10,
       MAX(CASE WHEN grh.rn = 11 THEN grh.responsive_headlines END) responsive_headlines11,
       MAX(CASE WHEN grh.rn = 12 THEN grh.responsive_headlines END) responsive_headlines12,
       MAX(CASE WHEN grh.rn = 13 THEN grh.responsive_headlines END) responsive_headlines13,
       MAX(CASE WHEN grh.rn = 14 THEN grh.responsive_headlines END) responsive_headlines14,
       MAX(CASE WHEN grh.rn = 15 THEN grh.responsive_headlines END) responsive_headlines15
   FROM (
       SELECT
           grh.*,
           ROW_NUMBER() OVER (
               PARTITION BY
                 grh.ad_id,
                 grh.responsive_path_1,
                 grh.responsive_path_2,
                 grh.responsive_descriptions
               ORDER BY grh.date) rn
       FROM
           irene_db.google_responsive_headlines grh
       WHERE grh.customer_id = 144
         AND grh.date >= '2022-05-21'
         AND grh.date <= '2022-06-21'
   ) grh
   GROUP BY
       grh.responsive_headlines,
       grh.responsive_path_1,
       grh.responsive_path_2,
       grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;

在舊版本的 MySQL 上，您需要將它們全部聚合起來並用於SUBSTRING_INDEX提取每個

SELECT
   grh.*
   d.responsive_descriptions,
   s.clicks,
   s.cost,
   s.impressions,
   s.ad_id,
   s.status,
   s.final_url,
   s.display_url
FROM
   irene_db.google_responsive_descriptions d
INNER JOIN (
   SELECT 
       SUM(gta.clicks) AS clicks,
       SUM(gta.cost) AS cost,
       SUM(gta.impressions) AS impressions,
       gta.ad_id,
       gta.status,
       gta.final_url,
       gta.display_url
   FROM
       google_text_ads gta
   WHERE gta.customer_id = 144
     AND gta.date >= '2022-05-21'
     AND gta.date <= '2022-06-21'
     AND gta.status IN ('ENABLED')
     AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
   GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
   SELECT
       grh.adid,
       grh.responsive_path_1,
       grh.responsive_path_2,
       grh.responsive_descriptions,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 1) responsive_headlines1,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 2) responsive_headlines2,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 3) responsive_headlines3,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 4) responsive_headlines4,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 5) responsive_headlines5,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 6) responsive_headlines6,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 7) responsive_headlines7,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 8) responsive_headlines8,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 9) responsive_headlines9,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 10) responsive_headlines10,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 11) responsive_headlines11,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 12) responsive_headlines12,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 13) responsive_headlines13,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 14) responsive_headlines14,
       SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 15) responsive_headlines15
   FROM
       irene_db.google_responsive_headlines grh
   WHERE grh.customer_id = 144
     AND grh.date >= '2022-05-21'
     AND grh.date <= '2022-06-21'
   GROUP BY
       grh.responsive_headlines,
       grh.responsive_path_1,
       grh.responsive_path_2,
       grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;

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

相關問答

Mysql

MySQL查詢：檢測另一個表中已刪除的行並顯示在結果表中

  • August 31, 2022
檢視問答
Mysql

您可以在 sql 查詢的結果中包含兩個單獨表的列嗎？

  • March 8, 2022
檢視問答
Mysql

使用映射表時獲取未映射記錄的計數

  • March 3, 2022
檢視問答
Mysql

MySQL 不接受表名級別授予的萬用字元

  • January 14, 2022
檢視問答
Mysql

需要幫助來編寫查詢連接 3 個表

  • November 22, 2021
檢視問答
Mysql

我可以在一列上排序/分組並在頂部顯示有限數量的結果，然後以第二個排序順序顯示其餘的結果嗎？

  • August 26, 2021
檢視問答