400分真心求解

Question

http://topic.csdn.net/u/20111117/17/770e1311-878b-41d5-ac33-1afc2ed4682f.html?47858
http://topic.csdn.net/u/20111117/17/2bc91e18-6316-4927-ade1-86431056c76f.html
SQL表连接。然后自动生成序号。序号比较特殊。有层级关系。不同层级序号显示的不一样。

Answer 1

借用大版的表:
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 行受影响)*/

Answer 2

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.父编号)/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 行受影响)*/

Answer 3

简化一下语句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 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
*/