描述:有2个表。一个表是分类表。一个表详细表
详细表根据分类表关联数据。前面序号自动生成。序号分为5级。下面是数据和想要结果:(请高手给出详细代码,,真心感谢)
分类表:父编号 编号 名称
0 001 数码
0 002 家居
0 003 配饰
001 001001 手机
001 001002 本本
001 001003 小家电
001 001004 相机
001002 001002001 苹果
001003 001003001 厨房
001003 001003002 影音
001003 001003003 电风扇
001003 001003004 吸尘器
002 002001 建材
002 002002 日用
002001 002001001 卫浴
002001 002001002 灯饰
002002 002002001 婚庆
003 003001 箱包
003 003002 珠宝
003001 003001001 女包
003001 003001002 男包
003001 003001003 单肩包
003001001 003001001001 手提女包
003001001 003001001002 单肩女包
――――――――――――――――――――――――――
详细表:
分类编号 名称 个数 价格
001001 Nokia 1 2000.00
001001 三星 2 3000.00
001002001 白色苹果 1 4000.00
003001001 名牌女包 1 20000.00
003001001 普通女包 1 300.00
003001001 贵族女包 1 100000.00
003001002 黑色男包 2 300.00
003001002 棕色男包 1 170.00
-------------------------------------------------------
序号:
一级序号: 一 二 三
二级序号: (一) (二) (三)
三级序号: 1 2 3
四级序号: (1) (2) (3)
五级序号: ① ② ③
----------------------------------------------------------------------------------
想要结果:
序号 分类编号 名称 个数 价钱
一 001 数码 4 12000.00 (一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
(1) 001002001 白色苹果 1 4000.00
二 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
(1) 003001001001 手提女包 3 120300.00
① 003001001001 名牌女包 1 20000.00
② 003001001001 普通女包 1 300.00
③ 003001001001 贵族女包 1 100000.00
2 003001002 男包 3 770.00
(1) 003001002 黑色男包 2 300.00
(2) 003001002 棕色男包 1 170.00
-------------
在线等
详细表根据分类表关联数据。前面序号自动生成。序号分为5级。下面是数据和想要结果:(请高手给出详细代码,,真心感谢)
分类表:父编号 编号 名称
0 001 数码
0 002 家居
0 003 配饰
001 001001 手机
001 001002 本本
001 001003 小家电
001 001004 相机
001002 001002001 苹果
001003 001003001 厨房
001003 001003002 影音
001003 001003003 电风扇
001003 001003004 吸尘器
002 002001 建材
002 002002 日用
002001 002001001 卫浴
002001 002001002 灯饰
002002 002002001 婚庆
003 003001 箱包
003 003002 珠宝
003001 003001001 女包
003001 003001002 男包
003001 003001003 单肩包
003001001 003001001001 手提女包
003001001 003001001002 单肩女包
――――――――――――――――――――――――――
详细表:
分类编号 名称 个数 价格
001001 Nokia 1 2000.00
001001 三星 2 3000.00
001002001 白色苹果 1 4000.00
003001001 名牌女包 1 20000.00
003001001 普通女包 1 300.00
003001001 贵族女包 1 100000.00
003001002 黑色男包 2 300.00
003001002 棕色男包 1 170.00
-------------------------------------------------------
序号:
一级序号: 一 二 三
二级序号: (一) (二) (三)
三级序号: 1 2 3
四级序号: (1) (2) (3)
五级序号: ① ② ③
----------------------------------------------------------------------------------
想要结果:
序号 分类编号 名称 个数 价钱
一 001 数码 4 12000.00 (一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
(1) 001002001 白色苹果 1 4000.00
二 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
(1) 003001001001 手提女包 3 120300.00
① 003001001001 名牌女包 1 20000.00
② 003001001001 普通女包 1 300.00
③ 003001001001 贵族女包 1 100000.00
2 003001002 男包 3 770.00
(1) 003001002 黑色男包 2 300.00
(2) 003001002 棕色男包 1 170.00
-------------
在线等
use Tempdb
go
--> -->
if not object_id(N'Category') is null
drop table Category
Go
Create table Category([父编号] nvarchar(50),[编号] nvarchar(50),[名称] nvarchar(50))
Insert Category
select N'0',N'001',N'数码' union all
select N'0',N'002',N'家居' union all
select N'0',N'003',N'配饰' union all
select N'001',N'001001',N'手机' union all
select N'001',N'001002',N'本本' union all
select N'001',N'001003',N'小家电' union all
select N'001',N'001004',N'相机' union all
select N'001002',N'001002001',N'苹果' union all
select N'001003',N'001003001',N'厨房' union all
select N'001003',N'001003002',N'影音' union all
select N'001003',N'001003003',N'电风扇' union all
select N'001003',N'001003004',N'吸尘器' union all
select N'002',N'002001',N'建材' union all
select N'002',N'002002',N'日用' union all
select N'002001',N'002001001',N'卫浴' union all
select N'002001',N'002001002',N'灯饰' union all
select N'002002',N'002002001',N'婚庆' union all
select N'003',N'003001',N'箱包' union all
select N'003',N'003002',N'珠宝' union all
select N'003001',N'003001001',N'女包' union all
select N'003001',N'003001002',N'男包' union all
select N'003001',N'003001003',N'单肩包' union all
select N'003001001',N'003001001001',N'手提女包' union all
select N'003001001',N'003001001002',N'单肩女包'
Go
if not object_id(N'Merchandise') is null
drop table Merchandise
Go
Create table Merchandise([分类编号] nvarchar(50),[名称] nvarchar(50),[个数] int,[价格] decimal(18,2))
Insert Merchandise
select N'001001',N'Nokia',1,2000.00 union all
select N'001001',N'三星',2,3000.00 union all
select N'001002001',N'白色苹果',1,4000.00 union all
select N'003001001001',N'名牌女包',1,20000.00 union all
select N'003001001001',N'普通女包',1,300.00 union all
select N'003001001001',N'贵族女包',1,100000.00 union all
select N'003001002',N'黑色男包',2,300.00 union all
select N'003001002',N'棕色男包',1,170.00go
if not object_id(N'NumType') is null
drop table NumType
Go
Create table NumType(ID INT IDENTITY,[Lev] int,[num] int,[Name] nvarchar(10))
Insert NumType
select 1,1,N'一' union all
select 1,2,N'二' union all
select 1,3,N'三' union all
select 2,1,N'(一)' union all
select 2,2,N'(二)' union all
select 2,3,N'(三)' union all
select 3,1,N'1' union all
select 3,2,N'2' union all
select 3,3,N'3' union all
select 4,1,N'(1)' union all
select 4,2,N'(2)' union all
select 4,3,N'(3)' union all
select 5,1,N'①' union all
select 5,2,N'②' union all
select 5,3,N'③'
Go;WITH B
AS
(
Select [父编号],[编号],[名称],[Lev]=1,num=ROW_NUMBER()OVER(PARTITION BY [父编号] ORDER BY [编号]) from Category WHERE [父编号]='0'
UNION ALL
SELECT a.*,b.[Lev]+1,row=ROW_NUMBER()OVER(PARTITION BY a.[父编号] ORDER BY a.[编号]) FROM Category AS a INNER JOIN b ON a.[父编号]=b.[编号]
)SELECT
[Name],[编号],[名称],[个数],[价格]
FROM
(
SELECT
c.[Name],a.[编号],a.[名称],SUM(b.[个数]) AS [个数] ,SUM(b.[价格]*b.[个数]) AS [价格],a.lev,a.num
FROM B AS a
inner JOIN Merchandise AS b ON b.[分类编号] LIKE a.[编号]+'%'
INNER JOIN NumType AS c ON c.[Lev]=a.[Lev] AND a.num=c.num
GROUP BY a.[编号],a.[名称],c.[Name],a.lev,a.num
UNION ALL
SELECT c.[Name],a.[分类编号],a. [名称],a.[个数],a.[价格],c.lev+1 AS lev,a.num
FROM
(select * ,ROW_NUMBER()OVER(PARTITION BY [分类编号] ORDER BY [分类编号]) AS num FROM Merchandise) AS a
INNER JOIN B ON b.[编号]=a.[分类编号]
INNER JOIN NumType AS c ON c.[Lev]=b.[Lev]+1 AND c.num=a.num
)t
ORDER BY [编号],lev,num/*
Name 编号 名称 个数 价格
一 001 数码 4 12000.00
(一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
(1) 001002001 白色苹果 1 4000.00
三 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
(1) 003001001001 手提女包 3 120300.00
① 003001001001 名牌女包 1 20000.00
② 003001001001 普通女包 1 300.00
③ 003001001001 贵族女包 1 100000.00
2 003001002 男包 3 770.00
(1) 003001002 黑色男包 2 300.00
(2) 003001002 棕色男包 1 170.00
*/
if not object_id(N'Category') is null
drop table Category
Go
Create table Category([父编号] nvarchar(50),[编号] nvarchar(50),[名称] nvarchar(50))
Insert Category
select N'0',N'001',N'数码' union all
select N'0',N'002',N'家居' union all
select N'0',N'003',N'配饰' union all
select N'001',N'001001',N'手机' union all
select N'001',N'001002',N'本本' union all
select N'001',N'001003',N'小家电' union all
select N'001',N'001004',N'相机' union all
select N'001002',N'001002001',N'苹果' union all
select N'001003',N'001003001',N'厨房' union all
select N'001003',N'001003002',N'影音' union all
select N'001003',N'001003003',N'电风扇' union all
select N'001003',N'001003004',N'吸尘器' union all
select N'002',N'002001',N'建材' union all
select N'002',N'002002',N'日用' union all
select N'002001',N'002001001',N'卫浴' union all
select N'002001',N'002001002',N'灯饰' union all
select N'002002',N'002002001',N'婚庆' union all
select N'003',N'003001',N'箱包' union all
select N'003',N'003002',N'珠宝' union all
select N'003001',N'003001001',N'女包' union all
select N'003001',N'003001002',N'男包' union all
select N'003001',N'003001003',N'单肩包' union all
select N'003001001',N'003001001001',N'手提女包' union all
select N'003001001',N'003001001002',N'单肩女包'
Go
if not object_id(N'Merchandise') is null
drop table Merchandise
Go
Create table Merchandise([分类编号] nvarchar(50),[名称] nvarchar(50),[个数] int,[价格] decimal(18,2))
Insert Merchandise
select N'001001',N'Nokia',1,2000.00 union all
select N'001001',N'三星',2,3000.00 union all
select N'001002001',N'白色苹果',1,4000.00 union all
select N'003001001001',N'名牌女包',1,20000.00 union all
select N'003001001001',N'普通女包',1,300.00 union all
select N'003001001001',N'贵族女包',1,100000.00 union all
select N'003001002',N'黑色男包',2,300.00 union all
select N'003001002',N'棕色男包',1,170.00go
if not object_id(N'NumType') is null
drop table NumType
Go
Create table NumType(ID INT IDENTITY,[Lev] int,[num] int,[Name] nvarchar(10))
Insert NumType
select 1,1,N'一' union all
select 1,2,N'二' union all
select 1,3,N'三' union all
select 2,1,N'(一)' union all
select 2,2,N'(二)' union all
select 2,3,N'(三)' union all
select 3,1,N'1' union all
select 3,2,N'2' union all
select 3,3,N'3' union all
select 4,1,N'(1)' union all
select 4,2,N'(2)' union all
select 4,3,N'(3)' union all
select 5,1,N'①' union all
select 5,2,N'②' union all
select 5,3,N'③'
Go
/**/
select b.name,a.编号,a.名称,a.个数,a.价格 from(
select row_number()over(partition by 父编号 order by 编号)rn,* from(
select a.父编号,a.编号,a.名称,sum(b.个数)个数,sum(b.个数*b.价格)价格
from Category a inner join Merchandise b on charindex(a.编号,b.分类编号)=1
group by a.父编号,a.编号,a.名称
union all
select 分类编号,* from Merchandise
)t)a inner join NumType b on b.num=a.rn and b.lev=(len(a.父编号)+1)/2
order by a.编号,a.rn
/*
name 编号 名称 个数 价格
---------- -------------------------------------------------- -------------------------------------------------- ----------- ---------------------------------------
一 001 数码 4 12000.00
(一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
① 001002001 白色苹果 1 4000.00
二 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
① 003001001001 手提女包 3 120300.00
① 003001002 黑色男包 2 300.00
② 003001002 棕色男包 1 170.00
2 003001002 男包 3 770.00(14 行受影响)*/
go
--> -->
if not object_id(N'Category') is null
drop table Category
Go
Create table Category([父编号] nvarchar(50),[编号] nvarchar(50),[名称] nvarchar(50))
Insert Category
select N'0',N'001',N'数码' union all
select N'0',N'002',N'家居' union all
select N'0',N'003',N'配饰' union all
select N'001',N'001001',N'手机' union all
select N'001',N'001002',N'本本' union all
select N'001',N'001003',N'小家电' union all
select N'001',N'001004',N'相机' union all
select N'001002',N'001002001',N'苹果' union all
select N'001003',N'001003001',N'厨房' union all
select N'001003',N'001003002',N'影音' union all
select N'001003',N'001003003',N'电风扇' union all
select N'001003',N'001003004',N'吸尘器' union all
select N'002',N'002001',N'建材' union all
select N'002',N'002002',N'日用' union all
select N'002001',N'002001001',N'卫浴' union all
select N'002001',N'002001002',N'灯饰' union all
select N'002002',N'002002001',N'婚庆' union all
select N'003',N'003001',N'箱包' union all
select N'003',N'003002',N'珠宝' union all
select N'003001',N'003001001',N'女包' union all
select N'003001',N'003001002',N'男包' union all
select N'003001',N'003001003',N'单肩包' union all
select N'003001001',N'003001001001',N'手提女包' union all
select N'003001001',N'003001001002',N'单肩女包'
Go
if not object_id(N'Merchandise') is null
drop table Merchandise
Go
Create table Merchandise([分类编号] nvarchar(50),[名称] nvarchar(50),[个数] int,[价格] decimal(18,2))
Insert Merchandise
select N'001001',N'Nokia',1,2000.00 union all
select N'001001',N'三星',2,3000.00 union all
select N'001002001',N'白色苹果',1,4000.00 union all
select N'003001001001',N'名牌女包',1,20000.00 union all
select N'003001001001',N'普通女包',1,300.00 union all
select N'003001001001',N'贵族女包',1,100000.00 union all
select N'003001002',N'黑色男包',2,300.00 union all
select N'003001002',N'棕色男包',1,170.00go
IF OBJECT_ID('NumType') IS NOT NULL
DROP VIEW NumType
go
CREATE VIEW NumType
AS
WITH Cte (Lev,num,Name)
AS
(
select 1,1,N'一' union all
select 1,2,N'二' union all
select 1,3,N'三' union all
select 2,1,N'(一)' union all
select 2,2,N'(二)' union all
select 2,3,N'(三)' UNION all
SELECT lev=3,
number+1 AS num,
CAST(number+1 AS NVARCHAR(2)) AS name
FROM master.dbo.spt_values AS c
WHERE type='P' AND number<20
UNION ALL
SELECT
lev=5,
number+1 AS num,
NCHAR(number+9312) AS Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
UNION ALL
SELECT
lev=4,
number+1 AS num,
NCHAR(9312+20+number) as Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
)
SELECT * FROM Cte
GO;with b
as
(
Select
a.[编号],a.[名称]
,SUM(b.[个数]) AS [个数] ,SUM(b.[价格]*b.[个数]) AS [价格]
,[Lev]=DENSE_RANK()over(order by len([父编号])),
num=DENSE_RANK()OVER(PARTITION BY [父编号] ORDER BY a.[编号])
from Category as a
inner join Merchandise as b on b.分类编号 like a.编号+'%'
group by a.[父编号],a.[编号],a.[名称]
)
select
[Name],[编号],[名称],[个数],[价格]
from (
select * from b union all
select
a.[分类编号] as [编号],a.[名称],a.[个数],a.[价格],b.lev+1 AS lev,ROW_NUMBER()OVER(PARTITION BY [分类编号] ORDER BY [分类编号]) AS num
from Merchandise as a
inner join b as b on a.分类编号=b.编号
)t1,NumType as t2
where t2.[Lev]=t1.[Lev] AND t1.num=t2.num
ORDER by [编号],t1.lev,t1.num
/*
Name 编号 名称 个数 价格
一 001 数码 4 12000.00
(一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
⑴ 001002001 白色苹果 1 4000.00
二 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
⑴ 003001001001 手提女包 3 120300.00
① 003001001001 名牌女包 1 20000.00
② 003001001001 普通女包 1 300.00
③ 003001001001 贵族女包 1 100000.00
2 003001002 男包 3 770.00
⑴ 003001002 黑色男包 2 300.00
⑵ 003001002 棕色男包 1 170.00
*/
go
--> -->
if not object_id(N'Category') is null
drop table Category
Go
Create table Category([父编号] nvarchar(50),[编号] nvarchar(50),[名称] nvarchar(50))
Insert Category
select N'0',N'001',N'数码' union all
select N'0',N'002',N'家居' union all
select N'0',N'003',N'配饰' union all
select N'001',N'001001',N'手机' union all
select N'001',N'001002',N'本本' union all
select N'001',N'001003',N'小家电' union all
select N'001',N'001004',N'相机' union all
select N'001002',N'001002001',N'苹果' union all
select N'001003',N'001003001',N'厨房' union all
select N'001003',N'001003002',N'影音' union all
select N'001003',N'001003003',N'电风扇' union all
select N'001003',N'001003004',N'吸尘器' union all
select N'002',N'002001',N'建材' union all
select N'002',N'002002',N'日用' union all
select N'002001',N'002001001',N'卫浴' union all
select N'002001',N'002001002',N'灯饰' union all
select N'002002',N'002002001',N'婚庆' union all
select N'003',N'003001',N'箱包' union all
select N'003',N'003002',N'珠宝' union all
select N'003001',N'003001001',N'女包' union all
select N'003001',N'003001002',N'男包' union all
select N'003001',N'003001003',N'单肩包' union all
select N'003001001',N'003001001001',N'手提女包' union all
select N'003001001',N'003001001002',N'单肩女包'
Go
if not object_id(N'Merchandise') is null
drop table Merchandise
Go
Create table Merchandise([分类编号] nvarchar(50),[名称] nvarchar(50),[个数] int,[价格] decimal(18,2))
Insert Merchandise
select N'001001',N'Nokia',1,2000.00 union all
select N'001001',N'三星',2,3000.00 union all
select N'001002001',N'白色苹果',1,4000.00 union all
select N'003001001001',N'名牌女包',1,20000.00 union all
select N'003001001001',N'普通女包',1,300.00 union all
select N'003001001001',N'贵族女包',1,100000.00 union all
select N'003001002',N'黑色男包',2,300.00 union all
select N'003001002',N'棕色男包',1,170.00go
IF OBJECT_ID('NumType') IS NOT NULL
DROP VIEW NumType
go
CREATE VIEW NumType
AS
WITH Cte (Lev,num,Name)
AS
(
select 1,1,N'一' union all
select 1,2,N'二' union all
select 1,3,N'三' union all
select 2,1,N'(一)' union all
select 2,2,N'(二)' union all
select 2,3,N'(三)' UNION all
SELECT lev=3,
number+1 AS num,
CAST(number+1 AS NVARCHAR(2)) AS name
FROM master.dbo.spt_values AS c
WHERE type='P' AND number<20
UNION ALL
SELECT
lev=5,
number+1 AS num,
NCHAR(number+9312) AS Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
UNION ALL
SELECT
lev=4,
number+1 AS num,
NCHAR(9312+20+number) as Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
)
SELECT * FROM Cte
GO;with b
as
(
Select
a.[编号],a.[名称]
,SUM(b.[个数]) AS [个数] ,SUM(b.[价格]*b.[个数]) AS [价格]
,[Lev]=len(a.[编号])/3,
num=DENSE_RANK()OVER(PARTITION BY [父编号] ORDER BY a.[编号])
from Category as a
inner join Merchandise as b on b.分类编号 like a.编号+'%'
group by a.[父编号],a.[编号],a.[名称]
union all
select
a.[分类编号] as [编号],a.[名称],a.[个数],a.[价格],
len(a.[分类编号])/3+1 AS lev,
ROW_NUMBER()OVER(PARTITION BY [分类编号] ORDER BY [分类编号]) AS num
from Merchandise as a
)
select
[Name],[编号],[名称],[个数],[价格]
from b as t1,NumType as t2
where t2.[Lev]=t1.[Lev] AND t1.num=t2.num
ORDER by [编号],t1.lev,t1.num
/*
Name 编号 名称 个数 价格
一 001 数码 4 12000.00
(一) 001001 手机 3 8000.00
1 001001 Nokia 1 2000.00
2 001001 三星 2 3000.00
(二) 001002 本本 1 4000.00
1 001002001 苹果 1 4000.00
⑴ 001002001 白色苹果 1 4000.00
二 003 配饰 6 121070.00
(一) 003001 箱包 6 121070.00
1 003001001 女包 3 120300.00
⑴ 003001001001 手提女包 3 120300.00
① 003001001001 名牌女包 1 20000.00
② 003001001001 普通女包 1 300.00
③ 003001001001 贵族女包 1 100000.00
2 003001002 男包 3 770.00
⑴ 003001002 黑色男包 2 300.00
⑵ 003001002 棕色男包 1 170.00
*/