描述:有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
*/
我希望序号不用表存。最好能自动生成。
这个要求可能有点高。在等等。如果再没有别的答案。过2天就结贴。
希望SQL版本的高手多帮帮忙。看看有什么更好方法没。(当然(中國風) 大哥的方法已经很不错了)
集思广益!
我希望序号不用表存。最好能自动生成。這都需要一個對照表或函數,有規則的可以推算如:1/①/⑴ 3種可以推算出來
①/⑴--最多支持20個數字,不用表可用視圖或函數給你舉個生成視圖的方法
看一下①/⑴的格式,格式不一樣時也只有一個個連接 union all select
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=4,
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=5,
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
SELECT * FROM NumType/*
Lev num Name
1 1 一
1 2 二
1 3 三
2 1 (一)
2 2 (二)
2 3 (三)
3 1 1
3 2 2
3 3 3
3 4 4
3 5 5
3 6 6
3 7 7
3 8 8
3 9 9
3 10 10
3 11 11
3 12 12
3 13 13
3 14 14
3 15 15
3 16 16
3 17 17
3 18 18
3 19 19
3 20 20
4 1 ①
4 2 ②
4 3 ③
4 4 ④
4 5 ⑤
4 6 ⑥
4 7 ⑦
4 8 ⑧
4 9 ⑨
4 10 ⑩
4 11 ⑪
4 12 ⑫
4 13 ⑬
4 14 ⑭
4 15 ⑮
4 16 ⑯
4 17 ⑰
4 18 ⑱
4 19 ⑲
4 20 ⑳
5 1 ⑴
5 2 ⑵
5 3 ⑶
5 4 ⑷
5 5 ⑸
5 6 ⑹
5 7 ⑺
5 8 ⑻
5 9 ⑼
5 10 ⑽
5 11 ⑾
5 12 ⑿
5 13 ⒀
5 14 ⒁
5 15 ⒂
5 16 ⒃
5 17 ⒄
5 18 ⒅
5 19 ⒆
5 20 ⒇
*/
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'(三)' 其它數字自己加上
五级序号: ① ② ③
反了,改改 lev值
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=4,
number+1 AS num,
NCHAR(number+9332) AS Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
UNION ALL
SELECT
lev=5,
number+1 AS num,
NCHAR(9312+number) as Name
FROM master.dbo.spt_values AS a
WHERE type='P' AND number<20
)
SELECT * FROM Cte
GO
SELECT * FROM NumType/*
Lev num Name
1 1 一
1 2 二
1 3 三
2 1 (一)
2 2 (二)
2 3 (三)
3 1 1
3 2 2
3 3 3
3 4 4
3 5 5
3 6 6
3 7 7
3 8 8
3 9 9
3 10 10
3 11 11
3 12 12
3 13 13
3 14 14
3 15 15
3 16 16
3 17 17
3 18 18
3 19 19
3 20 20
4 1 ⑴
4 2 ⑵
4 3 ⑶
4 4 ⑷
4 5 ⑸
4 6 ⑹
4 7 ⑺
4 8 ⑻
4 9 ⑼
4 10 ⑽
4 11 ⑾
4 12 ⑿
4 13 ⒀
4 14 ⒁
4 15 ⒂
4 16 ⒃
4 17 ⒄
4 18 ⒅
4 19 ⒆
4 20 ⒇
5 1 ①
5 2 ②
5 3 ③
5 4 ④
5 5 ⑤
5 6 ⑥
5 7 ⑦
5 8 ⑧
5 9 ⑨
5 10 ⑩
5 11 ⑪
5 12 ⑫
5 13 ⑬
5 14 ⑭
5 15 ⑮
5 16 ⑯
5 17 ⑰
5 18 ⑱
5 19 ⑲
5 20 ⑳
*/
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 行受影响)*/
WHERE [父编号]='0' and exists(select 1 from Merchandise where [分类编号] like a.编号+'%' )
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.父编号)/3+1
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
(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
(1) 003001002 黑色男包 2 300.00
(2) 003001002 棕色男包 1 170.00
2 003001002 男包 3 770.00(17 行受影响)*/
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
*/
我之前也是用charindex做得。中国风大哥的代码相信已经成熟了。我研究研究。看看你们2人代码的差异。以及看看能否综合。
呵呵。