急求SQL语句高手就进来

AuC 2009-08-21 03:15:01

有一张表 A 它有ID,W1,W2,W3,W4,W5,NAME 这些列，现在要通过一条SQL语句把它查询出来

ID,NAME ,W
1 a W1
1 a W2
1 a W3
1 a W4
1 a W5

...全文

201 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

AuC 2009-08-23

打赏
举报

实际问题如下期待高手能帮忙解决谢谢

生成表的具体可参照 http://www.shbsty.com/shbsswj/node6/node33/userobject1ai690.html





use master

go



/*

表里的wt1-wt12列是指每小时中 间隔 5分钟的水位， 里面的数据不用管

然后要跟据这张表里的数据，生成水位年报表（如截图）  里面的数据是指每天8时整的水位



还有月报表（上面是1-31号，竖排是0点-23点），日报表（上面是0点-23点，竖排是每小时 间隔5分钟时的水位）

*/

create table test(

id int,--站名id

ymdhm datetime, --年月日时分秒

wt1 float,

wt2 float,

wt3 float,

wt4 float,

wt5 float,

wt6 float,

wt7 float,

wt8 float,

wt9 float,

wt10 float,

wt11 float,

wt12 float

)



insert into test values(1,'2009-8-21 00:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 01:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 02:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 03:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 04:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 05:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 06:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 07:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 08:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 09:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 10:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 11:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 12:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 13:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 14:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 15:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 16:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 17:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 18:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 19:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 20:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 21:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 22:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-21 23:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 00:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 01:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 02:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 03:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 04:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 05:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 06:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 07:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 08:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 09:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 10:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 11:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 12:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 13:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 14:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 15:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 16:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 17:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 18:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 19:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 20:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 21:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 22:00:00',21,22,21,21,21,21,21,21,21,21,21,21)

insert into test values(1,'2009-8-22 23:00:00',21,22,21,21,21,21,21,21,21,21,21,21)



SELECT * from test



drop table test

zhangjiang264 2009-08-21

打赏
举报

学习了

鹏城大唐 2009-08-21

打赏
举报

这个需求比较怪异

feixianxxx 2009-08-21

打赏
举报

-- =========================================

-- -----------t_mac 小编-------------------

   --------------------希望有天成为大虾---- 

-- =========================================



IF OBJECT_ID('a') IS NOT NULL

DROP TABLE a

GO

CREATE TABLE a(ID  int,W1 int ,W2 int ,W3 int ,W4 int ,W5 int ,NAME varchar(10))

go

insert a SELECT 1,1,2,3,4,5,'a'

go

declare @s datetime 

set @s=GETDATE()

 select id,name,w1 as w,convert(varchar(17),@s,120)+'00' as 时间 from a union all 

select id,name,W2 as w ,convert(varchar(17),dateadd(minute,12,@s),120 )+'00'from a union all 

select id,name,w3 as w ,convert(varchar(17),dateadd(minute,24,@s),120 )+'00'from a union all 

select id,name,w4  as w ,convert(varchar(17),dateadd(minute,36,@s),120 )+'00'from a union all 

select id,name,w5 as w,convert(varchar(17),dateadd(minute,48,@s),120 )+'00'from a  

order by id

go



go

/*

id          name       w           时间

----------- ---------- ----------- -------------------

1           a          1           2009-08-21 15:33:00

1           a          2           2009-08-21 15:45:00

1           a          3           2009-08-21 15:57:00

1           a          4           2009-08-21 16:09:00

1           a          5           2009-08-21 16:21:00





*/

feixianxxx 2009-08-21

打赏
举报

-- =========================================

-- -----------t_mac 小编-------------------

   --------------------希望有天成为大虾---- 

-- =========================================



IF OBJECT_ID('a') IS NOT NULL

DROP TABLE a

GO

CREATE TABLE a(ID  int,W1 int ,W2 int ,W3 int ,W4 int ,W5 int ,NAME varchar(10))

go

insert a SELECT 1,1,2,3,4,5,'a'

go

declare @s datetime 

set @s=GETDATE()

 select id,name,w1 as w,convert(varchar(19),@s,120) as 时间 from a union all 

select id,name,W2 as w ,convert(varchar(19),dateadd(minute,12,@s),120 )from a union all 

select id,name,w3 as w ,convert(varchar(19),dateadd(minute,24,@s),120 )from a union all 

select id,name,w4  as w ,convert(varchar(19),dateadd(minute,36,@s),120 )from a union all 

select id,name,w5 as w,convert(varchar(19),dateadd(minute,48,@s),120 )from a  

order by id

go



go

/*

id          name       w           时间

----------- ---------- ----------- -------------------

1           a          1           2009-08-21 15:32:08

1           a          2           2009-08-21 15:44:08

1           a          3           2009-08-21 15:56:08

1           a          4           2009-08-21 16:08:08

1           a          5           2009-08-21 16:20:08



*/

guguda2008 2009-08-21

打赏
举报



IF OBJECT_ID('A') IS NOT NULL DROP TABLE A

GO

CREATE TABLE A(

ID INT,

W1 INT,

W2 INT,

W3 INT,

W4 INT,

W5 INT,

[NAME] VARCHAR(20)

)

SELECT 1 'ID',OBJ.[NAME]

,COL.[NAME] 'W',DATEADD(MI,12*(ROW_NUMBER() OVER (ORDER BY COL.[NAME] ASC)-1),'2009-08-20 14:00:00') 

FROM SYSCOLUMNS COL

INNER JOIN SYSOBJECTS OBJ ON COL.ID=OBJ.ID AND OBJ.[NAME]='A' AND COL.[NAME] LIKE 'W%'

/*

1	A	W1	2009-08-20 14:00:00.000

1	A	W2	2009-08-20 14:12:00.000

1	A	W3	2009-08-20 14:24:00.000

1	A	W4	2009-08-20 14:36:00.000

1	A	W5	2009-08-20 14:48:00.000

*/

--小F-- 2009-08-21

打赏
举报

--小麦的数据

IF OBJECT_ID('a') IS NOT NULL

DROP TABLE a

GO

CREATE TABLE a(ID  int,W1 int ,W2 int ,W3 int ,W4 int ,W5 int ,NAME varchar(10))

go

insert a SELECT 1,1,2,3,4,5,'a'

insert a SELECT 2,3,2,7,4,9,'b'

go

 select id,name,w1 as w from a union all 

select id,name,W2 as w from a union all 

select id,name,w3 as w from a union all 

select id,name,w4  as w from a union all 

select id,name,w5 as w from a  

order by id

go



declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select ID , [name] = ' + quotename(Name , '''') + ' , [W] = ' + quotename(Name) + ' from a'

from syscolumns 

where name! = N'ID' and ID = object_id('a') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by ID ')

/*



(1 行受影响)

id          name       w

----------- ---------- -----------

1           a          1

1           a          2

1           a          3

1           a          4

1           a          5

2           b          9

2           b          4

2           b          7

2           b          2

2           b          3



(10 行受影响)*/

guguda2008 2009-08-21

打赏
举报



IF OBJECT_ID('A') IS NOT NULL DROP TABLE A

GO

CREATE TABLE A(

ID INT,

W1 INT,

W2 INT,

W3 INT,

W4 INT,

W5 INT,

[NAME] VARCHAR(20)

)

SELECT 1 'ID',OBJ.[NAME],COL.[NAME] 'W' FROM SYSCOLUMNS COL

INNER JOIN SYSOBJECTS OBJ ON COL.ID=OBJ.ID AND OBJ.[NAME]='A' AND COL.[NAME] LIKE 'W%'

/*

1	A	W1

1	A	W2

1	A	W3

1	A	W4

1	A	W5

*/

SQL77 2009-08-21

打赏
举报

[Quote=引用 8 楼 zyaccp30 的回复:]
还有就是时间列时间要变加12分钟
比如说第一条是 2009-08-20 14：00：00
那么生成后面的就应分别是

2009-08-20 14：00：00
2009-08-20 14：12：00
2009-08-20 14：24：00
2009-08-20 14：36：00
2009-08-20 14：48：00

[/Quote]
这个好说,用时间函数处理一下
CONVERT(VARCHAR(10),TIME,120)
DATENAME(HH,TIME)+:
DATENAME(MI,TIME)+:
DATENAME(SS,TIME)

--小F-- 2009-08-21

打赏
举报

.....速度太快了

Yang_ 2009-08-21

打赏
举报

select id,name,w1 as w from a union all 

select id,name,w2 as w from a union all 

select id,name,w3 as w from a union all 

select id,name,w4 as w from a union all 

select id,name,w5 as w from a

SQL77 2009-08-21

打赏
举报

[Quote=引用 2 楼 jinjazz 的回复:]
select id,name,'w1'as w from a union all
select id,name,'w2'as w from a union all
select id,name,'w3'as w from a union all
select id,name,'w4'as w from a union all
select id,name,'w5'as w from a
[/Quote]
就是这个了

AuC 2009-08-21

打赏
举报

还有就是时间列时间要变加12分钟
比如说第一条是 2009-08-20 14：00：00
那么生成后面的就应分别是

2009-08-20 14：00：00
2009-08-20 14：12：00
2009-08-20 14：24：00
2009-08-20 14：36：00
2009-08-20 14：48：00

feixianxxx 2009-08-21

打赏
举报

-- =========================================

-- -----------t_mac 小编-------------------

   --------------------希望有天成为大虾---- 

-- =========================================



IF OBJECT_ID('a') IS NOT NULL

DROP TABLE a

GO

CREATE TABLE a(ID  int,W1 int ,W2 int ,W3 int ,W4 int ,W5 int ,NAME varchar(10))

go

insert a SELECT 1,1,2,3,4,5,'a'

insert a SELECT 2,3,2,7,4,9,'b'

go

 select id,name,w1 as w from a union all 

select id,name,W2 as w from a union all 

select id,name,w3 as w from a union all 

select id,name,w4  as w from a union all 

select id,name,w5 as w from a  

order by id

go

/*

(1 行受影响)

id          name       w

----------- ---------- -----------

1           a          1

1           a          2

1           a          3

1           a          4

1           a          5

2           b          9

2           b          4

2           b          7

2           b          2

2           b          3



*/

lihan6415151528 2009-08-21

打赏
举报



select id,name,'w1'as w from a union all 

select id,name,'w2'as w from a union all 

select id,name,'w3'as w from a union all 

select id,name,'w4'as w from a union all 

select id,name,'w5'as w from a

feixianxxx 2009-08-21

打赏
举报

-- =========================================

-- -----------t_mac 小编-------------------

   --------------------希望有天成为大虾---- 

-- =========================================



IF OBJECT_ID('a') IS NOT NULL

DROP TABLE a

GO

CREATE TABLE a(ID  int,W1 int ,W2 int ,W3 int ,W4 int ,W5 int ,NAME varchar(10))

go

insert a SELECT 1,1,2,3,4,5,'a'

go

 select id,name,w1 as w from a union all 

select id,name,W2 as w from a union all 

select id,name,w3 as w from a union all 

select id,name,w4  as w from a union all 

select id,name,w5 as w from a  



go



id          name       w

----------- ---------- -----------

1           a          1

1           a          2

1           a          3

1           a          4

1           a          5

guguda2008 2009-08-21

打赏
举报

从SYSCOLUMNS里取行了

zhengduan964532 2009-08-21

打赏
举报

不明白？啥意思

jinjazz 2009-08-21

打赏
举报

select id,name,'w1'as w from a union all
select id,name,'w2'as w from a union all
select id,name,'w3'as w from a union all
select id,name,'w4'as w from a union all
select id,name,'w5'as w from a

SQL77 2009-08-21