求一个sql语句！！！

2009-08-28 09:54:53 270 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

qinfujun 墨衍会员 · AI 创作全网分发关注 + 加入社区

游程理论提取灾害事件特征V2---基于MATLAB语言的编程实现游程理论，灾害事件，MATLAB 阅读详情

表
Id string bz
1 a 1
1 b 0
1 c 1
2 d 1
2 a 0
2 f 0
2 g 1
2 e 0
结果
Id bz(1) bz(0)
1 a b
c
2 d a
g f
e

...全文

超过4.2万个AOI（地理围栏），深圳市2025年2月15日更新的全类别AOI，矢量图层（全国22个超大特大城市AOI更新系列）超过4.2万个AOI（地理围栏），深圳市2025年2月15日更新的全类别AOI，矢量图层（全国22个超大特大城市AOI更新系列）阅读详情

qinfujun

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

ck40616342 2009-08-29

打赏
举报

领教了 SQL还有很多要学啊..

guguda2008 2009-08-28

打赏
举报

[Quote=引用 26 楼 herowang 的回复:]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-28 22:20:51
---------------------------------

IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (Id INT,string VARCHAR(1),bz INT)
INSERT INTO [tb]
SELECT 1,'a',1 UNION ALL
SELECT 1,'b',0 UNION ALL
SELECT 1,'c',1 UNION ALL
SELECT 2,'d',0 UNION ALL
SELECT 2,'a',0 UNION ALL
SELECT 2,'f',0 UNION ALL
SELECT 2,'g',1 UNION ALL
SELECT 2,'e',0

select K.id,[bz(1)]=K.string,
[bz(2)]=M.string
from (select row=row_number() over (partition by id order by getdate()),* from tb where bz=1) K
full join
(select row=row_number() over (partition by id order by getdate()),* from tb where bz=0) M
on K.row=M.row and K.id=M.id

[/Quote]
唉。。。。影子哥这个简单

guguda2008 2009-08-28

打赏
举报



--无耻地抄袭小三的数据

--练练手，写个2000的写法

--被LZ害惨了

--临时表是不是用多了,好像TEMP2可以不用

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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb  



SELECT 1,'a',1 UNION ALL 

SELECT 1,'b',0 UNION ALL 

SELECT 1,'c',1 UNION ALL 

SELECT 2,'d',0 UNION ALL 

SELECT 2,'a',0 UNION ALL 

SELECT 2,'f',0 UNION ALL 

SELECT 2,'g',1 UNION ALL 

SELECT 2,'e',0 

go



SELECT identity(int,1,1) 'row',* 

INTO #TEMP

FROM TB



SELECT 

ID

,CASE WHEN bz=1 THEN STRING ELSE '' END 'BZ1'

,CASE WHEN bz=0 THEN STRING ELSE '' END 'BZ2'

,[NEWID]

,BZ

INTO #TEMP2

FROM (

SELECT 

(SELECT COUNT(1) FROM #TEMP T2 WHERE T2.ID=T1.ID AND T2.ROW<=T1.ROW) 'NEWID'

,* FROM #TEMP T1) T



SELECT 

(SELECT COUNT(1) FROM #TEMP2 T3 WHERE T3.ID=T.ID AND T3.BZ=T.BZ AND T3.[NEWID]<=T.[NEWID]) 'NEWID2'

,* INTO #TEMP3 FROM #TEMP2 T

--SELECT * FROM #TEMP3

SELECT ID,MAX(BZ1),MAX(BZ2)  FROM #TEMP3

GROUP BY ID,NEWID2

ORDER BY ID ASC









DROP TABLE #TEMP

DROP TABLE #TEMP2

DROP TABLE #TEMP3

/*

1	a	b

1	c	

2	g	d

2		a

2		f

2		e

*/

feixianxxx 2009-08-28

打赏
举报

影子哥。。。
你指哪个写法？

让你望见影子的墙 2009-08-28

打赏
举报

---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-28 22:20:51
---------------------------------

IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (Id INT,string VARCHAR(1),bz INT)
INSERT INTO [tb]
SELECT 1,'a',1 UNION ALL
SELECT 1,'b',0 UNION ALL
SELECT 1,'c',1 UNION ALL
SELECT 2,'d',0 UNION ALL
SELECT 2,'a',0 UNION ALL
SELECT 2,'f',0 UNION ALL
SELECT 2,'g',1 UNION ALL
SELECT 2,'e',0

select K.id,[bz(1)]=K.string,
[bz(2)]=M.string
from (select row=row_number() over (partition by id order by getdate()),* from tb where bz=1) K
full join
(select row=row_number() over (partition by id order by getdate()),* from tb where bz=0) M
on K.row=M.row and K.id=M.id

让你望见影子的墙 2009-08-28

打赏
举报

小麦：
用的你语句测试下这个数据：
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-28 22:31:55
---------------------------------

IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (Id INT,string VARCHAR(1),bz INT)
INSERT INTO [tb]
SELECT 1,'a',1 UNION ALL
SELECT 1,'b',0 UNION ALL
SELECT 1,'c',1 UNION ALL
SELECT 2,'d',0 UNION ALL
SELECT 2,'a',0 UNION ALL
SELECT 2,'s',0 UNION ALL
SELECT 2,'f',0 UNION ALL
SELECT 2,'g',1 UNION ALL
SELECT 2,'e',0

select * from [tb]

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 23 楼 qinfujun 的回复:]
不好意思哈
[/Quote]
没事多弥补我的心灵吧。。

我刚才装VISTA装郁闷的

qinfujun 2009-08-28

打赏
举报

不好意思哈

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 19 楼 qinfujun 的回复:]
sql2000中没有ROW_NUMBER()这个函数啊！！
[/Quote]
20楼正解你

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 19 楼 qinfujun 的回复:]
sql2000中没有ROW_NUMBER()这个函数啊！！
[/Quote]

您已经让我白写了很多次

一开始结构不对
后来没说清楚是2000

F 你了

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 19 楼 qinfujun 的回复:]
sql2000中没有ROW_NUMBER()这个函数啊！！
[/Quote]



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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb SELECT 



1, 'a' ,1 UNION ALL SELECT 

1, 'b' ,0 UNION ALL SELECT 

1, 'c' ,1 UNION ALL SELECT 

2, 'd' ,1 UNION ALL SELECT 

2, 'a' ,0 UNION ALL SELECT 

2, 'f' ,0 UNION ALL SELECT 

2, 'g' ,1 UNION ALL SELECT 

2, 'e', 0 

go

select IDENTITY(int,1,1) as idd ,* into # from tb 

select 

idd=identity(int,1,1),

Id,

bz1=max(case when rn%2=1 then String else '' end ),

bz2=max(case when rn%2=0 then String else '' end )

into #2 

from(select rn=(select count(*) from # where l.id=id and IDd<=l.idd),* from # l) k

group by id,(rn-1)/2

order by Id



select 

ID=(case when idd=(select MIN(idd) from #2 where k.id=id) then rtrim(ID) else '' end),

bz1, bz2

from #2 k

/*

ID           bz1        bz2

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

1            a          b

             c          

2            d          a

             g          f

                        e

*/

qinfujun 2009-08-28

打赏
举报

sql2000中没有ROW_NUMBER()这个函数啊！！

guguda2008 2009-08-28

打赏
举报



--无耻地抄袭小三的数据

--练练手，写个2000的写法

--被LZ害惨了

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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb SELECT 



1, 'a' ,1 UNION ALL SELECT 

1, 'b' ,0 UNION ALL SELECT 

1, 'c' ,1 UNION ALL SELECT 

2, 'd' ,1 UNION ALL SELECT 

2, 'a' ,0 UNION ALL SELECT 

2, 'f' ,0 UNION ALL SELECT 

2, 'g' ,1 UNION ALL SELECT 

2, 'e', 0 

go



SELECT identity(int,1,1) 'row',* 

INTO #TEMP

FROM TB



SELECT 

ID

,MAX(CASE WHEN bz=1 THEN STRING ELSE '' END) 'BZ1'

,MAX(CASE WHEN bz=0 THEN STRING ELSE '' END) 'BZ2'

FROM (

SELECT 

(SELECT COUNT(1) FROM #TEMP T2 WHERE T2.ID=T1.ID AND T2.ROW<=T1.ROW) 'NEWID'

,* FROM #TEMP T1) T

GROUP BY ID,([NEWID]+1)/2

ORDER BY ID ASC



DROP TABLE #TEMP

/*

1	a	b

1	c	

2	d	a

2	g	f

2		e

*/

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 13 楼 qinfujun 的回复:]
t_mac 大侠还在不？
[/Quote]



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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb SELECT 



1, 'a' ,1 UNION ALL SELECT 

1, 'b' ,0 UNION ALL SELECT 

1, 'c' ,1 UNION ALL SELECT 

2, 'd' ,1 UNION ALL SELECT 

2, 'a' ,0 UNION ALL SELECT 

2, 'f' ,0 UNION ALL SELECT 

2, 'g' ,1 UNION ALL SELECT 

2, 'e', 0 

go

select 

idd=identity(int,1,1),

Id,

bz1=max(case when bz=1 then String else '' end ),

bz2=max(case when bz=0 then String else '' end )

into #1 

from(select rn=ROW_NUMBER()over(partition by id order by getdate() ),* from tb ) k

group by id,(rn-1)/2

order by Id

select 

ID=(case when idd=(select MIN(idd) from #1 where k.id=id) then rtrim(ID) else '' end),

bz1, bz2

from #1 k

/*

ID           bz1        bz2

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

1            a          b

             c          

2            d          a

             g          f

                        e

*/

feixianxxx 2009-08-28

打赏
举报

[Quote=引用 13 楼 qinfujun 的回复:]
t_mac 大侠还在不？
[/Quote]
在

--小F-- 2009-08-28

打赏
举报

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

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-08-28 22:21:09

-- Verstion:

        --Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

	--Nov 24 2008 13:01:59 

	--Copyright (c) 1988-2005 Microsoft Corporation

	--Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)



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

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([Id] int,[string] varchar(1),[bz] int)

insert [tb]

select 1,'a',1 union all

select 1,'b',0 union all

select 1,'c',1 union all

select 2,'d',1 union all

select 2,'a',0 union all

select 2,'f',0 union all

select 2,'g',1 union all

select 2,'e',0

--------------开始查询--------------------------



select 

id,



bz1=isnull((case bz when 1 then string else '' end),''),

bz2=isnull((case bz when 0 then string else '' end),'')





from [tb]

group by

id,isnull((case bz when 1 then string else '' end),''),

isnull((case bz when 0 then string else '' end),'')



----------------结果----------------------------

/* id          bz1  bz2

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

1                b

1           a    

1           c    

2                a

2                e

2                f

2           d    

2           g    



(8 行受影响)



*/

guguda2008 2009-08-28

打赏
举报

[Quote=引用 4 楼 qinfujun 的回复:]
晕死，发出来结果变形了，不好意思
结果要这样

Id bz(1) bz(0)
1 a b
c
2 d a
g f
e
就是bz= 0 的全在第三列（bz(0)），bz= 1的全在第二列bz(1)
[/Quote]
被你害死了

qinfujun 2009-08-28

打赏
举报

t_mac 大侠还在不？

guguda2008 2009-08-28

打赏
举报



--无耻地抄袭小三的数据

--练练手，写个2000的写法

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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb SELECT 



1, 'a' ,1 UNION ALL SELECT 

1, 'b' ,0 UNION ALL SELECT 

1, 'c' ,1 UNION ALL SELECT 

2, 'd' ,1 UNION ALL SELECT 

2, 'a' ,0 UNION ALL SELECT 

2, 'f' ,0 UNION ALL SELECT 

2, 'g' ,1 UNION ALL SELECT 

2, 'e', 0 

go



SELECT identity(int,1,1) 'row',* 

INTO #TEMP

FROM TB



SELECT 

ID

,MAX(CASE WHEN NEWID%2=1 THEN STRING ELSE '' END) 'BZ1'

,MAX(CASE WHEN NEWID%2=0 THEN STRING ELSE '' END) 'BZ2'

FROM (

SELECT 

(SELECT COUNT(1) FROM #TEMP T2 WHERE T2.ID=T1.ID AND T2.ROW<=T1.ROW) 'NEWID'

,* FROM #TEMP T1) T

GROUP BY ID,([NEWID]+1)/2

ORDER BY ID ASC



DROP TABLE #TEMP

/*

1	a	b

1	c	

2	d	a

2	f	g

2	e	

*/

feixianxxx 2009-08-28

打赏
举报

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

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

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

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



IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb( Id int,string varchar(10),bz int)

go

insert tb SELECT 



1, 'a' ,1 UNION ALL SELECT 

1, 'b' ,0 UNION ALL SELECT 

1, 'c' ,1 UNION ALL SELECT 

2, 'd' ,1 UNION ALL SELECT 

2, 'a' ,0 UNION ALL SELECT 

2, 'f' ,0 UNION ALL SELECT 

2, 'g' ,1 UNION ALL SELECT 

2, 'e', 0 

go

select 

Id,

bz1=max(case when bz=1 then String else '' end ),

bz2=max(case when bz=0 then String else '' end )

from(select rn=ROW_NUMBER()over(partition by id order by getdate() ),* from tb ) k

group by id,(rn-1)/2

order by Id



/*

Id          bz1        bz2

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

1           a          b

1           c          

2           d          a

2           g          f

2                      e

*/

加载更多回复（10）

求一个sql语句！！！

相关推荐