SQL Server实现mysql的group_concat函数功能

表结构入下：

-- ----------------------------
-- Table structure for area20201023
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[area20201023]') AND type IN ('U'))
    DROP TABLE [dbo].[area20201023]
GO

CREATE TABLE [dbo].[area20201023] (
  [sortid] int  NULL,
  [areacode] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [citycode] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [adcode] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [status] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [name] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [label] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [spell] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [x] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [y] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [short] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [index] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [name_en] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[area20201023] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Indexes structure for table area20201023
-- ----------------------------
CREATE NONCLUSTERED INDEX [idx_area20201023_lookup]
ON [dbo].[area20201023] (
  [areacode] ASC,
  [citycode] ASC,
  [adcode] ASC
)
GO

 

 

数据如下

INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (1, NULL, 'total', '100000', '1', '全国', '全国', NULL, '116.3683244', '39.915085', NULL, NULL, NULL);
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (4, NULL, NULL, '130000', '1', '河北', '河北省', 'HeBeiSheng', '114.502461', '38.045474', 'HB', 'H', 'Hebei Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (16, NULL, NULL, '140000', '1', '山西', '山西省', 'ShanXiSheng', '112.549248', '37.857014', 'SX', 'S', 'Shanxi Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (28, NULL, NULL, '150000', '1', '内蒙古', '内蒙古自治区', 'NeiMengGuZiZhiQu', '111.670801', '40.818311', 'NMGZZ', 'N', 'Inner Mongolia Autonomous Region');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (41, NULL, NULL, '210000', '1', '辽宁', '辽宁省', 'LiaoNingSheng', '123.429096', '41.796767', 'LN', 'L', 'Liaoning Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (56, NULL, NULL, '220000', '1', '吉林', '吉林省', 'JiLinSheng', '125.3245', '43.886841', 'JL', 'J', 'Jilin Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (66, NULL, NULL, '230000', '1', '黑龙江', '黑龙江省', 'HeiLongJiangSheng', '126.642464', '45.756967', 'HLJ', 'H', 'Heilongjiang Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (81, NULL, NULL, '320000', '1', '江苏', '江苏省', 'JiangSuSheng', '118.767413', '32.041544', 'JS', 'J', 'Jiangsu Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (95, NULL, NULL, '330000', '1', '浙江', '浙江省', 'ZheJiangSheng', '120.153576', '30.287459', 'ZJ', 'Z', 'Zhejiang Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (107, NULL, NULL, '340000', '1', '安徽', '安徽省', 'AnHuiSheng', '117.283042', '31.86119', 'AH', 'A', 'Anhui Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (124, NULL, NULL, '350000', '1', '福建', '福建省', 'FuJianSheng', '119.306239', '26.075302', 'FJ', 'F', 'Fujian Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (134, NULL, NULL, '360000', '1', '江西', '江西省', 'JiangXiSheng', '115.892151', '28.676493', 'JX', 'J', 'Jiangxi Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (146, NULL, NULL, '370000', '1', '山东', '山东省', 'ShanDong Sheng', '117.000923', '36.675807', 'SD', 'S', 'Shandong Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (164, NULL, NULL, '410000', '1', '河南', '河南省', 'HeNanSheng', '113.665412', '34.757975', 'HN', 'H', 'Henan Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (183, NULL, NULL, '420000', '1', '湖北', '湖北省', 'HuBeiSheng', '114.298572', '30.584355', 'HB', 'H', 'Hubei Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (201, NULL, NULL, '430000', '1', '湖南', '湖南省', 'HuNanSheng', '112.982279', '28.19409', 'HN', 'H', 'Hunan Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (216, NULL, NULL, '440000', '1', '广东', '广东省', 'GuangDongSheng', '113.280637', '23.125178', 'GD', 'G', 'Guangdong Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (238, NULL, NULL, '450000', '1', '广西', '广西壮族自治区', 'GuangXi ZhuangZu ZiZhiQu', '108.320004', '22.82402', 'GXZZZZ', 'G', 'Guangxi Zhuang Autonomous Region');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (253, NULL, NULL, '460000', '1', '海南', '海南省', 'HaiNanSheng', '110.33119', '20.031971', 'HN', 'H', 'Hainan Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (259, NULL, NULL, '510000', '1', '四川', '四川省', 'SiChuanSheng', '104.065735', '30.659462', 'SC', 'S', 'Sichuan Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (281, NULL, NULL, '520000', '1', '贵州', '贵州省', 'GuiZhouSheng', '106.713478', '26.578343', 'GZ', 'G', 'Guizhou Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (291, NULL, NULL, '530000', '1', '云南', '云南省', 'YunNanSheng', '102.712251', '25.040609', 'YN', 'Y', 'Yunnan Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (308, NULL, NULL, '540000', '1', '西藏', '西藏自治区', 'XiZangZiZhiQu', '91.132212', '29.660361', 'XCZZ', 'X', 'Tibet Autonomous Region');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (316, NULL, NULL, '610000', '1', '陕西', '陕西省', 'ShaanXiSheng', '108.948024', '34.263161', 'SX', 'S', 'Shaanxi Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (327, NULL, NULL, '620000', '1', '甘肃', '甘肃省', 'GanSuSheng', '103.823557', '36.058039', 'GS', 'G', 'Gansu Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (342, NULL, NULL, '630000', '1', '青海', '青海省', 'QingHaiSheng', '101.778916', '36.623178', 'QH', 'Q', 'Qinghai Province');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (351, NULL, NULL, '640000', '1', '宁夏', '宁夏回族自治区', 'NingXia HuiZu ZiZhiQu', '106.278179', '38.46637', 'NXHZZZ', 'N', 'Ningxia Hui Autonomous Region');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (357, NULL, NULL, '650000', '1', '新疆', '新疆维吾尔自治区', 'XinJiang WeiWuEr ZiZhiQu', '87.617733', '43.792818', 'XJWWEZZ', 'X', 'Xinjiang Uygur Autonomous Region');
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (376, NULL, '1886', '710000', '1', '台湾', '台湾省', 'TaiwanSheng', '121.509062', '25.044332', NULL, 'T', NULL);
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (377, NULL, '1852', '810000', '1', '香港', '香港特別行政區', 'XiangGang', '114.173355', '22.320048', NULL, 'X', NULL);
INSERT INTO [area20201023]([sortid], [areacode], [citycode], [adcode], [status], [name], [label], [spell], [x], [y], [short], [index], [name_en]) VALUES (378, NULL, '1853', '820000', '1', '澳门', '澳門特別行政區', 'AoMen', '113.54909', '22.198951', NULL, 'A', NULL);

 

SQL Server实现入下：

SELECT *
FROM (
    SELECT [index]
        ,[value] = stuff((
                SELECT ',' + name
                FROM area20201023
                WHERE [index] = t.[index]
                    AND areacode IS NULL
                FOR XML path('')
                ), 1, 1, '')
        ,count(*) AS num
    FROM area20201023 t
    WHERE areacode IS NULL
    GROUP BY [index]
    ) a
ORDER BY num DESC

主要使用到了stuff函数和for xml path()

子查询获得所需要的的数据

SELECT (SELECT ',' + name FROM area20201023 WHERE [index] = t.[index] AND areacode IS NULL FOR XML path('')
) AS num FROM area20201023 t WHERE areacode IS NULL GROUP BY [index]

得到类似数据：",河北,黑龙江,河南,湖北,湖南,海南"

stuff是字符串函数，删除指定长度的字符，并在指定的起始位置插入字符

select stuff(',河北,黑龙江,河南,湖北,湖南,海南,',1,1,''),使用空白字符替换第一个出现的‘,’

mysql实现入下：

select `index`,count(*) rownum,GROUP_CONCAT(name)
from area20201023 where areacode is null GROUP BY `INDEX`
order by rownum desc