27,580
社区成员
发帖
与我相关
我的任务
分享请教一个高难度的查询！
表A如下：
id 产品编码 类别
1 400001 酒类
2 400214 酒类
5 245588 酒类
6 455468 食品类
…… …… ……
表B如下：
id 类别名称 类别 输入类型
1 生产日期 酒类 选择
2 批号 酒类 文本
7 保质期 食品类 文本
…… …… …… ……
表C如下：
id 选择项 表B的id
3 选择1 1
5 选择2 1
6 选择3 1
…… …… ……
表D如下：
id 产品编码 表B的id 属性值
5 400001 1 3
6 400001 2 10151545
7 400001 7 30
8 400214 1 5
9 400214 2 10151525
10 400214 7 30
11 245588 1 6
12 245588 2 10151545
13 245588 7 30
…… …… ……
我需要得到如下结果（类别为酒类的产品）：
产品编码 类别 生产日期 批号 保质期
400001 酒类 选择1 10151545 30
400214 酒类 选择2 10151525 30
245588 酒类 选择3 10151545 30
…… …… …… …… ……
需要注意的是，将表B下的“类别名称”的值作为字段，并且还要判断该字段的“输入类型”，如果是“选择”。那么就要从表C取值“选择项”。最终的结果如上。
id 产品编码 类别
1 400001 酒类
2 400214 酒类
5 245588 酒类
6 455468 食品类
…… …… ……
表B如下：
id 类别名称 类别 输入类型
1 生产日期 酒类 选择
2 批号 酒类 文本
7 保质期 食品类 文本
…… …… …… ……
表C如下：
id 选择项 表B的id
3 选择1 1
5 选择2 1
6 选择3 1
…… …… ……
表D如下：
id 产品编码 表B的id 属性值
5 400001 1 3
6 400001 2 10151545
7 400001 7 30
8 400214 1 5
9 400214 2 10151525
10 400214 7 30
11 245588 1 6
12 245588 2 10151545
13 245588 7 30
…… …… ……
我需要得到如下结果（类别为酒类的产品）：
产品编码 类别 生产日期 批号 保质期
400001 酒类 选择1 10151545 30
400214 酒类 选择2 10151525 30
245588 酒类 选择3 10151545 30
…… …… …… …… ……
需要注意的是，将表B下的“类别名称”的值作为字段，并且还要判断该字段的“输入类型”，如果是“选择”。那么就要从表C取值“选择项”。最终的结果如上。
...全文
请发表友善的回复…
发表回复
--小F-- 2010-11-04
- 打赏
- 举报
没有测试 不正确的地方自己改下
--小F-- 2010-11-04
- 打赏
- 举报
select
a.产品编码,b.类别,
max(case b.输入类型 when '选择' then c.选择项 else '' end) as 选择项,
max(case b.类别名称 when '生产日期' then d.属性值 else '' end) as 生产日期,
max(case b.类别名称 when '批号' then d.属性值 else '' end) as 批号,
max(case b.类别名称 when '保质期' then d.属性值 else '' end) as 保质期
from
a,b,c,d
where
a.产品编码=d.产品编码
and
b.id=c.表B的id
and
b.id=d.表B的id
group by
a.产品编码,b.类别
wzsjzjjxy 2010-11-04
- 打赏
- 举报
爱莫能助，可以看看pivot，最近学05看到的
zdqyundou 2010-11-04
- 打赏
- 举报
望各位指教~
--小F-- 2010-11-04
- 打赏
- 举报
4表连查的行转列
abuying 2010-11-04
- 打赏
- 举报
;with cte as
(select d.*,b.类别名称,b.类别,value=d.属性值 from d full join B on d.[表B的id]=b.id where b.输入类型!=N'选择'
union all
select d.*,b.类别名称,b.类别,c.选择项 from d full join B on d.[表B的id]=b.id inner join c on d.[表B的id]=c.[表B的id] and cast(d.属性值 as int)=c.id where b.输入类型=N'选择'
)
select * into #temp from cte --插入临时表
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'SELECT 产品编码,类别=min(类别)+'',''+max(类别)'
SELECT @SQL = @SQL + N' , MAX(CASE 类别名称 WHEN N''' + 类别名称 + ''' THEN ltrim([value]) ELSE '''' END) [' + 类别名称 + ']'
FROM (SELECT DISTINCT 类别名称 FROM B) AS A
SET @SQL = @SQL + N' FROM #temp GROUP BY 产品编码 order by 产品编码'
EXEC(@SQL)
/** --这种情况你也试一下
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N'SELECT 产品编码,类别 '
SELECT @SQL = @SQL + N' , MAX(CASE 类别名称 WHEN N''' + 类别名称 + ''' THEN ltrim([value]) ELSE '''' END) [' + 类别名称 + ']'
FROM (SELECT DISTINCT 类别名称 FROM B) AS A
SET @SQL = @SQL + N' FROM #temp GROUP BY 产品编码,类别 order by 产品编码,类别'
EXEC(@SQL)
**/
产品编码 类别 保质期 批号 生产日期
-------------------------------------------------- ----------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
245588 酒类,食品类 30 10151545 选择3
400001 酒类,食品类 30 10151545 选择1
400214 酒类,食品类 30 10151525 选择2
5647489 酒类,食品类 15 6858528 选择2
(4 行受影响)abuying 2010-11-04
- 打赏
- 举报
你的数据与结果不符！
食品类没有保持质属性。
测试时，已把列名 表B的id换成tb_id
食品类没有保持质属性。
测试时，已把列名 表B的id换成tb_id
;with cte as
(select d.*,b.类别名称,b.类别,value=d.属性值 from d full join B on d.[表B的id]=b.id where b.输入类型!=N'选择'
union all
select d.*,b.类别名称,b.类别,c.选择项 from d full join B on d.[表B的id]=b.id inner join c on d.[表B的id]=c.[表B的id] and cast(d.属性值 as int)=c.id where b.输入类型=N'选择'
)
select
产品编码,类别,
max(case 类别名称 when N'生产日期' then ltrim([value]) else '' end) as 生产日期,
max(case 类别名称 when N'批号' then ltrim([value]) else '' end) as 批号,
max(case 类别名称 when N'保质期' then ltrim([value]) else '' end) as 保质期
from cte where 类别=N'酒类'
group by 产品编码,类别
产品编码 类别 生产日期 批号 保质期
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
245588 酒类 选择3 10151545
400001 酒类 选择1 10151545
400214 酒类 选择2 10151525
5647489 酒类 选择2 6858528
(4 行受影响)
--小F-- 2010-11-04
- 打赏
- 举报
SORRY 测试数据貌似有问题
zdqyundou 2010-11-04
- 打赏
- 举报
以下是测试数据：
USE [master]
GO
/****** Object: Table [dbo].[C] Script Date: 11/04/2010 15:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[C]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[C](
[id] [varchar](50) NOT NULL,
[选择项] [varchar](50) NULL,
[表B的id] [varchar](50) NULL,
CONSTRAINT [PK_C] 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]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[C] ([id], [选择项], [表B的id]) VALUES (N'3', N'选择1', N'1')
INSERT [dbo].[C] ([id], [选择项], [表B的id]) VALUES (N'5', N'选择2', N'1')
INSERT [dbo].[C] ([id], [选择项], [表B的id]) VALUES (N'6', N'选择3', N'1')
/****** Object: Table [dbo].[A] Script Date: 11/04/2010 15:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[A]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[A](
[id] [varchar](50) NOT NULL,
[产品编码] [varchar](50) NULL,
[类别] [varchar](50) NULL,
CONSTRAINT [PK_A] 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]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'1', N'400001', N'酒类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'2', N'400214', N'酒类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'5', N'245588', N'酒类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'6', N'455468', N'食品类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'7', N'541654', N'酒类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'8', N'5647489', N'酒类')
INSERT [dbo].[A] ([id], [产品编码], [类别]) VALUES (N'9', N'548489', N'酒类')
/****** Object: Table [dbo].[B] Script Date: 11/04/2010 15:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[B]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[B](
[ID] [varchar](50) NOT NULL,
[类别名称] [varchar](50) NULL,
[类别] [varchar](50) NULL,
[输入类型] [varchar](50) NULL,
CONSTRAINT [PK_B] 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]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[B] ([ID], [类别名称], [类别], [输入类型]) VALUES (N'1', N'生产日期', N'酒类', N'选择')
INSERT [dbo].[B] ([ID], [类别名称], [类别], [输入类型]) VALUES (N'2', N'批号', N'酒类', N'文本')
INSERT [dbo].[B] ([ID], [类别名称], [类别], [输入类型]) VALUES (N'7', N'保质期', N'食品类', N'文本')
/****** Object: Table [dbo].[D] Script Date: 11/04/2010 15:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[D]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[D](
[id] [varchar](50) NOT NULL,
[产品编码] [varchar](50) NULL,
[表B的id] [varchar](50) NULL,
[属性值] [varchar](50) NULL,
CONSTRAINT [PK_D] 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]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'10', N'400214', N'7', N'30')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'11', N'245588', N'1', N'6')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'12', N'245588', N'2', N'10151545')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'13', N'245588', N'7', N'30')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'14', N'5647489', N'1', N'5')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'15', N'5647489', N'2', N'6858528')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'16', N'5647489', N'7', N'15')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'5', N'400001', N'1', N'3')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'6', N'400001', N'2', N'10151545')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'7', N'400001', N'7', N'30')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'8', N'400214', N'1', N'5')
INSERT [dbo].[D] ([id], [产品编码], [表B的id], [属性值]) VALUES (N'9', N'400214', N'2', N'10151525')
--小F-- 2010-11-04
- 打赏
- 举报
[Quote=引用 6 楼 zdqyundou 的回复:]
to fredrickhu
产品编码 类别 生产日期 批号 保质期
245588 酒类 选择3 6
400001 酒类 选择3 3
400214 酒类 选择3 5
这是按您的办法得到的结果~不行呀
[/Quote]
那多给点测试数据 你上面的测试数据是得不到需要的结果的
to fredrickhu
产品编码 类别 生产日期 批号 保质期
245588 酒类 选择3 6
400001 酒类 选择3 3
400214 酒类 选择3 5
这是按您的办法得到的结果~不行呀
[/Quote]
那多给点测试数据 你上面的测试数据是得不到需要的结果的
zdqyundou 2010-11-04
- 打赏
- 举报
to fredrickhu
产品编码 类别 生产日期 批号 保质期
245588 酒类 选择3 6
400001 酒类 选择3 3
400214 酒类 选择3 5
这是按您的办法得到的结果~不行呀
产品编码 类别 生产日期 批号 保质期
245588 酒类 选择3 6
400001 酒类 选择3 3
400214 酒类 选择3 5
这是按您的办法得到的结果~不行呀
