现有表store ,表中数据如下图所示:
如何通过sql语句 查出下面这种结果:
字段:id cname(药店) product_name(药品名) specification1(药品规格1) specification2(药品规格2)
请问应该如何写,求大神赐教,感激不尽……
创建数据库代码
CREATE TABLE [dbo].[store](
[id] [int] NOT NULL,
[cname] [varchar](50) NULL,
[product_name] [varchar](50) NULL,
[specification1] [varchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (1, N'a药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'b药店', N'阿莫西林', N'6片装')
楼主这个需求我可以改一改,如果一个药品规格有N种的时候,你得到关于规格的列数就是N,N不确定,程序不好写。
我觉得从图一中得到的数据,做个遍历,就可以把规格合并成:
['a店'=>[
'药品1'=>[
'规格1',
'规格2',
.......
'规格N'
]
]
'药品2'=>[....],
.......
'药品N'=>[....]
先看个效果:
;with a as(
select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn from store
)
select cname,product_name,specification规格1,specification规格2 from a pivot(max(a.specification1) for rn in (specification规格1,specification规格2)) b
cname product_name specification规格1 specification规格2
a药店 阿莫西林 16片装 6片装
b药店 阿莫西林 6片装 NULL
假设我理解的没错,你的数据可能还有其他药品,且规格不一定是两个,因此就要动态生成上面的语句:
比如上面的结果语句可以如下面生成。下面的不能正常是新是因为我这里用with,a表改为临时表即可
declare @sql nvarchar(max)
;with a as(
select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn from store
)
select @sql= isnull(@sql+',','')+ rn from a group by rn
set @sql=N'select cname,product_name,'+@sql+N' from a'
+N' pivot(max(a.specification1) for rn in ('+@sql+N')) b'
select @sql
exec(@sql)
如下我又价格几个产品:
CREATE TABLE [dbo].[store](
[id] [int] NOT NULL,
[cname] [varchar](50) NULL,
[product_name] [varchar](50) NULL,
[specification1] [varchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (1, N'a药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'b药店', N'阿莫西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (2, N'a药店', N'阿莫西林西林', N'16片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'6片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'60片装')
INSERT [dbo].[store] ([id], [cname], [product_name], [specification1]) VALUES (3, N'a药店', N'阿莫西林西林', N'600片装')
if OBJECT_ID('tempdb..#a') is not null drop table #a
declare @sql nvarchar(max)
select cname,product_name,specification1,N'specification规格'+convert(varchar,ROW_NUMBER()over(partition by cname,product_name order by specification1) ) as rn
into #a from store
select @sql= isnull(@sql+',','')+ rn from #a group by rn
set @sql=N'select cname,product_name,'+@sql+N' from #a'
+N' pivot(max(specification1) for rn in ('+@sql+N')) b'
select @sql
exec(@sql)
结果如下:
|cname |product_name| specification规格1| specification规格2 |specification规格3 |specification规格4|
|a药店 |阿莫西林 |16片装 |6片装 |NULL |NULL|
|b药店 |阿莫西林 |6片装 |NULL |NULL |NULL|
|a药店 |阿莫西林西林 |16片装 |600片装 |60片装 |6片装|
典型的行转列
这个需求用sql很难实现,因为本质上sql查询出的结果只能是个固定列数的二维表。
按照需求中的说法,同一种药品,如果规格不同,需要按列分别列出规格,这意味着列的个数是不固定。上面的样例数据中,阿莫西林有6片装和24片装两种规格,规格列是2列,但如果有12片装的呢,规格列是否应该是3列?
如果需求中所说的输出列,只是为了显示用,可以在程序(PHP/JAVA等)中取出所有数据,按照药店、药品名称作为唯一值放到一个集合中,然后循环输出对应的规格列。
上面这个貌似比较难实现,因为多出的列要根据数据分组来。
但是可以实现差不多的功能,没有分列,用逗号分隔。
select id, cname, product_name,(
select group_concat(spec separator ',') from store s2
where s1.cname = s2.cname
) specification
from store s1
group by cname
可以查詢出來再自己寫個函數改成想要的格式