将新行插入MYSQL中的表中&使用union all组合两个不同的查询

Question

我试图在我当前的表“ccat”中插入一组新的行，称为“Other”，据此，我设置了一个条件，即如果添加的每个类别的计数小于30，则将在“其他”列下将每个类别的计数汇总为一个新行，称为“Other”。但是，在我设法用'Other‘和'125’(小于30的所有类别的总和)添加了这个新行后，我可以使用UNION ()查询我的结果，但我无法按'num_campaigns‘按降序排序。这是我的一套密码：

INSERT INTO ccat (campaign, category)
 VALUES (
    'Other',
    ( 
        SELECT SUM(num_campaigns)FROM
            (
                SELECT category, count(category) as num_campaigns
                FROM ccat
                GROUP BY category
                HAVING count(*) <30
            ) AS Other
    )
    );

​

​

SELECT category, count(*) as num_campaigns
    FROM ccat
    GROUP BY category
    HAVING count(*) >=30
UNION 
SELECT campaign as category, category as num_campaigns
    FROM ccat
    WHERE campaign ='other'
    GROUP BY category
ORDER BY num_campaigns DESC

我现在看到的是：

​

​

我希望达到的目标是：

​

​

一组数据：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


DROP TABLE IF EXISTS `ccat`;
CREATE TABLE `ccat` (
  `campaign` text,
  `category` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `ccat` (`campaign`, `category`) VALUES
('Hope Beyond Borders 2020', 'Fundraising'),
('Hope Beyond Borders 2020', 'Migrant Workers'),
('Family of 10 needs help to make ends meet', 'Children'),
('Family of 10 needs help to make ends meet', 'Living Expenses'),
('Family of 10 needs help to make ends meet', 'Family'),
('Raya for Hope 2020', 'Raya For Hope'),
('Covid-19 Migrant Support Coalition', 'COVID-19'),
('Covid-19 Migrant Support Coalition', 'Migrant Workers'),
('Emergency Fund for Ray of Hope beneficiaries', 'Fundraising'),
('Give hope to Vivian in her painful battle against end stage cervical cancer', 'Medical'),
('Help Home Bakers through this Circuit Breaker', 'Fundraising'),
('Help Home Bakers through this Circuit Breaker', 'Living Expenses'),
('Help Home Bakers through this Circuit Breaker', 'Food'),
('Family of 5 loses income due to Covid-19', 'Children'),
('Family of 5 loses income due to Covid-19', 'Family'),
('Give Hope for Mdm Noordewi and her family', 'Living Expenses'),
('Give Hope for Mdm Noordewi and her family', 'Family'),
('Project Providence', 'Fundraising'),
('Project Providence', 'Living Expenses'),
('Project Providence', 'Migrant Workers'),
('beINVOLVED - Migrant Workers Relief Efforts', 'Fundraising'),
('beINVOLVED - Migrant Workers Relief Efforts', 'Migrant Workers'),
('Good Food For Community', 'Children'),
('Good Food For Community', 'COVID-19'),
('Good Food For Community', 'Fundraising'),
('Good Food For Community', 'Migrant Workers'),
('Good Food For Community', 'Elderly'),
('Good Food For Community', 'Family'),
('Good Food For Community', 'Food'),
('Feeding the Hearts and Tummies of Underprivileged Kids', 'Children'),
('Feeding the Hearts and Tummies of Underprivileged Kids', 'COVID-19'),
('Feeding the Hearts and Tummies of Underprivileged Kids', 'Fundraising'),
('Feeding the Hearts and Tummies of Underprivileged Kids', 'Family'),
('Single Parents Giving Circle', 'Giving Circle'),
('Single Parents Giving Circle', 'Single Parents'),
('Your support can help lighten the financial burdens of Aidah as she fights cancer to get well again!', 'Children'),
('Your support can help lighten the financial burdens of Aidah as she fights cancer to get well again!', 'Family'),
('Caring Amidst COVID-19 Fund', 'COVID-19'),
('Caring Amidst COVID-19 Fund', 'Fundraising'),
('Caring Amidst COVID-19 Fund', 'Living Expenses'),
('Caring Amidst COVID-19 Fund', 'Family'),
('Caring Amidst COVID-19 Fund', 'Medical'),
('ItsRainingRaincoats', 'COVID-19'),
('ItsRainingRaincoats', 'Fundraising'),
('ItsRainingRaincoats', 'Migrant Workers'),
('DO GOOD . SEND LOVE', 'COVID-19'),
('DO GOOD . SEND LOVE', 'Fundraising'),
('DO GOOD . SEND LOVE', 'Family'),
('DO GOOD . SEND LOVE', 'Food'),
('DO GOOD . SEND LOVE', 'Special Needs'),
('Bandwidth - Apart yet Together', 'COVID-19'),
('Bandwidth - Apart yet Together', 'Fundraising'),
('Help a cancer patient as her husband looks for a job', 'Living Expenses'),
('Help a cancer patient as her husband looks for a job', 'Family'),
('Single Mum of 3 needs your help, her youngest child has Stage 4 of a rare cancer', 'Children'),
('Single Mum of 3 needs your help, her youngest child has Stage 4 of a rare cancer', 'COVID-19'),
('Single Mum of 3 needs your help, her youngest child has Stage 4 of a rare cancer', 'Living Expenses'),
('Single Mum of 3 needs your help, her youngest child has Stage 4 of a rare cancer', 'Family'),
('Single Mum of 3 needs your help, her youngest child has Stage 4 of a rare cancer', 'Single Parents'),
('Adopt-a-Bento', 'COVID-19'),
('Adopt-a-Bento', 'Fundraising'),
('Adopt-a-Bento', 'Food'),
('Give Mr Kumar a second chance', 'Living Expenses'),
('Help Rajen better himself and get back in touch with his family', 'Living Expenses'),
('Mr Silvaraj wants to turn over a new leaf', 'Transport'),
('Mr Silvaraj wants to turn over a new leaf', 'Living Expenses'),
('Mr Silvaraj wants to turn over a new leaf', 'Medical'),
('Help Sam continue onto his dream', 'Living Expenses'),
('Help Mr Raja and his elderly parents', 'Living Expenses'),
('Help Mr Raja and his elderly parents', 'Family'),
('Mr Raj wants to help his family', 'Living Expenses'),
('Mr Raj wants to help his family', 'Family'),
('Help 6,000 Socially Isolated & At-Risk Elderly Overcome COVID-19', 'COVID-19'),
('Help 6,000 Socially Isolated & At-Risk Elderly Overcome COVID-19', 'Fundraising'),
('Help 6,000 Socially Isolated & At-Risk Elderly Overcome COVID-19', 'Elderly'),
('FDW suddenly finds a lump in her thyroid, sent home for surgery', 'Foreign Domestic Worker'),
('FDW suddenly finds a lump in her thyroid, sent home for surgery', 'Fundraising'),
('FDW suddenly finds a lump in her thyroid, sent home for surgery', 'Medical'),
('Spread warmth & health through a cup of Chai', 'COVID-19'),
('Spread warmth & health through a cup of Chai', 'Migrant Workers'),
('Spread Love to Lee Ah Mooi Old Age Home', 'COVID-19'),
('Spread Love to Lee Ah Mooi Old Age Home', 'Fundraising'),
('Spread Love to Lee Ah Mooi Old Age Home', 'Elderly'),
('Give hope to Mr M and his family back home', 'Living Expenses'),
('Give hope to Mr M and his family back home', 'Migrant Workers'),
('Help Mr WQ support his wife and her medical costs back home', 'Living Expenses'),
('Help Mr WQ support his wife and her medical costs back home', 'Migrant Workers'),
('He came to Singapore with high hopes of escaping poverty and providing for his family', 'Living Expenses'),
('He came to Singapore with high hopes of escaping poverty and providing for his family', 'Migrant Workers'),
('Can You Help Our Local Elderly During This COVID-19 Situation?', 'COVID-19'),
('Can You Help Our Local Elderly During This COVID-19 Situation?', 'Fundraising'),
('Can You Help Our Local Elderly During This COVID-19 Situation?', 'Elderly'),
('Give hope to a single mum with 4 children who lost her job recently', 'Children'),
('Give hope to a single mum with 4 children who lost her job recently', 'COVID-19'),
('Give hope to a single mum with 4 children who lost her job recently', 'Family'),
('Give hope to a single mum with 4 children who lost her job recently', 'Single Parents'),
('Mum of 4 struggling with illnesses holds on for her children', 'Children'),
('Mum of 4 struggling with illnesses holds on for her children', 'COVID-19'),
('Mum of 4 struggling with illnesses holds on for her children', 'Living Expenses'),
('Mum of 4 struggling with illnesses holds on for her children', 'Family'),
('Mum of 4 struggling with illnesses holds on for her children', 'Food'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'Children'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'COVID-19'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'Elderly'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'Family'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'Food'),
('41 year old single mum who lost her job wants to pick herself up for her family', 'Single Parents'),
('Give hope to a caregiver, Ashura and help lighten her load', 'Children'),
('Give hope to a caregiver, Ashura and help lighten her load', 'COVID-19'),
('Give hope to a caregiver, Ashura and help lighten her load', 'Elderly'),
('Give hope to a caregiver, Ashura and help lighten her load', 'Family'),
('Give hope to a caregiver, Ashura and help lighten her load', 'Single Parents'),
('Give hope to a caregiver, Ashura and help lighten her load', 'Special Needs'),
('Give Hope and Support to Hashik who recently lost his mum to illness', 'Elderly'),
('Give Hope and Support to Hashik who recently lost his mum to illness', 'Single Parents'),
('Buy a Meal for Needy Elderly @ $3 per meal', 'Fundraising'),
('Buy a Meal for Needy Elderly @ $3 per meal', 'Elderly'),
('Buy a Meal for Needy Elderly @ $3 per meal', 'Food'),
('Abused single mum with two autistic sons needs your help to start afresh', 'Children'),
('Abused single mum with two autistic sons needs your help to start afresh', 'COVID-19'),
('Abused single mum with two autistic sons needs your help to start afresh', 'Transport'),
('Abused single mum with two autistic sons needs your help to start afresh', 'Single Parents'),
('Abused single mum with two autistic sons needs your help to start afresh', 'Special Needs'),
('Give hope to an injured sole breadwinner as he awaits his assessment', 'Living Expenses'),
('Give hope to an injured sole breadwinner as he awaits his assessment', 'Migrant Workers'),
('Give hope to an injured sole breadwinner as he awaits his assessment', 'Accident'),
('Donate to a 29 year old suffering from mental health issues to help her get back on her feet', 'Living Expenses'),
('Donate to a 29 year old suffering from mental health issues to help her get back on her feet', 'Medical'),
('Help support Mr JH while he gets legal help to validate his claim of work injury', 'Living Expenses'),
('Help support Mr JH while he gets legal help to validate his claim of work injury', 'Migrant Workers'),
('Help support Mr JH while he

​

​

Answer 1

我尝试过使用另一种方法，这将使我达到我想要的结果。守则如下：

CREATE TABLE result
AS (SELECT category, count(*) as num_campaigns
    FROM ccat
    GROUP BY category
    HAVING count(*) >=30);

INSERT INTO result (category, num_campaigns)
VALUES (
    'Other',
    ( 
        SELECT SUM(num_campaigns)FROM
            (
                SELECT category, count(category) as num_campaigns
                FROM ccat
                GROUP BY category
                HAVING count(*) <30 
            ) AS Other
    )
    );

SELECT category, num_campaigns
FROM result
GROUP BY category
ORDER BY num_campaigns DESC