求一sql

Question

只有2级还是有多级？另外SQL Server的版本是多少？

Answer 1

--create table tb1(id varchar(3) , fatherid varchar(3) , name Nvarchar(10))
--insert into tb1 values('1' , '0' , N'大类1')
--insert into tb1 values('2' , '0' , N'大类2')
--insert into tb1 values('3' , '0' , N'大类3')
--insert into tb1 values('4' , '1' , N'小类1')
--insert into tb1 values('5' , '1' , N'小类2')
--insert into tb1 values('6' , '2' , N'小类3')
--insert into tb1 values('7' , '2' , N'小类4')
--insert into tb1 values('8' , '2' , N'小类5')
--insert into tb1 values('9' , '3' , N'小类6')
--insert into tb1 values('10' , '3' ,N'小类7')
--go
----fatherid=0为父级--create table tb2(id varchar(3) , categoryid varchar(3) , name Nvarchar(10))
--insert into tb2 values('1' , '1'  , N'产品a')
--insert into tb2 values('2' , '2' , N'产品b')
--insert into tb2 values('3' , '3' , N'产品c')
--insert into tb2 values('4' , '4' , N'产品d')
--insert into tb2 values('5' , '5' , N'产品e')
--insert into tb2 values('6' , '6' , N'产品f')
--insert into tb2 values('7' , '7' , N'产品g')
--insert into tb2 values('8' , '8' , N'产品h')
--insert into tb2 values('9' , '9' , N'产品i')
--insert into tb2 values('10' , '10' ,  N'产品j')
--go--tb1.id=tb2.categoryid--怎么得到下面，得到从属于自己的产品和子级产品的个数
--id   fatherid   name  num
--1      0       大类1   3
--2      0       大类2   4
--3      0       大类3   3SELECT tb1.id,tb1.fatherid,tb1.NAME,(SELECT COUNT(b.NAME) FROM tb1 b WHERE fatherid=tb1.id and fatherid<>0)+COUNT(tb1.name)num
FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.categoryid
WHERE fatherid=0
GROUP BY tb1.id,tb1.fatherid,tb1.NAME
/*
id   fatherid NAME       num
---- -------- ---------- -----------
1    0        大类1        3
2    0        大类2        4
3    0        大类3        3
*/

Answer 2

DROP TABLE tb1
GO
create table tb1(id varchar(3) , fatherid varchar(3) , name nvarchar(10))
GO
insert into tb1 values('1' , '0' , N'大类1')
insert into tb1 values('2' , '0' , N'大类2')
insert into tb1 values('3' , '0' , N'大类3')
insert into tb1 values('4' , '1' , N'小类1')
insert into tb1 values('5' , '1' , N'小类2')
insert into tb1 values('6' , '2' , N'小类3')
insert into tb1 values('7' , '2' , N'小类4')
insert into tb1 values('8' , '2' , N'小类5')
insert into tb1 values('9' , '3' , N'小类6')
insert into tb1 values('10' , '3' , N'小类7')
go
--fatherid=0为父级
DROP TABLE tb2 
go
create table tb2(id varchar(3) , categoryid varchar(3) , name nvarchar(10))
GO
insert into tb2 values('1' , '1'  , N'产品a')
insert into tb2 values('2' , '2' , N'产品b')
insert into tb2 values('3' , '3' , N'产品c')
insert into tb2 values('4' , '4' , N'产品d')
insert into tb2 values('5' , '5' , N'产品e')
insert into tb2 values('6' , '6' , N'产品f')
insert into tb2 values('7' , '7' , N'产品g')
insert into tb2 values('8' , '8' , N'产品h')
insert into tb2 values('9' , '9' , N'产品i')
insert into tb2 values('10' , '10' ,  N'产品j')
goDECLARE @fatherid VARCHAR(10) = '0';
WITH a AS (
         SELECT id,
                fatherid,
                NAME,
                ID    AS ID_f,
                NAME  AS name_f
         FROM   tb1   AS t
         WHERE  t.fatherid = @fatherid
         UNION ALL
         SELECT tb1.id,
                tb1.fatherid,
                tb1.NAME,
                a.id    AS id_f,
                a.name  AS name_f
         FROM   tb1
                JOIN a
                     ON  tb1.fatherid = a.id
     )SELECT a.id_f,
       @fatherid  AS fatherid,
       a.NAME_f,
       COUNT(1)   AS num
FROM   tb2
       INNER JOIN a
            ON  tb2.categoryid = a.id
GROUP BY
       a.id_f,
       a.NAME_f 
/*
id_f fatherid   NAME_f     num
---- ---------- ---------- -----------
1    0          大类1        3
2    0          大类2        4
3    0          大类3        3(3 個資料列受到影響)
*/

Answer 3

没有产品自然是0
num=大类所属产品+大类子级所属产品  本身是不包括在内的，因为不是“产品”

Answer 4

--create table tb1(id varchar(3) , fatherid varchar(3) , name Nvarchar(10))
--insert into tb1 values('1' , '0' , N'大类1')
--insert into tb1 values('2' , '0' , N'大类2')
--insert into tb1 values('3' , '0' , N'大类3')
----insert into tb1 values('4' , '1' , N'小类1')
----insert into tb1 values('5' , '1' , N'小类2')
----insert into tb1 values('6' , '2' , N'小类3')
----insert into tb1 values('7' , '2' , N'小类4')
----insert into tb1 values('8' , '2' , N'小类5')
----insert into tb1 values('9' , '3' , N'小类6')
----insert into tb1 values('10' , '3' ,N'小类7')
----go
----fatherid=0为父级--create table tb2(id varchar(3) , categoryid varchar(3) , name Nvarchar(10))
--insert into tb2 values('1' , '1'  , N'产品a')
--insert into tb2 values('2' , '2' , N'产品b')
--insert into tb2 values('3' , '3' , N'产品c')
--insert into tb2 values('4' , '4' , N'产品d')
--insert into tb2 values('5' , '5' , N'产品e')
--insert into tb2 values('6' , '6' , N'产品f')
--insert into tb2 values('7' , '7' , N'产品g')
--insert into tb2 values('8' , '8' , N'产品h')
--insert into tb2 values('9' , '9' , N'产品i')
--insert into tb2 values('10' , '10' ,  N'产品j')
--go--tb1.id=tb2.categoryid--怎么得到下面，得到从属于自己的产品和子级产品的个数
--id   fatherid   name  num
--1      0       大类1   3
--2      0       大类2   4
--3      0       大类3   3
;WITH cte AS (
SELECT a.id  ,a.fatherid ,a.NAME  ,COUNT(b.name)num
FROM tb1 a LEFT JOIN tb2 b ON a.id=b.categoryid
GROUP BY a.id  ,a.fatherid ,a.NAME  )
SELECT a.id,a.fatherid,a.NAME,(SELECT SUM(num) FROM cte b WHERE b.fatherid=a.id AND b.fatherid<>0)+a.num num
FROM cte a
WHERE a.fatherid=0/*
id   fatherid NAME       num
---- -------- ---------- -----------
1    0        大类1        3
2    0        大类2        4
3    0        大类3        3
*/