搜搜考试网现有表t_task_vs_ip结构如下： task_ id start_ip end_ip 内容如下：
来源：学生作业帮 编辑：搜搜考试网作业帮 分类：综合作业 时间：2024/06/04 16:47:10
现有表t_task_vs_ip结构如下： task_ id start_ip end_ip 内容如下：
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.1
2 192.168.0.13 192.168.0.13
2 192.168.0.16 192.168.0.16
要求经过查询以后变为：
task_ id start_ip end_ip
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.16
这样的查询结果该如何实现呢?
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.1
2 192.168.0.13 192.168.0.13
2 192.168.0.16 192.168.0.16
要求经过查询以后变为：
task_ id start_ip end_ip
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.16
这样的查询结果该如何实现呢?
CREATE TABLE t_task_vs_ip (
task_id int,
start_ip varchar(15),
end_ip varchar(15)
);
GO
INSERT INTO t_task_vs_ip
SELECT 1,'0.0.0.0','0.0.0.1' UNION ALL
SELECT 2,'192.168.0.1','192.168.0.1' UNION ALL
SELECT 2,'192.168.0.13','192.168.0.13' UNION ALL
SELECT 2,'192.168.0.16','192.168.0.16'
GO
1> SELECT
2> task_id,MIN(start_ip),MAX(end_ip)
3> FROM
4> t_task_vs_ip
5> GROUP BY
6> task_id;
7> GO
task_id
----------- --------------- ---------------
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.16
(2 行受影响)
再问： 恩，对我也是用的这个方法，如果现在以ID 分组，列出所有IP又该如何实现呢？
再答： SELECT task_id, STUFF( ( SELECT ',' + ip FROM ( SELECT start_ip IP FROM t_task_vs_ip subTitle WHERE task_id = t_task_vs_ip.task_id UNION SELECT end_ip IP FROM t_task_vs_ip subTitle WHERE task_id = t_task_vs_ip.task_id ) subQuery FOR XML PATH('') ), 1, 1, '') AS allTitle FROM t_task_vs_ip GROUP BY task_id task_id allTitle ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 0.0.0.0,0.0.0.1 2 192.168.0.1,192.168.0.13,192.168.0.16 (2 行受影响) 这个效果？
task_id int,
start_ip varchar(15),
end_ip varchar(15)
);
GO
INSERT INTO t_task_vs_ip
SELECT 1,'0.0.0.0','0.0.0.1' UNION ALL
SELECT 2,'192.168.0.1','192.168.0.1' UNION ALL
SELECT 2,'192.168.0.13','192.168.0.13' UNION ALL
SELECT 2,'192.168.0.16','192.168.0.16'
GO
1> SELECT
2> task_id,MIN(start_ip),MAX(end_ip)
3> FROM
4> t_task_vs_ip
5> GROUP BY
6> task_id;
7> GO
task_id
----------- --------------- ---------------
1 0.0.0.0 0.0.0.1
2 192.168.0.1 192.168.0.16
(2 行受影响)
再问： 恩，对我也是用的这个方法，如果现在以ID 分组，列出所有IP又该如何实现呢？
再答： SELECT task_id, STUFF( ( SELECT ',' + ip FROM ( SELECT start_ip IP FROM t_task_vs_ip subTitle WHERE task_id = t_task_vs_ip.task_id UNION SELECT end_ip IP FROM t_task_vs_ip subTitle WHERE task_id = t_task_vs_ip.task_id ) subQuery FOR XML PATH('') ), 1, 1, '') AS allTitle FROM t_task_vs_ip GROUP BY task_id task_id allTitle ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 0.0.0.0,0.0.0.1 2 192.168.0.1,192.168.0.13,192.168.0.16 (2 行受影响) 这个效果？