请问一个查询，有点难度哦！（附脚本）

2012-12-14

请教一个查询，有点难度哦！（附脚本）脚本如下：USE [AdventureWorks]GO/****** Object:Table [dbo].[Table_1]

请教一个查询，有点难度哦！（附脚本）
脚本如下：


USE [AdventureWorks]
GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 2012/11/3 3:13:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_1](
  [id] [bigint] IDENTITY(1,1) NOT NULL,
  [类别名称] [varchar](50) NULL,
  [方式] [varchar](50) NULL,
  [名称] [varchar](50) NULL,
  [值] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [dbo].[Table_2]    Script Date: 2012/11/3 3:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_2](
  [id] [bigint] IDENTITY(1,1) NOT NULL,
  [Table_1_ID] [bigint] NULL,
  [Property] [varchar](50) NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [dbo].[Table_3]    Script Date: 2012/11/3 3:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_3](
  [id] [bigint] IDENTITY(1,1) NOT NULL,
  [产品名称] [varchar](50) NULL,
  [类别] [varchar](50) NULL,
 CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON 

INSERT [dbo].[Table_1] ([id], [类别名称], [方式], [名称], [值]) VALUES (1, N'食品', N'文本', N'生产日期', N'2012-10-01')
INSERT [dbo].[Table_1] ([id], [类别名称], [方式], [名称], [值]) VALUES (2, N'食品', N'选择', N'状态', N'1')
INSERT [dbo].[Table_1] ([id], [类别名称], [方式], [名称], [值]) VALUES (3, N'食品', N'文本', N'保质期', N'90')
INSERT [dbo].[Table_1] ([id], [类别名称], [方式], [名称], [值]) VALUES (4, N'电器', N'选择', N'状态', N'3')
INSERT [dbo].[Table_1] ([id], [类别名称], [方式], [名称], [值]) VALUES (5, N'电器', N'文本', N'出厂日期', N'2012-09-05')
SET IDENTITY_INSERT [dbo].[Table_1] OFF
SET IDENTITY_INSERT [dbo].[Table_2] ON  
 

INSERT [dbo].[Table_2] ([id], [Table_1_ID], [Property]) VALUES (1, 2, N'正常')
INSERT [dbo].[Table_2] ([id], [Table_1_ID], [Property]) VALUES (2, 2, N'异常')
INSERT [dbo].[Table_2] ([id], [Table_1_ID], [Property]) VALUES (3, 4, N'损坏')
INSERT [dbo].[Table_2] ([id], [Table_1_ID], [Property]) VALUES (4, 4, N'进水')
SET IDENTITY_INSERT [dbo].[Table_2] OFF
SET IDENTITY_INSERT [dbo].[Table_3] ON 

INSERT [dbo].[Table_3] ([id], [产品名称], [类别]) VALUES (1, N'喔喔奶糖', N'食品')
INSERT [dbo].[Table_3] ([id], [产品名称], [类别]) VALUES (2, N'喜之郎果冻', N'食品')
INSERT [dbo].[Table_3] ([id], [产品名称], [类别]) VALUES (3, N'新飞冰箱', N'电器')
INSERT [dbo].[Table_3] ([id], [产品名称], [类别]) VALUES (4, N'耐克运动鞋', N'')
SET IDENTITY_INSERT [dbo].[Table_3] OFF

需要生成如下的数据：
产品名称      类别      属性
喔喔奶糖      食品      生产日期:2012-10-01,状态:正常,保质期:90
喜之郎果冻    食品       生产日期:2012-10-01,状态:正常,保质期:90
新飞冰箱      电器      状态:损坏,出厂日期:2012-09-05
耐克运动鞋    无         无

难点主要是在【属性】字段的值，涉及到行转列及值的转换。例如：第一行数据，先取出[Table_1]的【名称】字段为“生产日期”，然后加上冒号，再取出[Table_1]的【值】字段的值。当[Table_1]的[方式]字段值为“选择”时，则需要从[Table_2]取出相应的值，这样组合起来。

[最优解释]



select 
x.产品名称,
case when x.类别 is null or ltrim(rtrim(x.类别)) = '' then '无' else x.类别 end as 类别,
isnull(stuff(y.属性,len(y.属性),1,''),'无') as 属性
from table_3 x left join
(
select 
distinct a.类别名称,
( 
select 
b.名称 + ':' + 
case when b.方式 = '选择' then ( select c.property from table_2 c where c.id = b.值 AND c.table_1_id = b.id ) else b.值  end,
',' 
from table_1 b where b.类别名称 = a.类别名称  FOR XML PATH('') 
) as 属性
from table_1 a
) y on x.类别 = y.类别名称





产品名称    类别    属性
----------  ------- ---------------------------------------
喔喔奶糖食品生产日期:2012-10-01,状态:正常,保质期:90
喜之郎果冻食品生产日期:2012-10-01,状态:正常,保质期:90
新飞冰箱电器状态:损坏,出厂日期:2012-09-05
耐克运动鞋无无


(4 行受影响)

[其他解释]

;WITH c1 as
(SELECT
        t1.id
        ,t1.类别名称
        ,t1.方式
        ,t1.名称
        ,t2.Property
                FROM table_1 t1
                INNER JOIN table_2 t2 ON t1.值 = t2.id

        WHERE 方式 = '选择')
,t1 as
(
        SELECT table_3.id,
                   table_3.产品名称,
                   table_3.类别,
                   table_1.名称 + ':' + table_1.值属性
          FROM table_3
          LEFT JOIN table_1 on table_1.类别名称 = table_3.类别
        WHERE table_1.id not in ( select id from c1 )
        UNION ALL
                select table_3.id,
                           table_3.产品名称,
                           table_3.类别,
                           c1.名称 + ':' + c1.Property 属性
                        from table_3
                        left join c1 on c1.类别名称 = table_3.类别
)
SELECT DISTINCT
        CAST(产品名称 AS VARCHAR(8)) 产品名称
        ,CASE LTRIM(类别) WHEN '' THEN NULL ELSE 类别 END 类别
        ,STUFF( (SELECT ';'+LTRIM(属性)
                                FROM t1
                                WHERE 类别 = d.类别 and 产品名称 = d.产品名称 FOR XML PATH('')),1,1,'' ) 组合属性
from t1 d
order by 产品名称

测试结果：
产品名称     类别        组合属性
-------- --------------- ------------------------------------
耐克运动     NULL        NULL
喔喔奶糖     食品        生产日期:2012-10-01;保质期:90;状态:正常
喜之郎果     食品        生产日期:2012-10-01;保质期:90;状态:正常
新飞冰箱     电器        出厂日期:2012-09-05;状态:损坏

(4 行受影响)

这段查询执行出来有多慢，查询时间给出来看看……

[其他解释]
to kensouterry1
目前看来您的方法也是可行的。但是在大数据量上WITH有点慢。不过我还没拿到正式数据上去试，我在改程序，改好去试试。

[其他解释]
该回复于2012-11-05 14:31:52被管理员删除
[其他解释]
该回复于2012-11-05 14:31:52被管理员删除
[其他解释]

我只是想了解那个Not In 运算导致的性能下降有多大，了解之后再进行改进即可，我一直认为平行查询性能不应该比子查询低很多，呵呵……
[其他解释]
with 的话，几万数据性能也不会很差，我试过，不过not in的话，由于大部分情况下会造成表扫描，所以如果可以，就少用。不过由于with的临时集合不能创建索引，所以在非常大的数据量，如百万甚至千万的时候，速度会明显下降，这个时候可以考虑使用具有索引的临时表来替代，表变量只能创建主键约束，所以这个也要按照数据量来评估是否有必要使用表变量。

热点排行

SQL Server

请问一个查询，有点难度哦！（附脚本）