合并相邻行

wang520d 2008-03-29 12:29:22
类型 长度 小数 是否为空 描述 是否主键 字段名称
int 10 0 no 产品表主键 yes productid
varchar 50 0 no 产品名称 no isteam
int 10 0 yes 是否组合产品 no productcode
varchar 50 0 yes 产品型号 no productspecsid
varchar 50 0 yes 产品规格ID no costprice
money 8 0 yes 成本价格 no commonprice
money 8 0 yes 市场价格 no actressprice
money 8 0 yes 会员价格 no commonpriceintegal
money 8 0 yes 普通价格积分 no actresspriceintegral
money 8 0 yes 优惠价格积分 no bigunitid
int 10 0 yes 大单位ID no smallunitid
int 10 0 yes 小单位 no listid


我想合并相邻的两行
下面是我要的结果

类型 长度 小数 是否为空 描述 是否主键 字段名称 类型1 长度1 小数1 是否为空1 描述1 是否主键1 字段名称1
int 10 0 no 产品表主键 yes productid varchar 50 0 no 产品名称 no isteam

int 10 0 yes 是否组合产品 no productcode varchar 50 0 yes 产品型号 no productspecsid

varchar 50 0 yes 产品规格ID no costprice money 8 0 yes 成本价格 no commonprice

money 8 0 yes 市场价格 no actressprice money 8 0 yes 会员价格 no commonpriceintegal

money 8 0 yes 普通价格积分 no actresspriceintegralmoney 8 0 yes 优惠价格积分 no bigunitid

int 10 0 yes 大单位ID no smallunitid int 10 0 yes 小单位 no listid

int 10 0 no 产品表主键 yes productid 无 无 无 无 无 无 无
遇到奇数行的时候 用“无”字替代当前字段内容

...全文
200 17 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
wang520d 2008-03-31
  • 打赏
  • 举报
回复
今天上班看看还有没有更优的解决办法?没有的话 下班结贴
Limpire 2008-03-30
  • 打赏
  • 举报
回复
--> 临时表
if object_id('tempdb.dbo.#') is not null drop table #
select num=identity(int,1,1),* into # from datadict where tablename='T_Product_List' and colprk !='yes' order by serialid
select a.num,a.colname,colname1=isnull(b.colname,N'无') from # as a left join # as b on a.num=b.num-1 where a.num%2=1

--> 直接
select a.num,a.colname,colname1=isnull(b.colname,N'无')
from
(select num=(select count(1) from datadict where tablename='T_Product_List' and colprk<>'yes' and serialid<=a.serialid),* from datadict a where tablename='T_Product_List' and colprk<>'yes') a
left join
(select num=(select count(1) from datadict where tablename='T_Product_List' and colprk<>'yes' and serialid<=a.serialid),* from datadict a where tablename='T_Product_List' and colprk<>'yes') b
on a.num=b.num-1 where a.num%2=1

用那种方法自己权衡
Limpire 2008-03-30
  • 打赏
  • 举报
回复
我晕倒

[Quote=引用 10 楼 wang520d 的回复:]
改成这样可以了 高手看看能不能优化这个SQL
…where b.num%2=1
[/Quote]

[Quote=引用 11 楼 Limpire 的回复:]
… where b.num%2=1
[/Quote]

改为 a.num%2=1
wang520d 2008-03-29
  • 打赏
  • 举报
回复
我用的sql 2000
wang520d 2008-03-29
  • 打赏
  • 举报
回复
表的字段的数目 不要写死 最好能从系统表里面去找出这个表的当前字段数
wang520d 2008-03-29
  • 打赏
  • 举报
回复
礼拜 上的人好少啊。。
wang520d 2008-03-29
  • 打赏
  • 举报
回复
表的脚本如下:根据这个脚本找出字段 colname的值为 t_product_list 且 colpk 值不为‘yes’ 的记录,再进行行合并
遇到结果集为奇数行的时候 用“无”字替代当前字段内容


if exists (select * from sysobjects where id = OBJECT_ID('[DataDict]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [DataDict]

CREATE TABLE [DataDict] (
[SerialID] [int] IDENTITY (1, 1) NOT NULL,
[TableName] [varchar] (200) NULL,
[ColName] [varchar] (200) NULL,
[ColType] [varchar] (200) NULL,
[ColLen] [int] NULL,
[ColDec] [int] NULL,
[ColNull] [varchar] (200) NULL,
[ColPrk] [varchar] (200) NULL,
[ColNote] [varchar] (200) NULL)

ALTER TABLE [DataDict] WITH NOCHECK ADD CONSTRAINT [PK_DataDict] PRIMARY KEY NONCLUSTERED ( [SerialID] )SET IDENTITY_INSERT [DataDict] ON

INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 46,'T_COD_Dic','Title','varchar',100,0,'yes','no','标题')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 47,'T_COD_Dic','Description','varchar',200,0,'yes','no','描述')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 48,'T_COD_Dic','Status','smallint',2,0,'no','no','字典状态1启用0停用')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 49,'T_COD_Dic','Type','int',4,0,'yes','no','字典类型')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 108,'T_Product_List','ListID','int',10,0,'no','yes','产品表主键')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 109,'T_Product_List','ProductID','varchar',50,0,'yes','no','产品ID')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 110,'T_Product_List','ProductName','varchar',50,0,'no','no','产品名称')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 111,'T_Product_List','IsTeam','int',10,0,'yes','no','是否组合产品(1是,0不是)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 112,'T_Product_List','ProductCode','varchar',50,0,'yes','no','产品型号')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 113,'T_Product_List','ProductSpecsID','varchar',50,0,'yes','no','产品规格ID(与产品规格表ProductSpecs对应)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 114,'T_Product_List','CostPrice','money',8,0,'yes','no','成本价格')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 115,'T_Product_List','CommonPrice','money',8,0,'yes','no','市场价格')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 116,'T_Product_List','ActressPrice','money',8,0,'yes','no','会员价格')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 117,'T_Product_List','CommonPriceIntegal','money',8,0,'yes','no','普通价格积分')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 118,'T_Product_List','ActressPriceIntegral','money',8,0,'yes','no','优惠价格积分')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 119,'T_Product_List','BigUnitID','int',10,0,'yes','no','大单位ID(对应表ProductUnit)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 120,'T_Product_List','SmallUnitID','int',10,0,'yes','no','小单位(对应ProductUnit表)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 121,'T_Product_List','ProductPictureID','int',10,0,'yes','no','产品图片ID(对应表ProductPicture)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 122,'T_Product_List','UnitNumber','int',10,0,'yes','no','单位数量')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 123,'T_Product_List','ProductArea','varchar',50,0,'yes','no','产地')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 124,'T_Product_List','ProductSizeID','int',10,0,'yes','no','产品尺寸ID(对应表ProductSize)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 125,'T_Product_List','ProductColorID','int',10,0,'yes','no','产品颜色ID号(对应表ProductColor)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 126,'T_Product_List','ProductWeight','varchar',50,0,'yes','no','产品重量')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 127,'T_Product_List','ProductFormID','int',10,0,'yes','no','产品形态ID(对应表ProductForm)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 128,'T_Product_List','ProductApplyPeopleID','int',10,0,'yes','no','产品适用人群ID(对应表ProductApplyPeople)')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 129,'T_Product_List','AlarmNumber','int',10,0,'yes','no','报警数量')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 130,'T_Product_List','Description','text',20,0,'yes','no','产品说明')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 194,'T_Product_List','OperatingPersonnel','varchar',20,0,'yes','no','操作人')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 219,'T_Product_List','ProductSmallPic','varchar',50,0,'yes','no','小图片')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 220,'T_Product_List','ProductSourceID','int',4,0,'yes','no','产品来源')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 221,'T_Product_List','SmallTypeID','int',4,0,'yes','no','产品小类')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 222,'T_Product_List','CommendTypeID','int',4,0,'yes','no','推荐类型')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 233,'T_Product_List','StoreRoomID','int',4,0,'yes','no','仓库')
INSERT [DataDict] ([SerialID],[TableName],[ColName],[ColType],[ColLen],[ColDec],[ColNull],[ColPrk],[ColNote]) VALUES ( 889,'T_Product_List','ProductDesc','text',20,0,'yes','no','产品说明')

SET IDENTITY_INSERT [DataDict] OFF
wang520d 2008-03-29
  • 打赏
  • 举报
回复
上面SQL结果还是不对的
正确结果的SQL如下 看了真想死 求高人更优的解决方法

select * from
(
select a.num,a.colname,colname1=isnull(b.colname,N'无') from
(
select
(
select sum(1) from
(
select * from datadict where tablename='T_Product_List' and colprk !='yes'
)
as
m
where serialid<=j.serialid
)
num,*
from
(
select * from datadict where tablename='T_Product_List' and colprk !='yes'
) j
)
as
a
left join
(
select
(
select sum(1) from
(
select * from datadict where tablename='T_Product_List' and colprk !='yes'
)
as
m
where serialid<=j.serialid
)
num,*
from
(
select * from datadict where tablename='T_Product_List' and colprk !='yes'
) j
)
as
b
on
a.num=b.num-1
)
as l
where l.num%2=1
Limpire 2008-03-29
  • 打赏
  • 举报
回复
-->放临时表去吧,结果集要重复引用的(left join),用子查询肯定慢
select num=identity(int,1,1),lower(colname) as colname,lower(coltype) as coltype into # from datadict h where tablename='T_Product_List' and colprk !='yes'
select a.num,a.colname,colname1=isnull(b.colname,N'无') from # as a left join # as b on a.num=b.num-1 where b.num%2=1
wang520d 2008-03-29
  • 打赏
  • 举报
回复
改成这样可以了 高手看看能不能优化这个SQL
select a.num,a.colname,colname1=isnull(b.colname,N'无') from
(
select (select sum(1) from datadict where serialid<=h.serialid) num,lower(colname) as colname,lower(coltype) as coltype
from datadict h where tablename='T_Product_List' and colprk !='yes'
)
as
a
left join
(
select (select sum(1) from datadict where serialid<=h.serialid) num,lower(colname) as colname,lower(coltype) as coltype
from datadict h where tablename='T_Product_List' and colprk !='yes'
)
as
b
on
a.num=b.num-1 where b.num%2=1
wang520d 2008-03-29
  • 打赏
  • 举报
回复
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'a' 无效。

下面的SQL报上面的错误 怎么修改高人指点下。
select a.num,a.colname,colname1=isnull(b.colname,N'无') from
(
select (select sum(1) from datadict where serialid<=h.serialid) num,lower(colname) as colname,lower(coltype) as coltype
from datadict h where tablename='T_Product_List' and colprk !='yes'
)
as
a
left join a b
on
a.num=b.num-1 where b.num%2=1
Limpire 2008-03-29
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 wang520d 的回复:]
我先给原来的表结果中生成一个 自增列每次查询的结果中都会生成这样一个列 应该能构造出奇偶数来吧。。然后利用这个奇偶数就能用上面那个朋友的那中方法了
[/Quote]

是的

select id=identity(int,1,1),* into # from ...
wang520d 2008-03-29
  • 打赏
  • 举报
回复
我先给原来的表结果中生成一个 自增列每次查询的结果中都会生成这样一个列 应该能构造出奇偶数来吧。。然后利用这个奇偶数就能用上面那个朋友的那中方法了
-晴天 2008-03-29
  • 打赏
  • 举报
回复
在SQL Server中,没有奇数行偶数行的概念,行与行之间是没有关系的,除非你以某个关键字进行排序.

因此,如果你没有给出排序条件,那最简单的办法是:以任意列进行排序,获取 top 50% 的行为原来列表的内容,再逆向排序获得另 50% 的内容作为新列,这样就可以得到新表了.当然,还要注意总行数为奇偶数和 with ties 两个问题.
wang520d 2008-03-29
  • 打赏
  • 举报
回复
如果按照你上面的方法 我现在的问题是 要把查询结果生成一个identity列
wang520d 2008-03-29
  • 打赏
  • 举报
回复
哈哈 谢谢
Limpire 2008-03-29
  • 打赏
  • 举报
回复
--> 困了,具体就不写了,先写个简单的例子:
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int identity, data nchar(1))
insert # select 'a'
insert # select 'b'
insert # select 'c'
insert # select 'd'
insert # select 'e'

select a.id, a.data, data1=isnull(b.data, N'无') from # a left join # b on a.id=b.id-1 where a.id%2=1

/*
id data data1
----------- ---- -----
1 a b
3 c d
5 e 无
*/

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧