求1sql查询语句

Question

CREATE TABLE [dbo].[t_yplb](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lbmc] [varchar](50) NOT NULL,
[lbbh] [varchar](10) NOT NULL,
[jc] [varchar](20) NULL,
[Demo] [varchar](50) NULL,
[ismx] [int] NULL CONSTRAINT [DF_t_yplb_ismx]  DEFAULT (0),
[maxbh] [int] NOT NULL CONSTRAINT [DF_t_yplb_maxbh]  DEFAULT (0),
 CONSTRAINT [PK_t_yplb] PRIMARY KEY CLUSTERED 
(
[lbbh] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]go
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '西药部分','01',NULL,NULL,0,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗生素类药物','0101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素类','010101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素1','01010101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素V钾','01010102',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物','0102',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物小类1','010201',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物小类2','010202',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '解热镇痛药物','0103',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '镇痛药物','0104',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '麻醉用药','0105',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '维生素及矿物质缺乏类用药','0106',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '营养治疗药','0107',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '激素及调节内分泌功能药物','0108',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '调节免疫功能药物','0109',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗肿瘤药物','0110',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗变态反应药物','0111','抗过敏药物',NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '神经系统药物','0112',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '治疗精神障碍药物','0113',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '呼吸系统药物','0114',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '消化系统药物','0115',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '心脑血管病用药','0116',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '泌尿系统药物','0117',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '血液系统药物','0118',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '调节水、电解质及酸碱平衡药物','0119',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '专科特殊用药','0120',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '疫苗','0121',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗病毒药物','0122',NULL,NULL,1,1)表t_yplb中有用的列就是lbbh,lbmc2列，其中lbbh 每2位代表1级分类，目前的分类要求是1-4级（自动生成多级更好）
想得到的结果是 显示最末级的记录+ 级别的分类编号和名称
结果如下，标题
lbbh,lbmc,ji_1,ji_mc1 ji_2,ji_mc2,ji_3,ji_mc3
数据，没有2或3级的ji_?,ji_mc? 用空格代替就行[]表示一下
01010101 青霉素1 01 西药部分 0101 抗生素类药物 010101 青霉素类
01010102 青霉素V钾 01 西药部分 0101 抗生素类药物 010101 青霉素类
010201 抗寄生虫病药物小类1 01 西药部分 0102 抗寄生虫病药物  [] []
010202 抗寄生虫病药物小类2 01 西药部分 0102 抗寄生虫病药物 [] []
0103 解热镇痛药物 01 西药部分 [] [] [] []
 

Answer 1

SQL SERVER 2000还是2005+？

Answer 2

SELECT     t_yplb.lbbh, t_yplb.lbmc,
 t_yplb_1.lbbh  Expr1, t_yplb_1.lbmc  Expr2,
case when  len(t_yplb_2.lbbh) = len(t_yplb.lbbh) then ''
when len(t_yplb_2.lbbh)<len(t_yplb.lbbh) then  t_yplb_2.lbbh else '' end   Expr3,
case when  t_yplb_2.lbmc = t_yplb.lbmc then ''
when   t_yplb_2.lbmc <> t_yplb.lbmc then t_yplb_2.lbmc else '' end   Expr4,
 case when  len( t_yplb_3.lbbh ) = len(t_yplb_2.lbbh) then '' 
when len(t_yplb_3.lbbh)<len(t_yplb.lbbh) then  t_yplb_3.lbbh else '' end   Expr5,
case when t_yplb_3.lbmc = t_yplb.lbmc then ''  
when   t_yplb_3.lbmc <> t_yplb.lbmc then t_yplb_3.lbmc else '' end   Expr6
FROM         t_yplb INNER JOIN
                      t_yplb AS t_yplb_1 ON LEFT(t_yplb.lbbh,2) = t_yplb_1.lbbh INNER JOIN
                      t_yplb AS t_yplb_2 ON LEFT(t_yplb.lbbh, 4) = t_yplb_2.lbbh INNER JOIN
                      t_yplb AS t_yplb_3 ON LEFT(t_yplb.lbbh, 6) = t_yplb_3.lbbh
WHERE     (LEN(t_yplb.lbbh) > 2)

Answer 3

SELECT  lbmc = LEFT(REPLICATE(' ', LEN(a.lbbh)) + lbmc ,28),
        lbbh ,级别 = LEN(a.lbbh) / 2
FROM    [t_yplb] a  ORDER BY lbbh
/*
lbmc                                                     lbbh       级别
-------------------------------------------------------- ---------- -----------
  西药部分                                                   01         1
    抗生素类药物                                               0101       2
      青霉素类                                               010101     3
        青霉素1                                             01010101   4
        青霉素V钾                                            01010102   4
    抗寄生虫病药物                                              0102       2
      抗寄生虫病药物小类1                                         010201     3
      抗寄生虫病药物小类2                                         010202     3
    解热镇痛药物                                               0103       2
    镇痛药物                                                 0104       2
    麻醉用药                                                 0105       2
    维生素及矿物质缺乏类用药                                         0106       2
    营养治疗药                                                0107       2
    激素及调节内分泌功能药物                                         0108       2
    调节免疫功能药物                                             0109       2
    抗肿瘤药物                                                0110       2
    抗变态反应药物                                              0111       2
    神经系统药物                                               0112       2
    治疗精神障碍药物                                             0113       2
    呼吸系统药物                                               0114       2
    消化系统药物                                               0115       2
    心脑血管病用药                                              0116       2
    泌尿系统药物                                               0117       2
    血液系统药物                                               0118       2
    调节水、电解质及酸碱平衡药物                                       0119       2
    专科特殊用药                                               0120       2
    疫苗                                                   0121       2
    抗病毒药物                                                0122       2(28 row(s) affected)
*/对结果表示不理解，正常的末级只有01010101和01010102

Answer 4

SELECT  lbmc = LEFT(REPLICATE(' ', LEN(a.lbbh)) + lbmc ,28),
        lbbh ,级别 = LEN(a.lbbh) / 2,
        一级=ISNULL((SELECT lbmc FROM [t_yplb] WHERE lbbh=LEFT(a.lbbh,2) AND lbmc<>a.lbmc),'[ ]'),
        二级=ISNULL((SELECT lbmc FROM [t_yplb] WHERE lbbh=LEFT(a.lbbh,4) AND lbmc<>a.lbmc),'[ ]'),
        三级=ISNULL((SELECT lbmc FROM [t_yplb] WHERE lbbh=LEFT(a.lbbh,6) AND lbmc<>a.lbmc),'[ ]')
FROM    [t_yplb] a  ORDER BY lbbh/*
lbmc                                                     lbbh       级别          一级                                                 二级                                                 三级
-------------------------------------------------------- ---------- ----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
  西药部分                                                   01         1           [ ]                                                [ ]                                                [ ]
    抗生素类药物                                               0101       2           西药部分                                               [ ]                                                [ ]
      青霉素类                                               010101     3           西药部分                                               抗生素类药物                                             [ ]
        青霉素1                                             01010101   4           西药部分                                               抗生素类药物                                             青霉素类
        青霉素V钾                                            01010102   4           西药部分                                               抗生素类药物                                             青霉素类
    抗寄生虫病药物                                              0102       2           西药部分                                               [ ]                                                [ ]
      抗寄生虫病药物小类1                                         010201     3           西药部分                                               抗寄生虫病药物                                            [ ]
      抗寄生虫病药物小类2                                         010202     3           西药部分                                               抗寄生虫病药物                                            [ ]
    解热镇痛药物                                               0103       2           西药部分                                               [ ]                                                [ ]
    镇痛药物                                                 0104       2           西药部分                                               [ ]                                                [ ]
    麻醉用药                                                 0105       2           西药部分                                               [ ]                                                [ ]
    维生素及矿物质缺乏类用药                                         0106       2           西药部分                                               [ ]                                                [ ]
    营养治疗药                                                0107       2           西药部分                                               [ ]                                                [ ]
    激素及调节内分泌功能药物                                         0108       2           西药部分                                               [ ]                                                [ ]
    调节免疫功能药物                                             0109       2           西药部分                                               [ ]                                                [ ]
    抗肿瘤药物                                                0110       2           西药部分                                               [ ]                                                [ ]
    抗变态反应药物                                              0111       2           西药部分                                               [ ]                                                [ ]
    神经系统药物                                               0112       2           西药部分                                               [ ]                                                [ ]
    治疗精神障碍药物                                             0113       2           西药部分                                               [ ]                                                [ ]
    呼吸系统药物                                               0114       2           西药部分                                               [ ]                                                [ ]
    消化系统药物                                               0115       2           西药部分                                               [ ]                                                [ ]
    心脑血管病用药                                              0116       2           西药部分                                               [ ]                                                [ ]
    泌尿系统药物                                               0117       2           西药部分                                               [ ]                                                [ ]
    血液系统药物                                               0118       2           西药部分                                               [ ]                                                [ ]
    调节水、电解质及酸碱平衡药物                                       0119       2           西药部分                                               [ ]                                                [ ]
    专科特殊用药                                               0120       2           西药部分                                               [ ]                                                [ ]
    疫苗                                                   0121       2           西药部分                                               [ ]                                                [ ]
    抗病毒药物                                                0122       2           西药部分                                               [ ]                                                [ ]
*/

Answer 5

CREATE TABLE [dbo].[t_yplb](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [lbmc] [varchar](50) NOT NULL,
    [lbbh] [varchar](10) NOT NULL,
    [jc] [varchar](20) NULL,
    [Demo] [varchar](50) NULL,
    [ismx] [int] NULL CONSTRAINT [DF_t_yplb_ismx]  DEFAULT (0),
    [maxbh] [int] NOT NULL CONSTRAINT [DF_t_yplb_maxbh]  DEFAULT (0)
)
go
set nocount on
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '西药部分','01',NULL,NULL,0,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗生素类药物','0101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素类','010101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素1','01010101',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '青霉素V钾','01010102',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物','0102',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物小类1','010201',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗寄生虫病药物小类2','010202',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '解热镇痛药物','0103',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '镇痛药物','0104',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '麻醉用药','0105',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '维生素及矿物质缺乏类用药','0106',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '营养治疗药','0107',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '激素及调节内分泌功能药物','0108',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '调节免疫功能药物','0109',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗肿瘤药物','0110',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗变态反应药物','0111','抗过敏药物',NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '神经系统药物','0112',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '治疗精神障碍药物','0113',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '呼吸系统药物','0114',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '消化系统药物','0115',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '心脑血管病用药','0116',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '泌尿系统药物','0117',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '血液系统药物','0118',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '调节水、电解质及酸碱平衡药物','0119',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '专科特殊用药','0120',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '疫苗','0121',NULL,NULL,1,1)
insert   t_yplb   (lbmc,lbbh,jc,Demo,ismx,maxbh)      values   (   '抗病毒药物','0122',NULL,NULL,1,1)
set nocount off
goSELECT  lbmc = LEFT(REPLICATE(' ', LEN(a.lbbh)) + lbmc ,28),
        lbbh ,级别 = LEN(a.lbbh) / 2
FROM    [t_yplb] a
where not exists (select 1 from [t_yplb] where len(lbbh)>len(a.lbbh) and left(lbbh,len(a.lbbh))=a.lbbh)
ORDER BY lbbhdrop table t_yplb/**************************************lbmc                                                     lbbh       级别
-------------------------------------------------------- ---------- -----------
        青霉素1                                             01010101   4
        青霉素V钾                                            01010102   4
      抗寄生虫病药物小类1                                         010201     3
      抗寄生虫病药物小类2                                         010202     3
    解热镇痛药物                                               0103       2
    镇痛药物                                                 0104       2
    麻醉用药                                                 0105       2
    维生素及矿物质缺乏类用药                                         0106       2
    营养治疗药                                                0107       2
    激素及调节内分泌功能药物                                         0108       2
    调节免疫功能药物                                             0109       2
    抗肿瘤药物                                                0110       2
    抗变态反应药物                                              0111       2
    神经系统药物                                               0112       2
    治疗精神障碍药物                                             0113       2
    呼吸系统药物                                               0114       2
    消化系统药物                                               0115       2
    心脑血管病用药                                              0116       2
    泌尿系统药物                                               0117       2
    血液系统药物                                               0118       2
    调节水、电解质及酸碱平衡药物                                       0119       2
    专科特殊用药                                               0120       2
    疫苗                                                   0121       2
    抗病毒药物                                                0122       2(24 行受影响)