【高分求解】一SQL语句

Question

表A(ip为主键)IP地址    使用端口     端口总数     设备类型
==============================================
1.1.1.1     8            48           A
1.1.1.1     6            45           B
1.1.1.1     0            50           C
1.1.1.2     5            64           A
1.1.1.2     6            32           C 
......要求select出IP地址    A使用端口     A端口总数     B使用端口     B端口总数      C使用端口     C端口总数
===========================================================================================
1.1.1.1     8            48              6             45             0             50
1.1.1.2     5            64              0              0             6             32
......

Answer 1

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-02 13:02:52
--> Version: SQL Server 2005
---------------------------------------------
    
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (IP地址 varchar(7),使用端口 int,端口总数 int,设备类型 varchar(1))
insert into [tb]
select '1.1.1.1',8,48,'A' union all
select '1.1.1.1',6,45,'B' union all
select '1.1.1.1',0,50,'C' union all
select '1.1.1.2',5,64,'A' union all
select '1.1.1.2',6,32,'C'declare @s varchar(4000)select @s=isnull(@s+',','')+
'max(case when 设备类型='''+设备类型+''' then 使用端口 else 0 end) ['+设备类型+'使用端口],'+
'sum(case when 设备类型='''+设备类型+''' then 使用端口 else 0 end) ['+设备类型+'端口总数]'
from (select distinct 设备类型 from tb)tset @s='select IP地址,'+@s+' from tb group by IP地址'
exec(@s)IP地址    A使用端口       A端口总数       B使用端口       B端口总数       C使用端口       C端口总数
------- ----------- ----------- ----------- ----------- ----------- -----------
1.1.1.1 8           8           6           6           0           0
1.1.1.2 5           5           0           0           6           6(2 行受影响)

Answer 2

---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([IP地址] varchar(7),[使用端口] int,[端口总数] int,[设备类型] varchar(1))
insert [A]
select '1.1.1.1',8,48,'A' union all
select '1.1.1.1',6,45,'B' union all
select '1.1.1.1',0,50,'C' union all
select '1.1.1.2',5,64,'A' union all
select '1.1.1.2',6,32,'C'
 
---查询---
DECLARE @SQL VARCHAR(8000)
SELECT 
  @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN 设备类型='''+设备类型+''' THEN 使用端口 ELSE 0 END) AS ['+设备类型+'使用端口],SUM(CASE WHEN 设备类型='''+设备类型+''' THEN 端口总数 ELSE 0 END) AS ['+设备类型+'端口总数]'
FROM
  (SELECT DISTINCT 设备类型 FROM A) T
SET @SQL='SELECT [IP地址],'+@SQL+' FROM A GROUP BY [IP地址]'EXEC(@SQL)---结果---
IP地址    A使用端口       A端口总数       B使用端口       B端口总数       C使用端口       C端口总数       
------- ----------- ----------- ----------- ----------- ----------- ----------- 
1.1.1.1 8           48          6           45          0           50
1.1.1.2 5           64          0           0           6           32

Answer 3

修改下
---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-02 13:02:52
--> Version: SQL Server 2005
---------------------------------------------
    
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (IP地址 varchar(7),使用端口 int,端口总数 int,设备类型 varchar(1))
insert into [tb]
select '1.1.1.1',8,48,'A' union all
select '1.1.1.1',6,45,'B' union all
select '1.1.1.1',0,50,'C' union all
select '1.1.1.2',5,64,'A' union all
select '1.1.1.2',6,32,'C'declare @s varchar(4000)select @s=isnull(@s+',','')+
'max(case when 设备类型='''+设备类型+''' then 使用端口 else 0 end) ['+设备类型+'使用端口],'+
'sum(case when 设备类型='''+设备类型+''' then 端口总数 else 0 end) ['+设备类型+'端口总数]'
from (select distinct 设备类型 from tb)tset @s='select IP地址,'+@s+' from tb group by IP地址'
exec(@s)
IP地址    A使用端口       A端口总数       B使用端口       B端口总数       C使用端口       C端口总数
------- ----------- ----------- ----------- ----------- ----------- -----------
1.1.1.1 8           48          6           45          0           50
1.1.1.2 5           64          0           0           6           32(2 行受影响)
 

Answer 4

上面的不是存储过程，是动态SQL语句。
如果你要求的结果是固定的，也可以写成静态的。
把＠sql变量的值写出来就可以了。

Answer 5

这不是存储过程，是动态语句如果你的设备类型只是ABC三种的话，就可以不用动态语句

Answer 6

如果设备是固定的话，可以参考以下语句
---查询---
SELECT 
  [IP地址],
  MAX(CASE WHEN 设备类型='A' THEN 使用端口 ELSE 0 END) AS [A使用端口],
  SUM(CASE WHEN 设备类型='A' THEN 端口总数 ELSE 0 END) AS [A端口总数],
  MAX(CASE WHEN 设备类型='B' THEN 使用端口 ELSE 0 END) AS [B使用端口],
  SUM(CASE WHEN 设备类型='B' THEN 端口总数 ELSE 0 END) AS [B端口总数],
  MAX(CASE WHEN 设备类型='C' THEN 使用端口 ELSE 0 END) AS [C使用端口],
  SUM(CASE WHEN 设备类型='C' THEN 端口总数 ELSE 0 END) AS [C端口总数] 
FROM 
  A 
GROUP BY 
  [IP地址]---结果---
IP地址    A使用端口       A端口总数       B使用端口       B端口总数       C使用端口       C端口总数       
------- ----------- ----------- ----------- ----------- ----------- ----------- 
1.1.1.1 8           48          6           45          0           50
1.1.1.2 5           64          0           0           6           32

Answer 7

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-02 13:02:52
--> Version: SQL Server 2005
---------------------------------------------
    
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (IP地址 varchar(7),使用端口 int,端口总数 int,设备类型 varchar(1))
insert into [tb]
select '1.1.1.1',8,48,'A' union all
select '1.1.1.1',6,45,'B' union all
select '1.1.1.1',0,50,'C' union all
select '1.1.1.2',5,64,'A' union all
select '1.1.1.2',6,32,'C' select 
IP地址,
A使用端口=max(case when 设备类型='A' then 使用端口 else 0 end),
A端口总数=sum(case when 设备类型='A' then 端口总数 else 0 end),
B使用端口=max(case when 设备类型='B' then 使用端口 else 0 end),       
B端口总数=sum(case when 设备类型='B' then 端口总数 else 0 end),       
C使用端口=max(case when 设备类型='C' then 使用端口 else 0 end),       
C端口总数=sum(case when 设备类型='C' then 端口总数 else 0 end)
FROM TB
GROUP BY IP地址
 IP地址    A使用端口       A端口总数       B使用端口       B端口总数       C使用端口       C端口总数
------- ----------- ----------- ----------- ----------- ----------- -----------
1.1.1.1 8           48          6           45          0           50
1.1.1.2 5           64          0           0           6           32(2 行受影响)