作业帮 > 综合 > 作业

现有表t_task_vs_ip结构如下: task_ id start_ip end_ip 内容如下:

来源:学生作业帮 编辑:百度作业网作业帮 分类:综合作业 时间:2024/10/11 03:35: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
这样的查询结果该如何实现呢?
现有表t_task_vs_ip结构如下: task_ id start_ip end_ip 内容如下:
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 行受影响) 这个效果?