sql 横纵列问题

2014-08-01 03:31:23 168 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于51单片机的多功能智能路灯控制系统（仿真+代码+原理图）智能路灯控制系统主要由51单片机最小系统+1602显示模块+DS1302时钟模块+光强检测模块+按键输入模块+声光报警模块+LED照明模块+状态检测电路+人体车辆红外检测模块组成。能实现以下功能： 1.能够按键设定系统时间、工作时间、光照阈值 2.工作时间内路灯点亮，凌晨12点后路灯关闭，有人或车通过是，路灯点亮10s 3.非工作时间光照强度低于阈值，路灯点亮 4.能够检测系统工作状态，路灯应亮未亮，声光报警 5.具备手动控制，能够切换自动/手动控制功能阅读详情

itemname checkvalue
左眼视力 1.0
右眼视力 1.5
左眼矫正视力 1.5
右眼矫正视力 1.5

结果(都是左眼/右眼打头,多个项目,不固定,需要动态)

视力矫正视力
左眼 1.0 1.5
右眼 1.0 1.5

...全文

置换矩阵、转置矩阵以及向量空间、子空间置换矩阵、转置矩阵以及向量空间、子空间导读置换矩阵导读 MIT矩阵分析课程之后的的二篇笔记。阅读详情

netajax1

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

netajax1 2014-08-01

打赏
举报

引用 8 楼 DBA_Huangzj 的回复:

开会去

----------------------------------------------------------------
-- Author  :DBA_tbZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[tb]
DROP TABLE TB
go
create table tb(
DataItemName nvarchar(20),
CheckValue char(10)
)
insert into tb(DataItemName,CheckValue)values('左眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('右眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('左眼OD球面镜','-4.50')
insert into tb(DataItemName,CheckValue)values('右眼OD球面镜','-5.50')
insert into tb(DataItemName,CheckValue)values('左眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('右眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('左眼OD轴位','5.00')
insert into tb(DataItemName,CheckValue)values('右眼OD轴位','180.00')
insert into tb(DataItemName,CheckValue)values('左眼OD最佳矫正视力','0.90')
insert into tb(DataItemName,CheckValue)values('右眼OD最佳矫正视力','0.90-1')

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([DataItemName],3,LEN([DataItemName])))+'=max(case when substring([DataItemName],3,LEN([DataItemName]))='+quotename(substring([DataItemName],3,LEN([DataItemName])),'''')+' then [checkvalue] else null end)'
from [tb] group by substring([DataItemName],3,LEN([DataItemName]))
exec('select substring([DataItemName],1,2) as 视力'+@s+' from [tb] group by substring([DataItemName],1,2)')


----------------结果----------------------------
/* 
视力   OD裸眼视力     OD球面镜      OD轴位       OD柱镜       OD最佳矫正视力
---- ---------- ---------- ---------- ---------- ----------
右眼   0.10       -5.50      180.00     -1.50      0.90-1    
左眼   0.10       -4.50      5.00       -1.50      0.90      

*/

多谢大神

發糞塗牆 2014-08-01

打赏
举报

开会去

----------------------------------------------------------------
-- Author  :DBA_tbZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[tb]
DROP TABLE TB
go
create table tb(
DataItemName nvarchar(20),
CheckValue char(10)
)
insert into tb(DataItemName,CheckValue)values('左眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('右眼OD裸眼视力','0.10')
insert into tb(DataItemName,CheckValue)values('左眼OD球面镜','-4.50')
insert into tb(DataItemName,CheckValue)values('右眼OD球面镜','-5.50')
insert into tb(DataItemName,CheckValue)values('左眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('右眼OD柱镜','-1.50')
insert into tb(DataItemName,CheckValue)values('左眼OD轴位','5.00')
insert into tb(DataItemName,CheckValue)values('右眼OD轴位','180.00')
insert into tb(DataItemName,CheckValue)values('左眼OD最佳矫正视力','0.90')
insert into tb(DataItemName,CheckValue)values('右眼OD最佳矫正视力','0.90-1')

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([DataItemName],3,LEN([DataItemName])))+'=max(case when substring([DataItemName],3,LEN([DataItemName]))='+quotename(substring([DataItemName],3,LEN([DataItemName])),'''')+' then [checkvalue] else null end)'
from [tb] group by substring([DataItemName],3,LEN([DataItemName]))
exec('select substring([DataItemName],1,2) as 视力'+@s+' from [tb] group by substring([DataItemName],1,2)')


----------------结果----------------------------
/* 
视力   OD裸眼视力     OD球面镜      OD轴位       OD柱镜       OD最佳矫正视力
---- ---------- ---------- ---------- ---------- ----------
右眼   0.10       -5.50      180.00     -1.50      0.90-1    
左眼   0.10       -4.50      5.00       -1.50      0.90      

*/

netajax1 2014-08-01

打赏
举报

引用 6 楼 DBA_Huangzj 的回复:

给点“有问题”的数据吧。

create table tb( DataItemName nvarchar(20), CheckValue char(10) ) insert into tb(DataItemName,CheckValue)values('左眼OD裸眼视力','0.10') insert into tb(DataItemName,CheckValue)values('右眼OD裸眼视力','0.10') insert into tb(DataItemName,CheckValue)values('左眼OD球面镜','-4.50') insert into tb(DataItemName,CheckValue)values('右眼OD球面镜','-5.50') insert into tb(DataItemName,CheckValue)values('左眼OD柱镜','-1.50') insert into tb(DataItemName,CheckValue)values('右眼OD柱镜','-1.50') insert into tb(DataItemName,CheckValue)values('左眼OD轴位','5.00') insert into tb(DataItemName,CheckValue)values('右眼OD轴位','180.00') insert into tb(DataItemName,CheckValue)values('左眼OD最佳矫正视力','0.90') insert into tb(DataItemName,CheckValue)values('右眼OD最佳矫正视力','0.90-1')

發糞塗牆 2014-08-01

打赏
举报

给点“有问题”的数据吧。

netajax1 2014-08-01

打赏
举报

而且checkvalue是字符类型

發糞塗牆 2014-08-01

打赏
举报

嗯，把你的数据列全一点看看

引用 3 楼 netajax1 的回复:

[quote=引用 1 楼 DBA_Huangzj 的回复:]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([itemname] varchar(12),[checkvalue] numeric(2,1))
insert [huang]
select '左眼视力',1.0 union all
select '右眼视力',1.5 union all
select '左眼矫正视力',1.5 union all
select '右眼矫正视力',1.5


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([itemname],3,LEN([itemname])))+'=max(case when substring([itemname],3,LEN([itemname]))='+quotename(substring([itemname],3,LEN([itemname])),'''')+' then [checkvalue] else 0 end)'
from [huang] group by substring([itemname],3,LEN([itemname]))
exec('select substring([itemname],1,2) as 视力'+@s+' from [huang] group by substring([itemname],1,2)')


----------------结果----------------------------
/* 
视力   矫正视力                                    视力
---- --------------------------------------- ---------------------------------------
右眼   1.5                                     1.5
左眼   1.5                                     1.0

*/

加入多一个左眼XX 右眼XX,那左眼右眼视力是不是也把左右眼XX值max进去了？[/quote]

netajax1 2014-08-01

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([itemname] varchar(12),[checkvalue] numeric(2,1))
insert [huang]
select '左眼视力',1.0 union all
select '右眼视力',1.5 union all
select '左眼矫正视力',1.5 union all
select '右眼矫正视力',1.5


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([itemname],3,LEN([itemname])))+'=max(case when substring([itemname],3,LEN([itemname]))='+quotename(substring([itemname],3,LEN([itemname])),'''')+' then [checkvalue] else 0 end)'
from [huang] group by substring([itemname],3,LEN([itemname]))
exec('select substring([itemname],1,2) as 视力'+@s+' from [huang] group by substring([itemname],1,2)')


----------------结果----------------------------
/* 
视力   矫正视力                                    视力
---- --------------------------------------- ---------------------------------------
右眼   1.5                                     1.5
左眼   1.5                                     1.0

*/

加入多一个左眼XX 右眼XX,那左眼右眼视力是不是也把左右眼XX值max进去了？

netajax1 2014-08-01

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([itemname] varchar(12),[checkvalue] numeric(2,1))
insert [huang]
select '左眼视力',1.0 union all
select '右眼视力',1.5 union all
select '左眼矫正视力',1.5 union all
select '右眼矫正视力',1.5


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([itemname],3,LEN([itemname])))+'=max(case when substring([itemname],3,LEN([itemname]))='+quotename(substring([itemname],3,LEN([itemname])),'''')+' then [checkvalue] else 0 end)'
from [huang] group by substring([itemname],3,LEN([itemname]))
exec('select substring([itemname],1,2) as 视力'+@s+' from [huang] group by substring([itemname],1,2)')


----------------结果----------------------------
/* 
视力   矫正视力                                    视力
---- --------------------------------------- ---------------------------------------
右眼   1.5                                     1.5
左眼   1.5                                     1.0

*/

好像不对哦，我其他项查出来为0

發糞塗牆 2014-08-01

打赏
举报

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-08-01 15:32:54
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([itemname] varchar(12),[checkvalue] numeric(2,1))
insert [huang]
select '左眼视力',1.0 union all
select '右眼视力',1.5 union all
select '左眼矫正视力',1.5 union all
select '右眼矫正视力',1.5


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(substring([itemname],3,LEN([itemname])))+'=max(case when substring([itemname],3,LEN([itemname]))='+quotename(substring([itemname],3,LEN([itemname])),'''')+' then [checkvalue] else 0 end)'
from [huang] group by substring([itemname],3,LEN([itemname]))
exec('select substring([itemname],1,2) as 视力'+@s+' from [huang] group by substring([itemname],1,2)')


----------------结果----------------------------
/* 
视力   矫正视力                                    视力
---- --------------------------------------- ---------------------------------------
右眼   1.5                                     1.5
左眼   1.5                                     1.0

*/

sql 横纵列问题

相关推荐