Rumah > pangkalan data > tutorial mysql > SQL行列转换

SQL行列转换

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-06-07 17:44:22
asal
929 orang telah melayarinya

以三个站点三个参数为例 1、以参数当列时 select zad_time,zad_sid,zad_data1,zad_data2,zad_data3,zad_data4 from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_sid in(000000,000001,000002) and zad_time='20130416' 2、以参数当行时 select * f

以三个站点三个参数为例

1、以参数当列时   

select zad_time,zad_sid,zad_data1,zad_data2,zad_data3,zad_data4    from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ]    where zad_sid in(000000,000001,000002) and zad_time='20130416'

2、以参数当行时   

select * from ( select zad_time,zad_sid,参数='SO2', 浓度值 = zad_data1 from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' and zad_sid in('000000','000001','000002') union all select zad_time,zad_sid,参数='NO2', 浓度值 = zad_data2 from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' and zad_sid in('000000','000001','000002') union all select zad_time,zad_sid,参数='PM10', 浓度值 = zad_data3 from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' and zad_sid in('000000','000001','000002') ) t order by zad_time

 

    以站点当列时

select zad_time,参数,供销商城,ssss,dddd  from ( select zad_time,参数='SO2',sum(case zad_sid when '000000' then zad_data1 end)  as '供销商城',sum(case zad_sid when '000001' then zad_data1 end)  as 'ssss', sum(case zad_sid when '000002' then zad_data1 end)  as 'dddd' from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' group by zad_time union all select zad_time,参数='NO2',sum(case zad_sid when '000000' then zad_data2 end)  as '供销商城',sum(case zad_sid when '000001' then zad_data2 end)  as 'ssss', sum(case zad_sid when '000002' then zad_data2 end)  as 'dddd' from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' group by zad_time union all select zad_time,参数='PM10',sum(case zad_sid when '000000' then zad_data3 end)  as '供销商城',sum(case zad_sid when '000001' then zad_data3 end)  as 'ssss', sum(case zad_sid when '000002' then zad_data3 end)  as 'dddd' from [XHAQMMS_CDB].[dbo].[XHZ_T_DAYREP_AQI_XZ] where zad_time='20130416' group by zad_time ) t order by zad_time

 

 

 

,网站空间,香港服务器,虚拟主机
Label berkaitan:
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan